Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Performance 1


T-SQL Performance 1

Author
Message
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18545 Visits: 13248
handkot (2/17/2012)
offtop

The performant alternative:
Create a Tally or Numbers Table


create table dbo.Tally(i int identity(1,1))
go
insert into dbo.Tally DEFAULT VALUES
go 1000000



You're still performing a loop, aka RBAR (row by agonazing row).
The Tally Table from Jeff executes in less than a second, while this little loop executes for over 1 minute on my machine (see attachment). And I changed the code to 11000, I wouldn't like to know what happened if I kept 1000000.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Attachments
statistics.bmp (3 views, 476.00 KB)
John Mitchell-245523
John Mitchell-245523
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8886 Visits: 15483
Got it right, but I think the answer should be "it depends". On what? The selectivity of the data in Col. You get a seek if you populate your table thus:

INSERT INTO TestTable (id, col)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name),
'name' + CAST(ROW_NUMBER() OVER (ORDER BY a.name) AS varchar(7))
FROM master.sys.columns a
CROSS JOIN master.sys.columns b
CROSS JOIN master.sys.columns c


... but a scan if you have the following data in the table:

INSERT INTO TestTable (id, col)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.name),
'name' + CAST((ROW_NUMBER() OVER (ORDER BY a.name))/500000 AS char(5))
FROM master.sys.columns a
CROSS JOIN master.sys.columns b
CROSS JOIN master.sys.columns c


Therefore, in the second case, there'd be no way of optimising the stored procedure.

John
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54348 Visits: 44637
You might also have to remove the ID column to get a seek, depending how selective that column is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


cengland0
cengland0
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 1300
I immediately thought of using a hint to force the use of the index. Is that wrong somehow?

SELECT ID,Col
FROM FirstTable WITH INDEX(IX_Col)
WHERE Col = @Get;

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8748 Visits: 11718
cengland0 (2/17/2012)
I immediately thought of using a hint to force the use of the index. Is that wrong somehow?

SELECT ID,Col
FROM FirstTable WITH INDEX(IX_Col)
WHERE Col = @Get;

Not wrong, just less good. With this hint, I expect you to get an index scan, which could be a bit better than a scan of the clustered index, but still far worse than an index seek.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
cengland0
cengland0
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 Visits: 1300
Hugo Kornelis (2/17/2012)
cengland0 (2/17/2012)
I immediately thought of using a hint to force the use of the index. Is that wrong somehow?

SELECT ID,Col
FROM FirstTable WITH INDEX(IX_Col)
WHERE Col = @Get;

Not wrong, just less good. With this hint, I expect you to get an index scan, which could be a bit better than a scan of the clustered index, but still far worse than an index seek.

The question is: Can I alter the procedure to do an index seek?
So, I immediately thought of adding the hint and that would force the use of the index. I then answered Yes to the question without even considering any other ways to cause the index to work naturally by changing the data type for example.

In other words, I got the question right but not for the reason specified.
danielfountain
danielfountain
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 888
Hugo Kornelis (2/17/2012)
Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.

There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.
Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.


Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?

Perhaps the above is the case - in which case you are getting rid of all conversions.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8748 Visits: 11718
danielfountain (2/17/2012)
Hugo Kornelis (2/17/2012)
Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.

There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.
Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.


Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?

Perhaps the above is the case - in which case you are getting rid of all conversions.

True.
In that case, the best way to improve performance is to fire the nincompoop developer who came up with the crazy idea of making the parameter nvarchar. Hehe


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11114 Visits: 11353
John Mitchell-245523 (2/17/2012)
Got it right, but I think the answer should be "it depends". On what? The selectivity of the data in Col [...] Therefore, in the second case, there'd be no way of optimising the stored procedure.


ALTER PROCEDURE dbo.QOD_Performance_1
@Get VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;

SELECT ft.ID, ft.Col
FROM dbo.FirstTable AS ft WITH (FORCESEEK)
WHERE ft.Col = @Get;
END;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
danielfountain
danielfountain
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 888
Hugo Kornelis (2/17/2012)
danielfountain (2/17/2012)
Hugo Kornelis (2/17/2012)
Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.

There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.
Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.


Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?

Perhaps the above is the case - in which case you are getting rid of all conversions.

True.
In that case, the best way to improve performance is to fire the nincompoop developer who came up with the crazy idea of making the parameter nvarchar. Hehe


Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search