Reason to use optimizer hints 1

[RichardClarke]

While investigating performance problems within an application recently I carried out some tests using SET SHOWPLAN ON.<br>
<br>
I had a query like this within a stored procedure:<br>
<br>
SELECT MAX(X) FROM Y WHERE Z LIKE @MYVAR<br>
<br>
Where @MYVAR was passed in. I discovered that SQL Server did a Table Scan even when Z had an index on it. A problem with 200,000 rows!<br>
<br>
If I said<br>
<br>
SELECT MAX(X) FROM Y WHERE Z LIKE 'HELLO%'<br>
<br>
(i.e., used a constant instead of a variable) SQL Server did use the index correctly and did not do a table scan.<br>
<br>
I got around this by rewriting my statement:<br>
<br>
SELECT MAX(X) FROM Y (INDEX=MYINDEX) WHERE Z LIKE @MYVAR<br>
<br>
in other words by manually specifying the index I had created on the Z column.<br>
<br>
Hope this helps someone.<br>
<br>

 

[calahans]

I have come across this problem in SQL Server 6.5 with sub queries, that is that SQL server can't handle them and ends up doing table scans. The solution we came up with was to rewrite subselects as joins (or move to version 7!)

 

[johnk]

RichardClarke, Are you running with 7.0?<br>
<br>
Thanks, John K

 

[RichardClarke]

No, I'm using SQL6.5 SP4, which I should have mentioned in my original post.