SQL Server Performance

Like '%xxx%'

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by acki4711, Jun 19, 2007.

  1. acki4711 Member

    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
  2. Adriaan New Member

    Perhaps add a separate column with the date only?
  3. satya Moderator

    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.
  4. acki4711 Member

    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
    ...
  5. FrankKalis Moderator

  6. Madhivanan Moderator

    If so, you can use like '20070618%' that can make use of Index

    Madhivanan

    Failing to plan is Planning to fail
  7. acki4711 Member

    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

  8. FrankKalis Moderator

    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>
  9. Adriaan New Member

    ... 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 />
  10. acki4711 Member

    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 />
  11. FrankKalis Moderator

    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.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  12. merrillaldrich New Member

    Indexed computed column seems like a good solution to me. I've done something similar in the past.

Share This Page