Query performance

Question

Query performance

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
July 28, 2015 at 2:34 am

#302803

I have these SQL statements inside a WHILE loop :
SET @StartDate1 = (SELECT MIN(A.startdate) FROM #Activities2 AS A WHERE activityIncId = @ActId )
SET @EndDate1 = (SELECT MAX(A.enddate) FROM #Activities2 AS A WHERE activityIncId = @ActId)
I am changing it to:
SELECT @StartDate1 = MIN(A.startdate), @EndDate1 = MAX(A.enddate) FROM #Activities2 AS A WHERE activityIncId = @ActId
Will this have any improvement on the performance point of view?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I'd say yes, but test it to be sure.

If you really want to improve performance though, you probably need to get rid of the WHILE loop. That's just a cursor with a different name.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Junglee_George (7/28/2015)
I have these SQL statements inside a WHILE loop :
SET @StartDate1 = (SELECT MIN(A.startdate) FROM #Activities2 AS A WHERE activityIncId = @ActId )
SET @EndDate1 = (SELECT MAX(A.enddate) FROM #Activities2 AS A WHERE activityIncId = @ActId)
I am changing it to:
SELECT @StartDate1 = MIN(A.startdate), @EndDate1 = MAX(A.enddate) FROM #Activities2 AS A WHERE activityIncId = @ActId
Will this have any improvement on the performance point of view?

Oddly enough I suspect that this one will be counter to first blush thoughts.

If you are optimized for this (i.e. not partitioned table with some form of index that allows a seek to get both the min and max, having 2 queries or one query will be an exceptionally small difference (just the "overhead", if you will, of the 2 executions).

However, if you are NOT optimized (i.e. you are doing a table scan for both operations) then combining them holds the chance of a 50% improvement in that it is possible that both the min and max can be acquired with a single scan with the second query.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 3

Hi Kevin

It will be useful if you can explain me with some code change example, in the way that will benefit the query performance.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Junglee_George (7/29/2015)
Hi Kevin
It will be useful if you can explain me with some code change example, in the way that will benefit the query performance.

You provided the code change already. I just explained how it might be beneficial (or not) depending on whether or not your query was optimized in the first place (with an index that allowed a seek for each operation). I don't know that I can explain it better without a whiteboard to demonstrate how indexes work. Sorry.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service