SQL Server Performance Forum – Threads Archive
Like ‘%xxx%’
Hi all,Have a table with filenames as one of the fields.
fieldcontent is somthing like ‘SOMEHEADER20070618.xml’ Select statement is:
SELECT somthingelse FROM table WHERE filename like ‘%20070618%’ In my understanding a index is not very helpful.
What’s the best way to query this field ?
Building index on substring (only possible if filenamelenght is always the same?) TIA
acki
Perhaps add a separate column with the date only?
How big is this table, I mean number of rows? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
satya,
table contains 250’000 rows
on field is a asc nonclustered index. cpu: ~ 1900
reads: ~ 7691
duration: ~ 500 15 selects like this are fired all 30 sec.
means in one minute
~15 sec. cpu goes ~100%
15 sec’s idle
15 sec’s high
…
Can you change the file naming convention from ‘SOMEHEADER20070618.xml’ to ‘20070618SOMEHEADER.xml’? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
If so, you can use like ‘20070618%’ that can make use of Index Madhivanan Failing to plan is Planning to fail
Hi all,
Thx for your support guys but I cannot change the file naming convention.
The only solution I see is to add an additional column or if possible add an index
on substring of filename column. (if filenamelength is always the same) Thx anyway
acki
The filename length is irrelevant. If your convention now is as you’ve posted, one possible solution would be:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @filename VARCHAR(30)<br />SET @filename = ‘SOMEHEADER20070618.XML'<br />SELECT SUBSTRING(@filename, CHARINDEX(‘.’, @filename, 1) – 8, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br />——–<br />20070618<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
… yes, but no solution for the lack of benefit from an index.<br /><br />Perhaps you could add a calculated column:<br /><br />ALTER TABLE mytable<br />ADD [FileDate] AS SUBSTRING(FileName, CHARINDEX(‘.’, FileName, 1) – 8, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />GO<br /><br />You can also add an index on the calculated column:<br /><br />CREATE INDEX idxMyTable_FileDate ON mytable (FileDate)<br />GO<br />
Frank,<br />I don’t get your point?<br />I have a column called filename with content like SOMEHEADER20070618.XML and a query with … WHERE filename like ‘%20070618%'<br />In this scenaria all rows are scanned and the existing index on filename does not help.<br />To speed things up I’m planning to implement an index on substring to extract the datepart (2006061<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> of filename.<br />In this case the length is relevant – isn’t it?<br /><br />
My point was in response to your comment
quote:
(if filenamelength is always the same)
The length of the filename doesn’t matter at all as you can see by my script. It was just to show how the extraction into a computed column or extra column might look like. It doesn’t make a search any more effective.
(if filenamelength is always the same)
—
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Indexed computed column seems like a good solution to me. I’ve done something similar in the past.
]]>