Hi,

> Oracle!
>
> Got it in about 1 sec!
>
> Thanks a lot. Now I will have to work on my SQL builder tool. :-(
>
> Can you explain why the previous query was that fast on MSSQL and not
> on FB? I can see your point but it seems that MSSQL was able to
> optimize the query while FB needed more "precautions".

I can't say (without more information :-) what MSSQL did different on this
query compared to FB, but i guess this :

The MSSQL optimizer is clever enough to see that the LEFT JOIN has an
condition in the where clause and turns it into a INNER JOIN. You can see
this by looking at the Execution PLAN in the SQL Query Analyzer.
What happens on MSSQL if you put the where clause condition also in the ON
search condition.

At least you've added a new item on my todo list now ;-)


Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81