Execution order of joins and where clauses

[thegeezza]

Morning,

Is a where clause applied to a data set before or after the join operation in the subquery bellow.

update Llyods_dedupe_template
set comp_add_dupe='T'
where key1 in
(select B.key1 from Llyods_dedupe_template A
left join Llyods_dedupe_template B on
A.gr_id1=B.gr_id1 and A.gr_id2=B.gr_id2 and A.gr_id3=B.gr_id3

where A.gr_id1!='00000000' and a.gr_id2!='00000000' and a.gr_id3!='00000000' and
A.source in ('LLYODS') and
B.source in ('CONDTU','CON-top150k','CORPD','THOM'))


The reason I ask is becuase if I remove all records that have a gr_id='00000000', the query executes in under 10 minutes.
Leaving the records within the table gives an estimated cost for the query of 329. It has been running for 21 hours now without completing.

My thinking is that the join operation is being performed for all records that have gr_id's of '00000000', which is 568664. That's a lot of joins.

Can anyone suggest what is happening here?

Regards,
Geezza

 

[miq]

select B.key1 from Llyods_dedupe_template A 
        left join Llyods_dedupe_template B on 
        A.gr_id1=B.gr_id1 and A.gr_id2=B.gr_id2 and A.gr_id3=B.gr_id3

    where     A.gr_id1!='00000000' and a.gr_id2!='00000000' and a.gr_id3!='00000000' and
        A.source in ('LLYODS') and 
        B.source in ('CONDTU','CON-top150k','CORPD','THOM')

This is too much! and and and.... too much of and
secondly, why use left join why not inner join.

I don't know DB schema of yours or the actual data but i guess this could give you some breath.

See if this code can do what you actulaly wants

select B.key1 from Llyods_dedupe_template A 
        left join Llyods_dedupe_template B on 
        A.gr_id1=B.gr_id1 
    where     
        A.gr_id1!='00000000' and
        A.source in ('LLYODS') and 
        B.source in ('CONDTU','CON-top150k','CORPD','THOM')

the lesser the conditions the better the performance. secondly, you can go for a compiste index on joins / where columns.

B.R,
miq

 

[SQLSister]

Sounds like you are affecting a lot of records. THis FAQ might help.
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes faq183-3141



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[thegeezza]

Thank you all for your replies.

MIQ, you are correct, I should be using an inner join.


SQLSister is there a typical loop structure I can make use of to recursively execute the batch update code block (outlined in the faq you kindly provided) until there are no more updates to be applied?

Thanks,
Geezza

 

[SQLSister]

Terry's FAQ has everything you need to use his solution. I believe the loop structure is inthere. Been awhile since I've read it though.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief