Maximum no of joins that can be used in a select statement from tables containing huge data for better fetching time(for better performance).What i mean is that if i have a select statement with 50 inner or left joins whether it is better to split the select statement into different select statement with less number of joins(4 or 5 joins) and collect in a data set.
Asked
Active
Viewed 116 times
0
-
For 50 joins the search space will be huge so breaking this up into smaller queries could be beneficial. – Martin Smith Jan 11 '13 at 11:27
-
So what the maximum no of joins provide better performance is it 4?? i mean ,i need to know the efficient no of joins to breakup that query to smaller queries . – Sachin K S Jan 11 '13 at 11:33
1 Answers
0
it depends; the fewer joins you have the better, but temp tables or temp variables have a cost of their own. If you have 50 or so joins in a single SELECT statement, the biggest benefit would come from an examination of your database design; you may be over-normalizing in some areas, or have some quirk that could eliminate soem of your headaches.
However, if you can't affect the design, and are attempting to reduce the number of joins in a single statement, there's no hard maximum number of joins; as a general rule of thumb, I'd probably try to half the number and evaluate performance from there.
Stuart Ainsworth
- 12,792
- 41
- 46