Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Slow "Like" Query

    i want to have a like search in the following query.

    SELECT DISTINCT TOP 200 a.AccountID,
    a.AccountNumber,
    c.CLI,
    con.SurName,
    addr.Address1 [Account Address],
    addr.Postcode as [Account Postcode],
    atp.Name AS Type,
    cs.Code AS Status
    FROM account_t a
    INNER JOIN customer_t cust on a.customerID = cust.CustomerID
    INNER JOIN AccountType_T atp on cust.AccountTypeID = atp.AccountTypeID
    INNER JOIN CustomerStatus_T cs ON a.CustomerStatusID = cs.CustomerStatusID
    INNER JOIN Contacts_T con on cust.MasterContactID = con.ContactID
    INNER JOIN Address_T addr ON cust.MasterAddressID = addr.AddressID
    LEFT OUTER JOIN CLI_T c ON a.AccountID = c.AccountID
    WHERE (c.CLI LIKE @CLI + '%')
    AND (con.SurName LIKE @Surname + '%')
    AND (addr.Address1 LIKE @Address + '%')
    AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%')
    AND c.DateArchived IS NULL

    here all fields @CLI, @Surname, @Address, @Postcode are varhcar types...which is making this query very slow...is there any suggestion to improve this query?

    thanks in advance
    bhavya

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do your indexes look like?

    varchar is not making it slow btw...

    This

    AND (REPLACE(addr.Postcode, ' ', '') LIKE @Postcode + '%')


    Will cause a scan because it's a stage 2 predicate...why are you doing that?

    Also what are the volume of the tables (how many rows each)?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    If you could do some testing to find out which portion is causing the problem.
    break the query into sub-portions, execute each of them, observer the time consumption and execution plan ( to check whether it's taking the right indexes).

    I think most likely the problem existing in the like cluase segment. like clause could not able to pick up index, not mention that you have so many wildcard.

    Think about populate the result of the like clause into temp table or table variable first, then only do the join with the original table.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    i think you can break it down into 2 functions and join them. one function can retrieve account and contacts info based on surname (would also include account type and customer status), and the other will pull out address info based on passed address and zip code. but you do need to get rid of your replace function.

    another way would be to put all passed parameters into a temp table and use it in your join. and since it's "and inner..." you can retain this logic while joining the temp table.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ClaireHsu
    I think most likely the problem existing in the like cluase segment. like clause could not able to pick up index, not mention that you have so many wildcard.
    Not true...

    Col1 LIKE '%something%'

    will cause a scan

    Col1 LIKE 'something%'

    will not cause a scan

    Still need to see the indexing strategy and the number of rows per table...

    Parsippany? I used to work in Roseland...used to make a quick run up to a good turish place....what's the name of the road...starts with a B

    Good deal...$7.00 shepards sald....gyro or whatever and a soda...

    Best humus I ever had....

    in Newark now

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    Sorry, Query Analyzer wont pick up if the leading character in a LIKE clause is a wildcard.

    I am pretty new to Parsippany, but I like this place. Beverwyck Rd you are talking about?

    Newark, I stayed there for 3 weeks 3 yrs ago. All I think was moving away asap.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure what you mean by :The Query Analyzer won'y pick it up"

    Do you mean the Optimizer?

    Well a leading wildcard will cause a scan, that's true (kinda said that already)

    Code:
    USE Northwind
    GO
    
    --Seek
    SELECT [Name] FROM sysobjects WHERE Name Like 'O%'
    --Scan
    SELECT [Name] FROM sysobjects WHERE Name Like '%O%'

    But this is bizzare...

    Code:
    select * from sysobjects where name like '%fil%'
    
    declare @lsFilt3 varchar(16)
    select @lsFilt3 = '%fil%'
    select * from sysobjects where name like @lsFilt3
    Would you not agree that both of these should scan?

    Try it out....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ClaireHsu
    I am pretty new to Parsippany, but I like this place. Beverwyck Rd you are talking about?

    Newark, I stayed there for 3 weeks 3 yrs ago. All I think was moving away asap.
    Yeah...Beverwyck ..that's it...Somewhere near the firehouse is that restaurant..I think it's called bosphorous......could be wrong...

    I know they deliver....

    Newark...only work here...live in W.O.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    So it's not always ture for those tips written in book or web-site rearding to query optimization.
    Query optimizer should certainly be smarter than what we think about and the way we try to dictate it around.

    It's a good news that Query Optimizer still pick up the clustered index in this wildcard situation.

    Good job, query optimizer!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, that's a little bit bizarre.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    That's just freakin kewl. Gotta love tests like that.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok, so the first one does a scan and the second does a seek.. is that correct? Or is there more to that which my untrained eye is missing...
    Last edited by Seppuku; 05-27-04 at 02:55.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •