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

sqldba2013Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sqldba2013Author Commented:
--
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.