Solved

SQL query performance issue

Posted on 2014-03-02
8
231 Views
Last Modified: 2014-03-07
Hi,

Below query is taking very long time for update. Please advise how to rewrite below query for better performance.
UPDATE	#tempHW 
SET [SO Number]= CUST.[SO] 
FROM #tempHW INNER JOIN dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + #tempHW.[Inquiry Number] + ']'    
INNER JOIN dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%'

Open in new window

0
Comment
Question by:sqldba2013
8 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 63 total points
ID: 39899619
I suspect must get rid of this inefficient join:

      INNER JOIN dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%'

in fact both joins involve inefficiencies (the other join involves a concatenation).

Why can't your temporary table be formed with more precise data for these relationships?

&

Are you indexing this temp table at all? Note you haven't provided many details for us to look at.
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 62 total points
ID: 39899621
It looks like you're doing a lot of implicit type conversions. What type (int, bigint, varchar, ...) is CPON.Inquiries. Hopefully it's some numerical like int or bigint. If so, change it in your temp table (#tempHW) to match. OTOH, if you're wrapping the Inquiry Number with "[" and "]" 'cos it's done like that in CPOA, then do a pre-query to change the field content in every row of the temp table #tempHW - it'll be lots quicker than doing it row by row during the query!

Also, why are you doing this:

CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%'

I would have thought that wither your customer number should match or not match, and that you were only interested in the ones that matched, so changing that to

CUST.[Customer PO] = #tempHW.[CPO Number]

will speed things up tremendously.

Finally, I'd advice putting an index on your temp table:

create index idx_tempHW on #tempHW([Inquiry Number], [CPO Number])

and then run the query. You should find that you don't need the "(nolock)" hints (which are, really, when you think about it) just a way of saying "get me an answer - I don't care if it's right or not" !)

hth

Mike
0
 

Author Comment

by:sqldba2013
ID: 39899630
I have already created index for temp table

CREATE nonclustered INDEX ix_InqNum ON #tempHW ([Inquiry Number]) include ([CPO Number])

The Inquiries  column contains "[" and "]" in the table itself.
Ex:
[123456]
567889
[99848]

Column Data type details:
CPOA.[Inquiries] varchar(450)
#tempHW.[Inquiry Number] VARCHAR(255)
#tempHW.[CPO Number] VARCHAR(50)
BI_Custmon.[Customer PO] varchar(50)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:sqldba2013
ID: 39899636
test data of  physical table:

BI_Custmon.[Customer PO]
**************
5330589 10103749
5327945 10090971
5327945 10090971
5330593 10068658

CPOA.Inquiries
******
[5330589]
[10506317]
[10506320]
[5330593]

Please advise how to rewrite above query based on above data.
0
 
LVL 2

Assisted Solution

by:c_kedar
c_kedar earned 62 total points
ID: 39899685
As commented already by other experts, both the joins involve inefficiencies.
We need to work on how to minimize/eliminate those inefficiencies.

CPON.Inquiries = '[' + #tempHW.[Inquiry Number] + ']'
      Either add column in dbo.CPOA CPON table, of type number, to hold Inquiry Number without brackets, create index on this column and use it in join.
      Alternatively, add a column in temp table tempHW, of type varchar, to hold Inquiry Number bracketed by square brackets, create index on this column and use it in join. This, I think is more acceptable solution as it it does not modify the structure of any permanent tables for de-normalization for performance.

CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%'
      I think this join may not be as costly as the first one. If query continues to be slow after implementing the change suggested above, then
      Add column in CUST table to hold first part of PO Number separately, create index on this column and use in join.
      Sadly, I can not think of solution where you can make some change in temp table structure instead of main CUST table.

Do you experience same slowness for select query with same joins?
If not, then I guess issue is somewhere else.
If yes, then use select query to do the experimentation suggested above.
0
 

Author Comment

by:sqldba2013
ID: 39899713
Hi C_Kedar,

I am herewith attaching entire query with execution plan output. Please review the attached query and suggest me with updated query.

Thanks in advance.
Query.txt
Plan.sqlplan
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 63 total points
ID: 39901825
Store the [Inquiry Number] in #tempHW during initial load in the same format as it will be joined in.  In fact, all other functions -- such as RTRIM, LTRIM, etc. -- should also be done once during the initial load, not in every join afterward.

Also, use a clustered index on the temp table, not a nonclustered.

But, for this time, since the temp table and its index already exists, we have to correct that first before the main query:

DROP INDEX ix_InqNum ON #tempHW

UPDATE #tempHW
SET
    [Inquiry Number] = '[' + [Inquiry Number] + ']',
    <column_1> = LTRIM(RTRIM(<column_1>))
    ,...

CREATE CLUSTERED INDEX ix_InqNum ON #tempHW ([Inquiry Number])


Then run your UPDATE above.
0
 

Author Closing Comment

by:sqldba2013
ID: 39912113
--
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

775 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