Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

SQL query performance issue

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
sqldba2013
Asked:
sqldba2013
4 Solutions
 
PaulCommented:
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
 
DcpKingCommented:
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
 
sqldba2013Author Commented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sqldba2013Author Commented:
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
 
c_kedarCommented:
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
 
sqldba2013Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
sqldba2013Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now