Re: [firebird-support] Another Unoptimized question

[Alexandre Benson Smith]

Em 16/4/2013 11:09, skander_sp escreveu:
 May be obvious, but til now, i don't see how much unoptimized works the 
 field in subselect syntax.

 I'm using it in some procedure, just right til now, after check they work in 
 the worse unoptimized mode...

 example:

 select * from Table1 T1
where T1.id in (select T2.id
  from Table2 T2
  where T2.Customer=:customer)

 of course T2.id is a Primary key and T1.id is a Foreing key

 I think they resolve getting the value(s) in the subselect, and then applying 
 to the main where, but the performace analisys show he run all the T1 table 
 from begin to end and then check with the subselect

 of course I can solve (in procedure) with a FOR subselect to get every value 
 and check after not the nicest way, and pretty ugly and less human 
 readable, but by far much more quick and optimized.

 for select T2.id
from Table2 T2
where T2.Customer=:customer
into :id do
select * from Table1 T1
  where T1.id=:id

 There is some way to get a good result using field in subselect? some trick 
 to get an optimized result using this syntax?



Internally your query is transformed into:

select
*
from
Table1 T1
where
T1.id exists (select
 *
  from
 Table2 T2
  where
 T2.id = T1.id and
 T2.Customer=:customer)


That's why you see a full table scan on T1. Your non correlated sub-query 
becomes a correlated sub-query after the transformation.
  

an easy way to achieve what you want is to convert it to a simple JOIN 
like this:

select
t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer

if the relationship is not 1:1 you would need to avoid duplicates with 
something like:

select
distinct t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer

Some handling for null values should be taken into account if aplicable
  

if it will be faster or not depends on your data.

see you !

[firebird-support] Another Unoptimized question

[skander_sp]

May be obvious, but til now, i don't see how much unoptimized works the field 
in subselect syntax.

I'm using it in some procedure, just right til now, after check they work in 
the worse unoptimized mode...

example:

select * from Table1 T1
  where T1.id in (select T2.id
from Table2 T2
where T2.Customer=:customer)

of course T2.id is a Primary key and T1.id is a Foreing key

I think they resolve getting the value(s) in the subselect, and then applying 
to the main where, but the performace analisys show he run all the T1 table 
from begin to end and then check with the subselect

of course I can solve (in procedure) with a FOR subselect to get every value 
and check after not the nicest way, and pretty ugly and less human 
readable, but by far much more quick and optimized.

for select T2.id
  from Table2 T2
  where T2.Customer=:customer
  into :id do
  select * from Table1 T1
where T1.id=:id

There is some way to get a good result using field in subselect? some trick 
to get an optimized result using this syntax?

Re: [firebird-support] Another Unoptimized question

[fabianoaspro]

Subselects never get optimized in the way you posted.
Bad but truth.
Em 16/04/2013 11:09, skander_sp skander...@yahoo.com escreveu:

 **



 May be obvious, but til now, i don't see how much unoptimized works the
 field in subselect syntax.

 I'm using it in some procedure, just right til now, after check they work
 in the worse unoptimized mode...

 example:

 select * from Table1 T1
 where T1.id in (select T2.id
 from Table2 T2
 where T2.Customer=:customer)

 of course T2.id is a Primary key and T1.id is a Foreing key

 I think they resolve getting the value(s) in the subselect, and then
 applying to the main where, but the performace analisys show he run all the
 T1 table from begin to end and then check with the subselect

 of course I can solve (in procedure) with a FOR subselect to get every
 value and check after not the nicest way, and pretty ugly and less
 human readable, but by far much more quick and optimized.

 for select T2.id
 from Table2 T2
 where T2.Customer=:customer
 into :id do
 select * from Table1 T1
 where T1.id=:id

 There is some way to get a good result using field in subselect? some
 trick to get an optimized result using this syntax?

  



[Non-text portions of this message have been removed]





++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com 

++
Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* Your email settings:
Individual Email | Traditional

* To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

* To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

* To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

* Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/