Solved

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

Posted on 2013-11-05
10
466 Views
Last Modified: 2013-11-07
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
Comment
Question by:sqldba2013
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39624106
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
 
LVL 8

Expert Comment

by:virtuadept
ID: 39624565
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
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39624570
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
 
LVL 2

Expert Comment

by:burdit
ID: 39624588
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:virtuadept
ID: 39624607
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39625611
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
 

Author Comment

by:sqldba2013
ID: 39626305
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
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 250 total points
ID: 39626532
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
 

Author Closing Comment

by:sqldba2013
ID: 39629782
--
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now