May 14, 2013 at 6:14 am
Hello!
I have one stored proc which returns data by querying multiple tables using union clause without any search criteria.
select xyz as Col1 from table1 join table2 ...(total 8 joins)
union
select abc as Col1 from table1 join table3...(total 8 joins)
union
select pqras Col1 from table1 join table4...(total 8 joins)
union
select pqras Col1 from table1 join table5...(total 8 joins)
As per database design Table1 represent supertype and Table3,4,5 represent sub types. Table1 has 1146090 rows. Not sure what to do with this. It showed up as leading in average reads at 14 million per execution and runs for 35 seconds. It is going to get a lot worse looking at the tables it's selecting from. It is basically doing a select across lots of tables, some large tables, without a select criteria. It's doing a bunch of in clustered index scans... appreciate your help here.
Abhijit - http://abhijitmore.wordpress.com
May 14, 2013 at 7:11 am
Abhijit More (5/14/2013)
Hello!I have one stored proc which returns data by querying multiple tables using union clause without any search criteria.
select xyz as Col1 from table1 join table2 ...(total 8 joins)
union
select abc as Col1 from table1 join table3...(total 8 joins)
union
select pqras Col1 from table1 join table4...(total 8 joins)
union
select pqras Col1 from table1 join table5...(total 8 joins)
As per database design Table1 represent supertype and Table3,4,5 represent sub types. Table1 has 1146090 rows. Not sure what to do with this. It showed up as leading in average reads at 14 million per execution and runs for 35 seconds. It is going to get a lot worse looking at the tables it's selecting from. It is basically doing a select across lots of tables, some large tables, without a select criteria. It's doing a bunch of in clustered index scans... appreciate your help here.
How do you want us to help?
Can you drop this proc and never use?
If not, what are the real requirements?
It's impossible to advise on something like that - too little information.
May 14, 2013 at 7:58 am
How do you want us to help? - need help to optimize the query.
Can you drop this proc and never use? - not possible
If not, what are the real requirements? - requirement is to get all record that satisfy the join criteria
It's impossible to advise on something like that - too little information. - can u tell me what information do you need
Abhijit - http://abhijitmore.wordpress.com
May 14, 2013 at 8:16 am
Abhijit More (5/14/2013)
How do you want us to help? - need help to optimize the query.Can you drop this proc and never use? - not possible
If not, what are the real requirements? - requirement is to get all record that satisfy the join criteria
It's impossible to advise on something like that - too little information. - can u tell me what information do you need
Seriously with 900 points you have surely at least one or two forum posts. We can't see your screen, we can't read your mind, we don't know your data structures, we don't know the business rules...in short we don't know anything about your system or your issue.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 8:18 am
Abhijit More (5/14/2013)
How do you want us to help? - need help to optimize the query.Can you drop this proc and never use? - not possible
If not, what are the real requirements? - requirement is to get all record that satisfy the join criteria
It's impossible to advise on something like that - too little information. - can u tell me what information do you need
Details!
I guess you should start from here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 16, 2013 at 3:56 am
sorry friends it my bad....i should have frame the question correctly...but for now I think i found the solution for my problem...sorry for the inconvenience
Abhijit - http://abhijitmore.wordpress.com
May 16, 2013 at 7:09 am
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.
May 16, 2013 at 7:41 am
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.
Why not?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2013 at 7:58 am
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
May 16, 2013 at 8:14 am
John Mitchell-245523 (5/16/2013)
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
But that is only good if you want to eliminate ALL duplicates. It is very possible that you have 3 tables and in the first 2 tables you want duplicates but if the same value shows up in the third table you want only distinct values. Other times maybe you want to include all duplicates.
The blanket statement that "you never use more than one UNION, which is the last one. All the rest should be UNION ALL." is just completely not true. There are time when that is correct but not always.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2013 at 8:16 am
Thanks Sean - that was the point that I was hoping to make.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 16, 2013 at 8:40 am
Sean Lange (5/16/2013)
John Mitchell-245523 (5/16/2013)
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
But that is only good if you want to eliminate ALL duplicates. It is very possible that you have 3 tables and in the first 2 tables you want duplicates but if the same value shows up in the third table you want only distinct values. Other times maybe you want to include all duplicates.
The blanket statement that "you never use more than one UNION, which is the last one. All the rest should be UNION ALL." is just completely not true. There are time when that is correct but not always.
I'm not sure I understand. Those duplicates from the first two tables will be eliminated by the UNION operator. Try this:
SELECT * FROM (VALUES (1), (1), (2)) x(A)
UNION ALL
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION
SELECT * FROM (VALUES (4)) z(C)
John
May 16, 2013 at 8:57 am
John Mitchell-245523 (5/16/2013)
Sean Lange (5/16/2013)
John Mitchell-245523 (5/16/2013)
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
But that is only good if you want to eliminate ALL duplicates. It is very possible that you have 3 tables and in the first 2 tables you want duplicates but if the same value shows up in the third table you want only distinct values. Other times maybe you want to include all duplicates.
The blanket statement that "you never use more than one UNION, which is the last one. All the rest should be UNION ALL." is just completely not true. There are time when that is correct but not always.
I'm not sure I understand. Those duplicates from the first two tables will be eliminated by the UNION operator. Try this:
SELECT * FROM (VALUES (1), (1), (2)) x(A)UNION ALL
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION
SELECT * FROM (VALUES (4)) z(C)
John
try this:
SELECT * FROM (VALUES (1), (1), (2)) x(A)
UNION
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION all
SELECT * FROM (VALUES (4),(3)) z(C)
However, until this sort of functionality is really required, I wouldn't mix both UNION and UNION ALL in one query.
May 16, 2013 at 9:05 am
John Mitchell-245523 (5/16/2013)
Sean Lange (5/16/2013)
John Mitchell-245523 (5/16/2013)
Phil Parkin (5/16/2013)
lnardozi 61862 (5/16/2013)
Just for completeness sake, you never use more than one UNION, which is the last one. All the rest should be UNION ALL.Why not?
I suppose because UNION eliminates duplicates you only need to do it once - as long as you make sure that UNION is the last one evaluated. I don't know whether the query optimizer is smart enough to do that implicitly if you use more than one UNION - so it may or may not make a difference from a performance point of view.
John
But that is only good if you want to eliminate ALL duplicates. It is very possible that you have 3 tables and in the first 2 tables you want duplicates but if the same value shows up in the third table you want only distinct values. Other times maybe you want to include all duplicates.
The blanket statement that "you never use more than one UNION, which is the last one. All the rest should be UNION ALL." is just completely not true. There are time when that is correct but not always.
I'm not sure I understand. Those duplicates from the first two tables will be eliminated by the UNION operator. Try this:
SELECT * FROM (VALUES (1), (1), (2)) x(A)UNION ALL
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION
SELECT * FROM (VALUES (4)) z(C)
John
The difference is that you don't have any values in z that are also in either of the first two tables.
Let's say you wanted distinct values from x and y but you wanted to include duplicates from z if they exist. In this scenario you have to change the order of the UNION and UNION ALL.
SELECT * FROM (VALUES (1), (1), (2)) x(A)
UNION
SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION ALL
SELECT * FROM (VALUES (4), (1)) z(C)
--EDIT--
Didn't see Eugene's post before I hit reply. I agree that you should not mix them unless it is really necessary. It does however prove that the blanket statement posted above is just not true. As with nearly everything in SQL "it depends". 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2013 at 9:08 am
But that's because you changed the order. The proviso was that the UNION operator is the last one to be evaluated. If you use parentheses to make it so, you get the same results as before:
SELECT * FROM (VALUES (1), (1), (2)) x(A)
UNION
(SELECT * FROM (VALUES (2), (3), (3)) y(B)
UNION all
SELECT * FROM (VALUES (4),(3)) z(C))
John
Edit - I think I understand what you're saying. I need to play around and see what happens when you have more than one UNION. I'll see if I get time to do that tomorrow.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply