Very slow SQL query.

Question

Very slow SQL query.

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

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

David Kranes SSCrazy Points: 2523 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

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

David Kranes SSCrazy Points: 2523 More actions · Answer 5

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

David Kranes SSCrazy Points: 2523 More actions · Answer 9

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

David Kranes SSCrazy Points: 2523 More actions · Answer 10

David Kranes

SSCrazy

Points: 2523

September 24, 2009 at 12:44 pm

#1058219

Here is the DDL. Thank you.

Best Regards,

~David

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

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)