#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0

    how to increase performance?


    Hi, I am using this MS SQL statement:

    "SELECT * FROM dbo.Products INNER JOIN dbo.InvPrice ON dbo.Products.ProductCode = dbo.InvPrice.ProductCode WHERE (dbo.Products.ProductCode LIKE '%" +Prod__Search2+ "%' AND Supplier LIKE '" + Prod__Select2+ "' OR Description LIKE '%" + Prod__Search2+ "%' AND Supplier LIKE '" + Prod__Select2+ "') AND (dbo.InvPrice.PriceCode = 'AA')"

    For an asp search page, but the results takes a long time to display. If I take out the wildcard '%' eg. i change (...LIKE '%" +Prod__Search2+ "%'...) to (...LIKE '" + Prod__Search2+ "'...) for an exact phrase search, the results show up instantaneous, but I don't want an exact phrase search.

    My database consists of 250,000 records. Any solutions?

    Thanks.
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    The moment you do something like this:
    LIKE '%" +Prod__Search2+ "%'

    then SQL has no choice, but to do a full table scan, because you have wildcards on both sides of the expression. If you did something like this instead:
    LIKE Prod__Search2+ "%'
    then the SQL engine could potentially use an index, since the wildcard is only on the right. However, since you have wild cards on both sides of the words to be searched, there's no way to prevent a table scan.

    The only solution that occurs to me is if you can break up the phrase into individual words and then xref back to the phrase table. Then u can search for the words with = instead of LIKE and make the joins much faster.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0
    What if the search is not for a phrase but for a code? eg. PC-AK04
    PC09232, PC-add32, etc... and when the user types in PC, all those queries containing PC in front would come up.

    I have tried useing '%' on one side only, but the performance is still slow.

    thanks.

IMN logo majestic logo threadwatch logo seochat tools logo