CODEWITHSUNDEEP
sqlsql-server
user3476463
user3476463

Reputation: 4575

Slow Join on Varchar

I have a query in sql server 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)

Upvotes: 0

Views: 2535

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

A join on processed columns invariably takes more effort than a join on raw columns. In this case, you can improve performance by using computed columns. For instance, on the first table, you can do:

alter table new_all_renewals add CSN_num as SUBSTRING(t1.RESELLER_CSN,2,11);
create index on new_all_renewals(CSN_num);

This will generate an index on the column, which should speed the query. (Note: you'll need to reference the computed column rather than actually using the function.)

Upvotes: 2

Related Questions