February 17, 2012 at 3:18 am
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,ColFROM 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.
February 17, 2012 at 4:26 am
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.
February 17, 2012 at 4:58 am
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:
February 17, 2012 at 4:59 am
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
SQLkiwi blog
@SQL_Kiwi
February 17, 2012 at 5:01 am
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!
February 17, 2012 at 5:09 am
SQL Kiwi (2/17/2012)
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;
That would force a seek (as asked in the QotD question), but it would not optimise the procedure (as John indicates).
</nitpicking>
February 17, 2012 at 5:13 am
Hugo
I was about to make precisely that point. Paul is quite right, since the question asks how to get the proc to do an index seek. But it is often inadvisable to overrule the query optimizer - you'd want to do plenty of testing before you put anything like that live.
John
February 17, 2012 at 5:13 am
good question!!!
thanks!
February 17, 2012 at 5:34 am
danielfountain (2/17/2012)
Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option!
We're big on "Quality Initiatives" where I work, and our biggest customer is always sending along a Six Sigma Black Belt to work on them. However, my idea for a simple, but effective, quality program has yet to be adopted: "hire fewer idiots" :w00t:
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
February 17, 2012 at 5:36 am
Koen Verbeeck (2/16/2012)
Nice question. I immediately chose "yes", because it would have been quite sad if you couldn't force a seek somehow 🙂
Yes, I answered "yes" immediately, as I want to live in a world where it's possible. The leading nature of the question may be responsible for the high number of correct answers. However, thanks to OP for the question! Excellent discussion has resulted.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
February 17, 2012 at 5:44 am
Hugo Kornelis (2/17/2012)
SQL Kiwi (2/17/2012)
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;
That would force a seek (as asked in the QotD question), but it would not optimise the procedure (as John indicates).
</nitpicking>
:laugh: That's beyond nitpicking, even in QotD comments!. I was primarily responding to the statement highlighted above (that the answer should be "it depends"). Clearly the only correct answer to the question "Can I alter the procedure to do an index seek?" is yes.
If I were feeling nitpicky myself, I would have pointed out earlier that the original procedure and table (as written in the question) is likely to use a seek anyway, assuming the database (or table column) has a Windows collation, and the optimizer decides a seek would be best.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 17, 2012 at 6:29 am
I only was able to answer the question correctly by assumption.
If the answer was no, then would there be a need to have an explanation. Every question has an explanation so therefore the answer is yes.
February 17, 2012 at 7:10 am
Good question, thanks. Great follow on discussion.
February 17, 2012 at 7:41 am
Nice question! I chose "Yes" because I felt you can apply hints to this query to force the engine to use the index.
Kwex.
February 17, 2012 at 7:50 am
SQL Kiwi (2/16/2012)
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):
Even worse, it isn't even a guaranteed way to obtain a seek: if the table is small enough, optimiser can pick a scan regardless of whether the implicit conversion is there or not. Maybe degree of selectivity of the where clause (which depends on the frequency distribution of values for that field in the table) has an effect too.
I guess FORCESEEK always works, though (?). Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".
Tom
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply