Hi !
It's known that Firebird transforms this kind of query:
select
TableA.Foo
from
TableA
where
TableA.Bar in (select TableB.Bar where X=Y)
into:
select
TableA.Foo
from
TableA
where
exists (select * from TableB where where X=Y and TableB.Bar =
TableA.Bar)
This implies a correlated sub-query that will be executed every time for
each row on TableA
Since the sub-query is not correlated (on the original query) to the
outer query, FB could execute it first and use the values to execute the
outer query, something like:
select TableB.Bar where X=Y
Results in 1, 2, 3, 5, 7, 11
and then execute:
select
TableA.Foo
from
TableA
where
TableA.Bar in (1, 2, 3, 5, 7, 11)
When TableA is big there is a huge performance improvement using the
second method.
There is any plan to add this kind of otimization in FB 3 ?
I know there are other ways to write the same query, but in some cases I
could not rewrite the whole query, but just add a WHERE clause...
see you !
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnnow-d2d
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
