Post reply

Very slow SQL query.

  • September 23, 2009 at 1:13 pm

    #1057727

    The current collation is Latin1_General_BIN. Would there be any benefit in changing this possibly atthe column level? If so, to what? Also, you will notice in the following query we are using the RTRIM function. This causes very poor performance. In Oracle it is easy to create a function based index. Is there a work around for this in SQL Server other than using computed columns? I would like to see it taken out completely and the RTRIM done when the data is loaded. We are looking into that as well.

    SELECT customer.customer_no, customer.customer_id, upper(first_name) 'first_name', upper(last_name) 'last_name',

    telephone_no, address_1, post_code, email_address , address_match_key

    FROM customer (index (customer_x2)), address (index (address_x0))

    WHERE rtrim(alpha_key) = N'BEDOYA TERE'

    and customer.customer_id=address.customer_id

    AND customer.active_address_id=address.address_id

    and address_match_key LIKE N '07093%'

    Best Regards,

    ~David

  • September 23, 2009 at 4:11 pm

    #1057805

    David Kranes (9/23/2009)

    Sorry for the delay in providing this. I have attached the DDL.

    Where? Not on that post...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 23, 2009 at 4:25 pm

    #1057809

    Jeff Moden (9/23/2009)

    David Kranes (9/23/2009)

    Sorry for the delay in providing this. I have attached the DDL.

    Where? Not on that post...

    It was there, I opened directly, but I since deleted what I had, sorry.

  • September 24, 2009 at 11:15 am

    #1058168

    I would have thought the the word LIKE would have tipped things off. I thought that LIKE almost always performed a table scan. Nobody asked about how many rows in the tables.

    ATBCharles Kincaid

  • September 24, 2009 at 11:26 am

    #1058174

    Mr. Kincaid,

    there are over 10 million rows in the tables. When forced to use the correct indexes, the LIKE clause poses no problems. The results return immediately.

    Best Regards,

    ~David

  • September 24, 2009 at 11:43 am

    #1058179

    David.

    OK. Like I said "almost always". Are you always needing the % at the end of the string? If so then I would try ranging. (WHERE first_name>='Charles') If it needs to be anywhere then you might be stuck with the hints.

    I'm sort of flying blind and shooting from the hip as I don't see any attachements for the plans or DDL. I've not had a perfect experience since moving to IE-8.

    Please feel free to give the "Mr." a rest. Since the rest of the crowd on these forums have called me out using my first name you can join in too.

    ATBCharles Kincaid

  • September 24, 2009 at 12:05 pm

    #1058185

    Could you post the execution plan of the query without the index hints? That would probably be much more helpful in solving the problem. What appears to be happening is that the Query Optimizer doesn't believe that the existing indexes are good enough for this query. It is possible tht you may need a new index on each of the tables involved.

    I'd also be interested in knowing a little more about the actual quesry as well, such as how is it used? Is it actually in a stored procedure that is used to pass parameters to the query?

  • September 24, 2009 at 12:06 pm

    #1058186

    Also, could you repost the DDL? I had them for a while yesterday, but I opened them directly and did not save it on my system.

  • September 24, 2009 at 12:43 pm

    #1058218

    Lynn, this query is actually generated dynamically through a stored procedure based on input from the application. I have attached the query plan.

    Best Regards,

    ~David

  • September 24, 2009 at 12:44 pm

    #1058219

    Here is the DDL. Thank you.

    Best Regards,

    ~David

  • September 24, 2009 at 5:19 pm

    #1058315

    David Kranes (9/24/2009)

    Here is the DDL. Thank you.

    There real problem is that the "Active_Address_ID" has no index on it nor is it a part of any other index, yet you're using it as a part of the join criteria in the WHERE clause. Adding the correct index should make the lookup virtually instantaneous.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply