SQL Server Performance

searches on text

Discussion in 'Performance Tuning for DBAs' started by bfarr23, Apr 1, 2005.

  1. bfarr23 New Member

    what is the fastest way to do full wildcard searches on text?
    %text%

    i know this cannot use an index.
    Full Text Search not appropriate for this.


    any tricks out there?
  2. derrickleggett New Member

    Is this a real text field, or char/varchar? How prevalent is your need for this? How big/comlex are the text strings? What made you decide full text search was a horrible idea?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  3. bfarr23 New Member

    these are nvarchar fields.

    this req. has to happpen within the next 2 months.

    some text strings are 50 in length. Others are 1500.

    my understanding full text search is only good when you have some ideas of what the search text will be that the user enters. correct?
  4. derrickleggett New Member

    Well, there's no efficient way to search with wildcards on both sides of the string unless you either:

    1. Manually build and maintain a keyword table and index system.
    --This is expensive and should only be done if you are on a system that doesn't allow full text.

    2. Full text indexes.
    --Your assumption isn't necessarily correct. As long as they aren't entering a bunch of reserved or "ignored" words, full-text should work fine in your example.

    I would try full-text out. There really isn't a good way to search by %text% without it.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. bfarr23 New Member

    can u elaborate on this?

    As long as they aren't entering a bunch of reserved or "ignored" words, full-text should work fine in your example.
  6. derrickleggett New Member

    There are common words that full-text indexing ignores such as between, and, or, etc. If you try to search on those specific words, then you will have problems because they are considered ignored words. Other than that, I really don't see what the issue would be with using full-text indexes in your case. You can read about the ignored words, and a LOT of other information in Books Online of course. There are also some good webcasts I believe on the microsoft site. <br /><br />Why don't you just try it and see if you like it. That won't cost you anything. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  7. bfarr23 New Member

    cannot use full-text search on a table that has a composite index.

    that bites.





Share This Page