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 Thursday, February 16, 2012 8:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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

Before posting a performance problem please read
Post #1253572
Posted Thursday, February 16, 2012 10:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 9,928, Visits: 11,194
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.jpg (520 views, 40.93 KB)
Post #1253587
Posted Thursday, February 16, 2012 11:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 13,520, Visits: 11,313
Nice question. I immediately chose "yes", because it would have been quite sad if you couldn't force a seek somehow



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 #1253599
Posted Friday, February 17, 2012 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Good question

Mohammed Moinudheen
Post #1253639
Posted Friday, February 17, 2012 12:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1253643
Posted Friday, February 17, 2012 1:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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, :)


There's no kill switch on awesome!
Post #1253660
Posted Friday, February 17, 2012 1:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:21 PM
Points: 1,380, Visits: 482
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;
Post #1253662
Posted Friday, February 17, 2012 1:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 13,520, Visits: 11,313
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




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 #1253663
Posted Friday, February 17, 2012 1:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 10:21 PM
Points: 1,380, Visits: 482
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

Post #1253667
Posted Friday, February 17, 2012 2:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, September 6, 2014 2:15 PM
Points: 1,597, Visits: 1,153
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 :)


There's no kill switch on awesome!
Post #1253668
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse