joining to itself

Question

joining to itself

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
March 1, 2005 at 7:49 pm

#60237

I have a problem tunning a query which self join itself like;
select aud.inv_id as change_value_1, aud.inv_id as change_value_2, x.trust_xref_code as old_value, aud.trust_xref_code as new_value,aud.inf_date_changed
from inf_inventory aud, inf_inventory x
where aud.action = 'UPDATE'
and x.inv_id = aud.inv_id
and x.trust_xref_code!= aud.trust_xref_code
and x.action in ('UPD', 'INS')
and x.inf_id in (select max(y.inf_id) from inf_inventory y where y.inv_id = aud.inv_id and y.inf_idand aud.inf_date_change between (2 dates parameters)
The query has been writen a long time ago and till the table was small it was OK, but now the cost is vewry high and it's too slow. How could I rewrite it to optimize the speed? There are good indexes on all joint fields.
Ani idea is greatly appreciate it.
Thanks,
MJ

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

Hi Mj,

Very Good Morning. I would change the query like this. I hope it would improve the performance to a certain extent as you are saying all the columns are indexed. Please try it.

Declare @inf_id varchar(100)

select @inf_id = max(y.inf_id)

from inf_inventory y

where exists

(select 1 from inf_inventory aud

where y.inv_id = aud.inv_id

and aud.inf_date_change between (2 dates parameters)

select aud.inv_id as change_value_1, aud.inv_id as change_value_2, x.trust_xref_code as old_value, aud.trust_xref_code as new_value,aud.inf_date_changed

from inf_inventory aud

where exists

(select 1

from inf_inventory x

where x.inv_id = aud.inv_id

and x.trust_xref_code != aud.trust_xref_code

and x.action in ('UPD', 'INS')

and x.inf_id = @inf_id)

Thanks,

Ganesh

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 2

Hi Ganesh,

good morning to you too but I'm almost ready to go to bed...

This improved the performance - very good thinking. Thank you. In the same time, I redesigned the query to use a temp table. It's very fast and no overhead, but I'll test your suggestion again tomorrow and may be i'll replace it.

Thanks again.

MJ

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 3

One more complication - I cannot replace the single query for the report with procedure as I was thinking. I can not touch the report and it needs a simple select statement.

More ideas, please,

Thanks,

MJ

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 4

Could you please send your procedure ?

Thanks,

Ganesh

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 5

This is the query from my initial posting. It's an old report and I cannot change anything else then the select statement, but it still need to return multiple resultset at once. (I cannot even access the source of the report... fun,fun,fun).

select aud.inv_id as change_value_1, aud.inv_id as change_value_2, x.trust_xref_code as old_value, aud.trust_xref_code as new_value,aud.inf_date_changed

from inf_inventory aud, inf_inventory x

where aud.action = 'UPDATE'

and x.inv_id = aud.inv_id

and x.trust_xref_code!= aud.trust_xref_code

and x.action in ('UPD', 'INS')

and x.inf_id in (select max(y.inf_id) from inf_inventory y where y.inv_id = aud.inv_id and y.inf_idand aud.inf_date_change between (2 dates parameters)

Thanks a lot.

MJ

philcart SSC-Forever Points: 47795 More actions · Answer 6

You should re-write the query using the ANSI join syntax (INNER JOIN, OUTER JOIN, etc...).

If you use a derived table instead of an IN clause in the WHERE statement, SQL will be able to optimize the query better.

EG:

select aud.inv_id as change_value_1, aud.inv_id as change_value_2
, x.trust_xref_code as old_value, aud.trust_xref_code as new_value
, aud.inf_date_changed
from inf_inventory aud 
inner join inf_inventory x 
on x.inv_id = aud.inv_id


inner join (
      select max(y.inf_id) as inf_id from inf_inventory y 
      where y.inv_id = aud.inv_id 
      and y.inf_id 
      and aud.inf_date_change between (2 dates parameters) 
    ) as z
    on x.inf_id = z.inf_id
where aud.action = 'UPDATE'
and x.trust_xref_code != aud.trust_xref_code
and x.action in ('UPD', 'INS')

--------------------
Colt 45 - the original point and click interface