Re: [Firebird-devel] Optmizer vs IN (sub-select)

[Doychin Bondzhev]

On 23.1.2013 г. 20:18 ч., Alexandre Benson Smith wrote:
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


In such case I usually go with left outer join.

select * from tableA
left outer join tableb on tableb.bar = tablea.bar and x=y
where tableb.bar is not null

I'm not sure this will work in your case but this really speeds up the 
execution.

--
Doychin Bondzhev
dSoft-Bulgaria Ltd.
PowerPro - billing & provisioning solution for Service providers
PowerStor - Warehouse & POS
http://www.dsoft-bg.com/
Mobile: +359888243116
<<attachment: doychin.vcf>>------------------------------------------------------------------------------
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-d2dFirebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel