?
Solved

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

Posted on 2013-11-05
10
Medium Priority
?
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 66

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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:Scott Pletcher
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 1000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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