• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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

In below SQL statement, CAST & REPLACE command is causing performance issue and it was taking very long time to give output.

Please suggest me which command I can use in place of CAST & REPLACE to minimize the query execution time.

select ASR.*,
CPOA.[PO Number], CPOA.Revision, CPOA.Amount
from dbo.ps_requests_asr  ASR with(nolock)
LEFT OUTER JOIN BCAMDB.dbo.CPOA CPOA with(nolock)
ON CAST(REPLACE(REPLACE(CPOA.Inquiries, '[', ''),']', '') AS VARCHAR(50)) = ASR.inq_num
ORDER BY ASR.inq_num,CPOA.Revision
0
sqldba2013
Asked:
sqldba2013
  • 2
  • 2
  • 2
  • +3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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?
0
 
virtuadeptCommented:
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?
0
 
dannygonzalez09Commented:
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

Open 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

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
burditCommented:
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.
0
 
virtuadeptCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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?
0
 
sqldba2013Author Commented:
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.
0
 
dannygonzalez09Commented:
give this a try.. As virtuadept mentioned this approach would be more helpful if you can limit the number of rows you are inserting into the temp table by using a Where clause

creating a clustered index on inquiries column in temp table and inq_num column in ps_requests_asr table might also help

ex: CREATE CLUSTERED INDEX CI_Inquiries ON ##Temp (Inquiries)

SELECT Amount,[CPOA Register date],CAST(REPLACE(REPLACE(CPON.Inquiries, '[', ''),']', '') AS VARCHAR(50)) as Inquiries
INTO ##Temp
FROM BCAMDB.dbo.CPOA CPON

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 ##Temp CPON ON CPON.Inquiries = PRA.inq_num

Open in new window

0
 
sqldba2013Author Commented:
--
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now