Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

T-SQL Performance 1 Expand / Collapse
Author
Message
Posted Friday, February 17, 2012 2:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
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


  Post Attachments 
statistics.bmp (2 views, 476.69 KB)
Post #1253669
Posted Friday, February 17, 2012 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
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

Post #1253670
Posted Friday, February 17, 2012 2:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 42,768, Visits: 35,867
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

Post #1253681
Posted Friday, February 17, 2012 3:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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;
Post #1253709
Posted Friday, February 17, 2012 3:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #1253715
Posted Friday, February 17, 2012 3:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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.
Post #1253720
Posted Friday, February 17, 2012 4:26 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:45 AM
Points: 766, Visits: 840
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.
Post #1253750
Posted Friday, February 17, 2012 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #1253757
Posted Friday, February 17, 2012 4:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 11,194, Visits: 11,137
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
Post #1253758
Posted Friday, February 17, 2012 5:01 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:45 AM
Points: 766, Visits: 840
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!
Post #1253761
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse