|
|
|
SSCrazy Eights
        
Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 9,367,
Visits: 6,465
|
|
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?
Member of LinkedIn. My blog at LessThanDot.
 MCSA SQL Server 2012 - MCSE Business Intelligence
|
|
|
|
|
SSCarpal Tunnel
       
Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 4,422,
Visits: 7,180
|
|
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
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 5:43 AM
Points: 37,678,
Visits: 29,933
|
|
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 2008, MVP 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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299
|
|
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;
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 5,235,
Visits: 7,035
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354,
Visits: 1,299
|
|
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.
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Yesterday @ 1:47 AM
Points: 494,
Visits: 563
|
|
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.
|
|
|
|
|
SSCertifiable
       
Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 5,235,
Visits: 7,035
|
|
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.
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
|
|
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 SQL Server MVP SQLblog.com @SQL_Kiwi
|
|
|
|
|
SSC-Addicted
      
Group: General Forum Members
Last Login: Yesterday @ 1:47 AM
Points: 494,
Visits: 563
|
|
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. 
Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option!
|
|
|
|