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
Solved

SQL query performance issue

Posted on 2014-03-02
8
233 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

792 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