T-SQL: How to speed up a JOIN using a CAST & REPLACE expression

Using a JOIN on a string expression will result in long execution times, and especially when that JOIN is based on an expression such as what you have.

So .. what values are in COPA and Inquiries?

Unless you left out part of the query you're doing two table scans (at least).

How many rows are in dbo.ps_requests_asr and how many are in BCAMDB.dbo.CPOA?

Is BCAMDB.dbo.CPOA in a different database (same server) as the ASR table?

Is there anything else you can join on besides inq_num?  

How are these tables indexed?

Did you try dumping the required columns to a temp table and use the temp table in the joins instead

select ASR.*, 
CPOA.[PO Number], CPOA.Revision, CPOA.Amount 
from dbo.ps_requests_asr  ASR with(nolock)
LEFT OUTER JOIN #Temp CPOA with(nolock)
ON CPOA.Inquiries = ASR.inq_num 
ORDER BY ASR.inq_num,CPOA.Revision

Select allOpen in new window

Select * into #Temp
from
(
SELECT
CPOA.[PO Number], CPOA.Revision, CPOA.Amount , CAST(REPLACE(REPLACE(CPOA.Inquiries, '[', ''),']', '') as Inquiries
from BCAMDB.dbo.CPOA CPOA with(nolock)
)X
ORDER BY ASR.inq_num,CPOA.Revision

Select allOpen in new window

Make a temp table to build your cast/replace table then join to ASR. This way you are two smaller queries instead of one giant one. Hopefully there is a where statement in there somewhere or these are small tables. errr dannygonzalez09 beat me to posting, had to help a co-worker and lost out on points.

The temp table idea may help but what will help more is limiting what you need to pull into the temp table (or working table if not using a temp table) with a WHERE clause that will reduce the number of rows to pull in from BCAMDB.dbo.CPOA. I think for anyone to make any more useful suggestions the answers to the questions in the first 2 responses need to be provided. If these are substantial tables then you need some way to limit what data you want to actually work with.

Is the ASR.inq_num column "varchar(50)"?  If not, change the CAST to exactly the same data type as that column.

How many rows are in each table?

Is there an index on ASR.inq_num?

Thanks to all for your suggestions.

@dannygonzalez09:

I have tried with temp table and I got syntax error. Please guide me how to modify below query with temp table option.

select
PRA.region AS [Region],
PRA.market AS [Market],
PRA.quote_name AS [Quote Name],
REPLACE(PRA.Ship_To,'?','') AS [Ship To],
CPON.Amount AS [CPO Value],
CPON.[CPOA Register date] AS [CPO date received in ONE]
FROM ps_requests_asr PRA with(nolock)
LEFT JOIN BCAMDB.dbo.CPOA CPON ON CAST(REPLACE(REPLACE(CPON.Inquiries, '[', ''),']', '') AS VARCHAR(50)) = PRA.inq_num

Thanks.

--