That is the trouble with the LIKE keyword in your WHERE clause. From
BOL:
Another important consideration in using wildcards is their effect on
performance. If a wildcard begins the expression, an index cannot be
used. (Just as you wouldn't know where to start in a phone book if given
the name '%mith', not 'Smith'.) A wildcard in or at the end of an
expression does not preclude use of an index (just as in a phone book,
you would know where to search if the name was 'Samuel%', regardless of
whether the names Samuels and Samuelson are both there).
If you disallow the use of the first percentage sign, as it seems you
do, it helps a bit, but your poor old db engine still has to look at all
the entries that start with your letter.
If you make it an equals instead of a like, then it can use the index
properly.
If you helped things along by requiring some other field in the filter,
that had an index on it, that would also help, by cutting down on the
number of rows it had to look through
Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272
-----Original Message-----
From: prakash
[mailto:mssqldba-ezmlmshield-x91607615.[Email address protected]
Sent: Thursday, July 29, 2004 2:47 PM
To: LazyDBA Discussion
Subject: How to make this query run fast !!!!1
Hi
I have one problem with the index selection on query.
I have a table called Item . The table description is as follows:-
CREATE TABLE [Items] (
[autItemID] [int] IDENTITY (1, 1) NOT NULL ,
[vchItemNumber] [varchar] (255) NOT NULL ,
[vchName] [varchar] (255) NOT NULL ,
[vchDescription] [varchar] (1000) NULL ,
[intState] [tinyint] NOT NULL CONSTRAINT [DF_WD_Items_intState]
DEFAULT
(1),
[datCreatedOn] [smalldatetime] NOT NULL CONSTRAINT
[DF__WD_Items_datCreatedOn] DEFAULT (getdate()),
[datModifiedOn] [smalldatetime] NULL ,
CONSTRAINT [PK_WD_Items] PRIMARY KEY CLUSTERED
(
[autItemID]
)
)
GO
All queries are working fine. And giving me index seeks. But I have one
query which searches for the items on vchName field.
SELECT autItemID,vchItemNumber,vchName,vchDescription,intState
FROM DBo.Items
WHERE vchName LIKE @vchLetter + '%'
This query does the index scan. And I don't want index scan as there are
more than 1,000,000 items in the table. I know this will do index scan
because vchName don't have any Index. I tried creating non clustered
index
on vchName and this will also will not work because the query is range
query.
So, can anybody give me some alternate solution to make the query fast.
Prakash Zalkikar
SQL DBA
WebDirekt India Pvt LTD
C 1/19 Kumar City, Kalyani Nagar
Pune - 411014
Phone(O):- 91-20-27031240
Phone(R):- 91-20-27034557
Mobile:- 9422314041
mailto:- p.[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page