June 12, 2014 at 7:14 pm
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)
June 13, 2014 at 1:21 am
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."
June 13, 2014 at 4:08 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2014 at 11:11 am
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?
June 13, 2014 at 11:30 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply