Query improvement

Question

Query improvement

Krishna1

Hall of Fame

Points: 3666
More actions
June 25, 2013 at 10:02 pm

#279166

Dear All
I have folloing query
select sum(yy) sumyy, 'summary line' xx
from tablea
where somedate_column < @startdate_parameter
union
select yy, xx
from tablea
where somedate_column between @startdate_parameter and @enddate_parameter
Is there a better way to achive this or this is the ok
Regards
Krishana

Viewing 13 posts - 1 through 12 (of 12 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Krishna1 (6/25/2013)
Dear All
I have folloing query
select sum(yy) sumyy, 'summary line' xx
from tablea
where somedate_column < @startdate_parameter
union
select yy, xx
from tablea
where somedate_column between @startdate_parameter and @enddate_parameter
Is there a better way to achive this or this is the ok
Regards
Krishana

Pretty hard to tell if that is the best approach or not. We can't see your tables, don't know the data or the requirements for the project. In addition, what you posted is only a portion of what appears to be a stored proc.

_______________________________________________________________

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/

jkassay SSC Journeyman Points: 99 More actions · Answer 2

Krishna1 (6/25/2013)
Dear All
I have folloing query
select sum(yy) sumyy, 'summary line' xx
from tablea
where somedate_column < @startdate_parameter
union
select yy, xx
from tablea
where somedate_column between @startdate_parameter and @enddate_parameter
Is there a better way to achive this or this is the ok
Regards
Krishana

I agree with what Sean Lange said. The only thing that you can do, which isn't much, is to change the BETWEEN condition in the second part of the UNION to be:

WHERE somedate_column >= @startdate_parameter and somedate_column <= @enddate_parameter

This is what the optimizer does in the background anyway, so save it the need to do so.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Depending on the requirements you might be able to use UNION ALL instead of UNION which is certainly a performance improvement.

_______________________________________________________________

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/

Krishna1 Hall of Fame Points: 3666 More actions · Answer 4

Dear All

Table structure is as follows

Create table tablea(

Yy numeric(13,3),

Xx varchar(100),

somedate_column datetime)

What i wanted was like cube or rollup is there a way to make single query rather than union

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 5

Are you using Index well on the table?

please specify

Neeraj Prasad Sharma
Sql Server Tutorials

HanShi SSC-Dedicated Points: 33508 More actions · Answer 6

Krishna1 (6/26/2013)
What i wanted was like cube or rollup is there a way to make single query rather than union

These functions are available in SQL Server (2008+). Take a look at http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

So it would be something like:

SELECT

xx

, sum(Yy) as total

FROM tablea

GROUP BY ROLLUP (Xx)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Krishna1 Hall of Fame Points: 3666 More actions · Answer 7

Dear Hanshi

Thanks for your reply. But if you see my query it has got differnt where clasues for the 2 query hence can not use roll up.

There is no performace issue as idexex are defined.

I only wnated to see if the same query could be written in any other better way.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

I would change your UNION to a UNION ALL. It is better for performance and in your case you probably want to get both result sets even if they are the same.

_______________________________________________________________

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/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Is the somedate_column indexed? What indexes do you have?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

HanShi SSC-Dedicated Points: 33508 More actions · Answer 10

Krishna1 (6/27/2013)
But if you see my query it has got differnt where clasues for the 2 query hence can not use roll up.

I overlooked that the WHERE clause is different. In that case I don't think there is another way to write the query. But I agree with Sean Lange to replace your UNION with the UNION ALL statement if your resultset doesn't have to be GROUPED BY.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Why read the table twice?

SELECT

sumyy = sum(yy),

xx = ???('summary line') -- where ??? is an aggregate function

FROM tablea

CROSS APPLY (

SELECT SurrogateGroup = CASE

WHEN somedate_column < @startdate_parameter THEN <<constant value>>

WHEN somedate_column between @startdate_parameter and @enddate_parameter THEN <<tablea.PK>>

ELSE NULL end

) x

WHERE SurrogateGroup IS NOT NULL -- or use somedate_column <= @enddate_parameter

GROUP BY SurrogateGroup

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

ChrisM@Work (6/27/2013)
Why read the table twice?
SELECT
sumyy = sum(yy),
xx = ???('summary line') -- where ??? is an aggregate function
FROM tablea
CROSS APPLY (
SELECT SurrogateGroup = CASE
WHEN somedate_column < @startdate_parameter THEN <<constant value>>
WHEN somedate_column between @startdate_parameter and @enddate_parameter THEN <<tablea.PK>>
ELSE NULL end
) x
WHERE SurrogateGroup IS NOT NULL -- or use somedate_column <= @enddate_parameter
GROUP BY SurrogateGroup

Careful with this approach Chris, the constant scan generated is often worse than just hitting the table twice depending on indexes, data size etc. I do always like to test that approach when doing something that needs a "working" column, but it often loses out in my environment.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/