How to Search fast using part of Indexed Fields | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to Search fast using part of Indexed Fields

Hi, I am trying to search left 4 digits of an indexed field, when i write query to search with full value it is very fast but when i search with left(fld1,4) it is very slow. My Table Structure is as follows CREATE TABLE [dbo].[table1](
[fld1] [char](20) NOT NULL,
[fld2] [char](13) NOT NULL,
) ON [PRIMARY]
END
GO CREATE NONCLUSTERED INDEX [IX_table1_fld1] ON [dbo].[tabl1]
(
[fld1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO let suppose there are 2 records in DB fld1 | fld2
ABCDEF | 12344JH
GHFJAJ | 0090sdH Now if i search using SELECT * FROM table1 WHERE fld1=’ABCDEF’ my result is instant, but if i use
SELECT * FROM table1 WHERE LEFT(fld1,4)=’ABCD’ it is taking long time (i never check how long it is going to take, although i wait for 15 min). Keeping in mind i have 7 million records in my table. All suggestions are welcome. Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
Try this format … WHERE fld1 LIKE ‘ABCD%’ The problem with string functions in a WHERE clause is that all rows will be scanned. But the same will happen if the string expression in your LIKE clause starts with a wildcard – so LIKE will not always perform better. Additionally, you don’t seem to have a clustered index on the table, which I think makes the non-clustered index a poor performer.
Adrian,<br /><br />I do have Cluster Index but table structure was big so i create quick and dirty table <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />. Anyway your suggested method is working [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />], you saved my day [8D].<br /><br />Waqar.<br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
]]>