hello,

this is how my table - table1(int,int,varchar) - looks like:

recid         |   ft          | val
---------------------------------------
1001         |   301     |   a
1001         |   302     |   b
1001         |   303     |   c
1002         |   301     |   d
1002         |   302     |   e
1003         |   303     |   f
1003         |   301     |   g
1003         |   302     |   h
1004         |   303     |   i

in reality this table has a few millions of records.

for the following query:
select t1.val as val1 , t2.val as val2
from table1 t1
left join table1 t2 on t1.recid=t2.recid and t2.ft=303
where t1.recid=1002 and t1.ft=301

i get the expected result:
val1 | val2
---------------------
d | f

the execution time of this query is very slow: ~1.5 seconds - in comparison to MSSql:0.016 seconds.
the kind of a query that i really need in my production application doesn't have only one such a "left join...", but may have even 10. with 5 such "left join..."s the query execution time is ~25 seconds, which is so far slower than MSSql: 0.156

more info:
1. i run the queries from SQuirreL SQL Client. the MonetDB5 is installed\run on the same machine.
the MSSql server was running on another machine in the same LAN.
2. the primary key of table1 is : recid,ft,val

3. at the beginning i had another problem, that i have got an empty result when running a simple query like this:

select * from table1 t1 where t1.recid=1002 and t1.ft=301

i'm not sure about this, but i guess that this problem had been solved by creating a view such as:

create view v1 as select recid,ft,val from table1

i have no clue whether these two problems related to each other, but i mentioned it since it might give you a hint for solving the other problem.

thanks in advanced for any help,
ewilde.