Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.

The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for example.

However if I strip out the first 2 letters ( right(tbl1.id,7) = right(tbl2.id,7) )and join only on the digits the query speed improves greatly. Why is this?

enter image description here

share|improve this question
4  
An easy answer would be that there are missing indexes. But frankly it's impossible to tell without schema, sample queries, and actual execution plans (.sqlplan files, not screenshots, please). I will suggest that if the key always has the first two letters RN, then the prefix doesn't belong there - all that does is make it very hard for SQL Server to narrow down the range of rows it's looking for. – Aaron Bertrand Jan 14 at 13:29
1  
Can you post the actual execution plans? – Martin Smith Jan 14 at 14:29
The actual estimated execution plan is weird as the query that is faster says 100% query cost and the one that is slow says 0% query cost. – davey Jan 14 at 14:59
@davey - Can you post the actual plan XML? That normally means something is awry with estimated vs actual number of rows. Sounds like you may just luck into getting a different plan better suited for the actual number of rows (e.g. maybe hash join rather than nested loops) due to either different cardinality estimates or the use of unsargable predicates in the 2nd version. – Martin Smith Jan 14 at 15:18
Hi Martin, the xml plan is massive to post, but you're right. The slowest query showing 0% query cost has loads of nested loop joins costing 0% and the quicker query has parallelism and hash matches on the joins??? I have no idea what that means though. Is there a section of XML i can post for you to see? – davey Jan 14 at 15:23
show 2 more comments

2 Answers

up vote 3 down vote accepted

Your statistics need updating.

See Statistics, row estimations and the ascending date column.

Recent dates are not proportionately represented in the statistics and so SQL Server underestimates the number of rows that will match the >= 2012-12-31 predicate (as shown by the Estimated no of rows of 1 vs actual 13922). So it chooses a plan with nested loops and a seek on the other table.

The use of right(tbl1.id,7) = right(tbl2.id,7) prevents a seek being used so you get the hash join more suitable for the actual number of rows involved.

If you update the statistics on that column it should choose a more appropriate plan naturally. You might also consider using Trace Flags 2389 & 2390 to make this issue less likely in the future.

share|improve this answer
Is there a way to force the hash match rather than using the nested loop and keep the Key values as they are? (not drop the 2 letter prefix) – davey Jan 15 at 9:20
@davey - If you update the statistics on that column it should choose a more appropriate plan naturally. – Martin Smith Jan 15 at 10:09
up vote 1 down vote

It is a bad idea to have a key that always starts with the same two letters: that means the index has to search all the way to the third character before finding a difference.

Change your data structure to store the number in one field and the RN in another and then have a calculated field to display the whole thing. You can use the numeric field as the PK and have that be what is in the child tables.

Of course make sure your FK is indexed as they are not indexed automatically.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.