T-SQL Performance 1

Question

T-SQL Performance 1

Ron McCullough

SSC Guru

Points: 63877
More actions
February 16, 2012 at 8:11 pm

#268449

Comments posted to this topic are about the item T-SQL Performance 1 at:
http://www.sqlservercentral.com/Questions
Edited to include the following at 8:23 AM Feb 17, 2012
HAPPY DAYS ARE HERE AGAIN
One of, or perhaps the major objective of the QOD is to teach. In many cases, there is more teaching performed in the discussion, then in the supporting reference(s) cited, as is the case with this question.
No matter how you answered the question, if you drop into this discussion to add a comment I strongly urge you to read on. The comments so far are in themselves a wonderful lesson.
And for that I thank SQL Kiwi, Hugo Kornelis and others to numerous to mention.
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Viewing 15 posts - 1 through 15 (of 56 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

It is certainly very good practice to match data types, but that is not the only way to obtain a seek here (assuming the default collation is a Windows one like Latin1_General_CI_AS rather than a SQL one like SQL_Latin1_General_CP1_CI_AS):

CREATE PROC QOD_Performance_1

@Get NVARCHAR(20)

AS

SELECT ID,Col

FROM FirstTable WITH (FORCESEEK)

WHERE Col = @Get;

GO

EXECUTE dbo.QOD_Performance_1

@Get = N'XYZ'

Wondering how this works, or what the Constant Scan and Compute Scalar are for? I wrote about the hard work put in by the optimizer with these sorts of data type mismatches here:

http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Nice question. I immediately chose "yes", because it would have been quite sad if you couldn't force a seek somehow 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

M&M SSC-Insane Points: 21700 More actions · Answer 3

M&M

SSC-Insane

Points: 21700

February 17, 2012 at 12:28 am

#1448299

Good question

M&M

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Loundy SSCertifiable Points: 6080 More actions · Answer 5

Hi All,

Interesting question. I think i must be doing something wrong when testing this myself though.

I've created the table and index like so:

CREATE TABLE TestTable (ID INT, Col VARCHAR(100));

GO

CREATE INDEX IX_Col ON TestTable (Col);

GO

Then created the procedure:

CREATE PROCEDURE Test_Procedure

@Get NVARCHAR(20)

AS

SELECT ID, Col

FROM TestTable

WHERE Col = @Get;

Then populated the table with some random data:

DECLARE @int INT;

SET @int = 1;

WHILE @int <100

BEGIN

INSERT INTO TestTable (id, col)

VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));

SET @int += 1;

END

Now, this does result in a table scan with the CONVERT_IMPLICIT operator.

If I then change the stored proc to use VARCHAR (or VARCHAR(100)) it still results in a table scan - however the CONVERT_IMPLICIT is gone.

Is this the correct behaviour? or am I doing something silly....

Cheers, 🙂

[font="Times New Roman"]There's no kill switch on awesome![/font]

handkot SSCertifiable Points: 5181 More actions · Answer 6

I have a table containg a large number

DECLARE @int INT;

SET @int = 1;

WHILE @int <=1000

BEGIN

INSERT INTO TestTable (id, col)

VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));

SET @int += 1;

I Have Nine Lives You Have One Only
THINK!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

handkot (2/17/2012)
I have a table containg a large number
DECLARE @int INT;
SET @int = 1;
WHILE @int <=1000
BEGIN
INSERT INTO TestTable (id, col)
VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));
SET @int += 1;

The performant alternative:

Create a Tally or Numbers Table

[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

handkot SSCertifiable Points: 5181 More actions · Answer 8

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

I Have Nine Lives You Have One Only
THINK!

Loundy SSCertifiable Points: 6080 More actions · Answer 9

Loundy (2/17/2012)
Hi All,
Interesting question. I think i must be doing something wrong when testing this myself though.
I've created the table and index like so:
CREATE TABLE TestTable (ID INT, Col VARCHAR(100));
GO
CREATE INDEX IX_Col ON TestTable (Col);
GO
Then created the procedure:
CREATE PROCEDURE Test_Procedure
@Get NVARCHAR(20)
AS
SELECT ID, Col
FROM TestTable
WHERE Col = @Get;
Then populated the table with some random data:
DECLARE @int INT;
SET @int = 1;
WHILE @int <100
BEGIN
INSERT INTO TestTable (id, col)
VALUES (@int, 'name' + CAST(@int AS VARCHAR(100)));
SET @int += 1;
END
Now, this does result in a table scan with the CONVERT_IMPLICIT operator.
If I then change the stored proc to use VARCHAR (or VARCHAR(100)) it still results in a table scan - however the CONVERT_IMPLICIT is gone.
Is this the correct behaviour? or am I doing something silly....
Cheers, 🙂

Ok, it was because my table wasn't big enough 🙂 having 10000 rows in there demonstrated the behaviour correctly 🙂

[font="Times New Roman"]There's no kill switch on awesome![/font]

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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 SSCertifiable Points: 6102 More actions · Answer 13

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 SSC Guru Points: 64780 More actions · Answer 14

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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/