December 7, 2006 at 12:11 am
Hi to all i am working on Code Optimisation of Stored Procedures.
but right now i am facing how to improve the joins so that i can achive the fast query result in a fraction of time . Suppose i have two table Which is as follows
Title, TitleSearch
the first table holds 12 lakh records and other table holds 19 lakh records. there are two field which joins these table data , one is Category and other is Titleid which binds these tables............
I have following joins statements which give the fast results......
Title T, TitleSearch TS
where T.Category = TS.Category and T.Titleid = TS.Titleid and T.Titleid = 1256321 and T.Category = 'SBV'
Alternative
Title T, TitleSearch TS
Where T.Category = TS.Category and T.Titleid = TS.Titleid and T.Titleid = 1256321 anb T.Category = 'SBV' and TS.Titleid = 1256321 and TS.Category = 'SBV'
Alternative
Title T, TitleSearch TS
Where T.Category = 'SBV' and T.Titleid = 1256321 and TS.Category = 'SBV' and TS.Titleid = 1256321
According to some groups and experts 2nd Alternative is give fast result but why . the same thing u r repeating , what is the benefit of doing so.
is there any other alternative of join which make my result fast. If you face the join related problem and find the right solution then please post me your reply for checking and understanding ...............
Because a poor joining condition hits your data base . and which make your programmbility very poor......
Expert suggestion will be heartly appreciated........
December 7, 2006 at 5:01 am
The second one could be faster because the server can check its statistics more quickly.
I would suggest using inner join instead of Title T, TitleSearch TS
Select ...
from title T inner join titlesearch TS
on T.Category = TS.Category
and T.Titleid = TS.Titleid
and T.Titleid = 1256321 and T.Category = 'SBV'
Also check if there are indexes on the join columns.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply