SQL query performance issue

sqldba2013
sqldba2013 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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.
Commented:
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

Author

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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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.
Commented:
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.

Author

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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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.

Author

Commented:
--

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial