Slow Join

Question

Slow Join

scotsditch

Ten Centuries

Points: 1020
More actions
June 12, 2014 at 7:14 pm

#287442

I have a query with a join that is taking forever. I'm hoping someone might have a tip to speed it up.
I think the problem is I'm joining on a field called Reseller_CSN which has values like '_0070000050'
I've tried using the substring function in the join to return everything but underscore, example '0070000050' but I keep getting an error when I try to cast or convert the result to int or bigint.
Any tips would be greatly appreciated, the query is below:
select
t1.RESELLER_CSN
,t1.FIRST_YEAR_RENEWAL
,t1.SEAT_SEGMENT
,t2.Target_End_Date_CY
,t2.Target_End_Date_PQ
,t2.Target_End_Date_CY_1
,t2.Target_End_Date_CY_2
,t1.ASSET_SUB_END_DATE
from dbo.new_all_renewals t1
left join dbo.new_all_renewals_vwTable t2 on SUBSTRING(t1.RESELLER_CSN,2,11) = SUBSTRING(t2.RESELLER_CSN,2,11)

Viewing 5 posts - 1 through 4 (of 4 total)

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

free_mascot One Orange Chip Points: 27168 More actions · Answer 1

Have you checked the query plan? Can you share the query plan too?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Functions on columns like that are a common code smell. They will prevent the use of statistics or indexes and will result in full table scans to satisfy the criteria. You need to eliminate the functions. Either do the joins straight or run a calculation to create an additional column in your table that will allow you to run the join straight. There's no way to tune the query with those functions in place.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

scotsditch Ten Centuries Points: 1020 More actions · Answer 3

Hi thanks for getting back to me so quickly on that. When I ran the query before without the substring function it ran really slowly also. Is there anything you would suggest, without the substring function?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Without the execution plan, I couldn't tell you why the other method ran slowly. It could be that the foreign key constraint isn't trusted. This would be because the WITH NOCHECK command is enabled on the constraint. It could be that you need an index on the column on one or both tables. Might be something else.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software