sqldba2013
asked on
SQL query
The below sql query is taking very long time (20 minutes for 600 records) and I have created required indexes on tables and stil it was taking long time for execution.
Please advise how to optimize/speed-up below query.
Please advise how to optimize/speed-up below query.
UPDATE tempHW
SET [SO Number]= CUST.[SO]
	FROM tempHW INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'
	INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'
JimHorns points are valid, but unless your table has a few million records I wouldn't expect it to take 20mins even with string concatination and wildcard queries.
Sometimes an inner join can be the slower way to solve the problem. Instead you can do a left join and then in the where clause exclude any where the joined table are null (which is in effect an inner join).
How are you creating table tempHW? It appears to be a temporary table, therefore you should be able to create the column '[' + tempHW.[Inquiry Number] + ']' as a natural column which will avoid having to transfer it for the join.
The last thing to do is display the expected execution plan as that should give you a tip as to where the time is being spent and how to optimise it.
Sometimes an inner join can be the slower way to solve the problem. Instead you can do a left join and then in the where clause exclude any where the joined table are null (which is in effect an inner join).
How are you creating table tempHW? It appears to be a temporary table, therefore you should be able to create the column '[' + tempHW.[Inquiry Number] + ']' as a natural column which will avoid having to transfer it for the join.
The last thing to do is display the expected execution plan as that should give you a tip as to where the time is being spent and how to optimise it.
ASKER
Thanks for suggestions.
Yes, tempHW is temp table.
I have modified query with inner join (removed like operator) and i got the output for [SO number] as a NULL (output is wrong).
Pls advise.
Yes, tempHW is temp table.
I have modified query with inner join (removed like operator) and i got the output for [SO number] as a NULL (output is wrong).
UPDATE tempHW
SET [SO Number]= CUST.[SO]
FROM tempHW INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'
INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] = tempHW.[CPO Number] + '%'
Pls advise.
You can't remove the like operator without removing the '%' as well. I didn't have any suggestions for how to change that particular clause.
ASKER
Execution plan for above query:
Index seek (Non-Clustered)
BI_Custmon (_dta_index_BI_CUSTMON_8_5 65577053__ K22_1)
Cost 56%
Index seek (Non-Clustered)
BI_Custmon (_dta_index_BI_CUSTMON_8_5
Cost 56%
Hmmm, that looks like the problem then. Is there a lot of data in that table? Looks like you need an index on [Customer PO].
Why were you using a like statement anyway? Do you really want to match [Customer PO] with [CPO Number] + '%' i.e. followed by anything? Shouldn't it be more exact that that?
Why were you using a like statement anyway? Do you really want to match [Customer PO] with [CPO Number] + '%' i.e. followed by anything? Shouldn't it be more exact that that?
ASKER
I have already created index on [Customer PO].
CREATE NONCLUSTERED INDEX [_dta_index_BI_CUSTMON_8_565577053__K22_1] ON [dbo].[BI_CUSTMON]
(
[Customer PO] ASC
)
INCLUDE ([SO])
I have to match [Customer PO] with [CPO Number] because DB is not having proper data.
Yeah, as JimHorn said, it won't use the index while you are using a like compare.
You must have a massive amount of data in your table for it to take that long. I can't think of anything else to try sorry - its the sort of problem where I need the real data so I can try various things which come to mind - but I don't have any further ideas from what I know so far.
You must have a massive amount of data in your table for it to take that long. I can't think of anything else to try sorry - its the sort of problem where I need the real data so I can try various things which come to mind - but I don't have any further ideas from what I know so far.
ASKER
Pls find the sample records
Inquiries CPO Number Customer PO
========================== ========== ===
[10492511] 4415867 4989289 10050042
[10499517] 4415869 4989289 10050042
[10499454] 4415870 4989289 10050042
[10492511] 4415873 4989289 10050042
[10492517] 4415874 4989289 10050042
[10492512] 4415876 4990579 10110787
[10492513] 4415881 4990579 10110787
[10492519] 4415882 4990579 10110787
[10492520] 4415895 4990579 10110787
[10492511] 4415896 4990579 10110787
Inquiries CPO Number Customer PO
==========================
[10492511] 4415867 4989289 10050042
[10499517] 4415869 4989289 10050042
[10499454] 4415870 4989289 10050042
[10492511] 4415873 4989289 10050042
[10492517] 4415874 4989289 10050042
[10492512] 4415876 4990579 10110787
[10492513] 4415881 4990579 10110787
[10492519] 4415882 4990579 10110787
[10492520] 4415895 4990579 10110787
[10492511] 4415896 4990579 10110787
Sample records from which table(s)? Nothing seems to match there?
ASKER
CPOA: Inquiries,[CPO Number]
bi_custmon: [customer po]
tempHW : temp table
bi_custmon: [customer po]
tempHW : temp table
Sorry I don't understand. Unfortunately I don't have any other ideas.
Would you post?:
all index definitions on all tables;
the xml version of the query plan;
the approx. number of rows in each table; for example, using this t-sql:
select count(*) from tempHW with (nolock)
select object_name(object_id, DB_ID('BCDB')), row_count
from BCDB.sys.dm_db_partition_s tats dps
where
object_id in ( object_id('BCDB.dbo.CPOA') , object_id('BCDB.dbo.BI_CUS TMON') )
all index definitions on all tables;
the xml version of the query plan;
the approx. number of rows in each table; for example, using this t-sql:
select count(*) from tempHW with (nolock)
select object_name(object_id, DB_ID('BCDB')), row_count
from BCDB.sys.dm_db_partition_s
where
object_id in ( object_id('BCDB.dbo.CPOA')
ASKER
Hi ScottPletcher,
I am herewith attaching entire query with execution plan. Please review the query and advise me and we want to reduce the execution time.
#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min
Thanks in advance.
SQLQuery.sql
Plan.sqlplan
I am herewith attaching entire query with execution plan. Please review the query and advise me and we want to reduce the execution time.
#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min
Thanks in advance.
SQLQuery.sql
Plan.sqlplan
Very helpful, but I still need the table and index definitions for the permanent tables, including at least:
[BCDB].dbo.CPOA CPON
[BCDB].dbo.BI_CUSTMON
and perhaps:
REACT.dbo.[MicroWave DB]
just to be more complete.
[BCDB].dbo.CPOA CPON
[BCDB].dbo.BI_CUSTMON
and perhaps:
REACT.dbo.[MicroWave DB]
just to be more complete.
ASKER
If you can, send sqls to reproduce the issue. Include sql to populate tables.
Add the query and the expected output.
For a new contributing expert, he needs to understand the issue without going through all the discussions.
Add the query and the expected output.
For a new contributing expert, he needs to understand the issue without going through all the discussions.
I would start by finding out the bottleneck: is it finding the records, or updating them. If you run
select 1
FROM tempHW
INNER JOIN [BCDB].dbo.CPOA CPON WITH(NOLOCK) ON CPON.Inquiries = '[' + tempHW.[Inquiry Number] + ']'
INNER JOIN [BCDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'
is it as slow as UPDATE?
ASKER
As i said above,
#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min
I believe, #tempHW table or Union operator is causing the performance issue.
I am herewith attaching entire query and please advise me on this issue (how to reduce the execution time).
SQLQuery.sql
#tempSVC : it returns 1825 rows in 5 seconds
#tempHW : it returns 220000 rows in 25 minutes
Union operator : It returns 600 rows in 1 hr 40 min
I believe, #tempHW table or Union operator is causing the performance issue.
I am herewith attaching entire query and please advise me on this issue (how to reduce the execution time).
SQLQuery.sql
At first glance a select from a temp table with 220k rows taking 25 min is rediculous. That's usually the server out of ram and swapping to disk causing a chain reaction of slowness.
@arrontomosky is imho right.
To test it, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to start with cold buffers. Run your query two or three times. How does execution time changes? See SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer. Caveat: This should not be done on production servers under load.
Also use SET STATISTICS IO ON; to see whether we have too much scans and physical reads.
@jimhorn is right about the non-sargeable nature of your JOIN's on your temp table. To use indices here, create an additional inquery column which includes the square brackets. Create an index on this column and use it in the subsequent JOIN's. This should speed up the bunch of 9% queries. Also creating the index after filling the table is normally faster.
And last but not least: The mixture of inquriy# stored with and without brackets is a bad handling. You should stick to one usage only.
To test it, run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to start with cold buffers. Run your query two or three times. How does execution time changes? See SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer. Caveat: This should not be done on production servers under load.
Also use SET STATISTICS IO ON; to see whether we have too much scans and physical reads.
@jimhorn is right about the non-sargeable nature of your JOIN's on your temp table. To use indices here, create an additional inquery column which includes the square brackets. Create an index on this column and use it in the subsequent JOIN's. This should speed up the bunch of 9% queries. Also creating the index after filling the table is normally faster.
CREATE TABLE #tempSVC
(
[Inquiry Number] VARCHAR(255), [CPO Number] VARCHAR(50),
InquiryNumberWithSquaredBrackets VARCHAR(257),
[ID1] float,[Market] VARCHAR(150),[Location] VARCHAR(50),[Request Date] DATETIME,Requestor VARCHAR(510),[HW Quote Generated By] VARCHAR(510),
[NOVA] VARCHAR(510),[Quote Name] VARCHAR(150),[Type1] VARCHAR(510),[Service Type] VARCHAR(510),[Quote Type] VARCHAR(510),
[Site Description] VARCHAR(510),[CASPR] VARCHAR(50),[CASPR Target] VARCHAR(50),[FA Code] VARCHAR(510),Cabinet VARCHAR(510),
[CRM ID] VARCHAR(255),[Contract #] VARCHAR(510),NOTES VARCHAR(510),BATCH float(8),[Final Quote Submitted] DATETIME,
[Quote Need by date] DATETIME,[Urgent Request] bit,[Business Case] VARCHAR(300),[Assigned ASR] VARCHAR(300),[TAQOS Status] VARCHAR(50),
[Total of Lines] FLOAT,[Total Quote Value] MONEY,[TAQOS Submitted Date] DATETIME,[E-mail Submitted Date] DATETIME,[REACT Status] VARCHAR(255),
[REACT Number] VARCHAR(50),[REACT Submitted Date] DATETIME,[REACT Release Date] DATETIME,[Quote Value in REACT] money,
[CPO Rev] VARCHAR(50),[CPO Value] float,[CPO date received in ONE] DATETIME,[SO Number] VARCHAR(50),
[Sales Order $ to date] DECIMAL(18,2),[SO Date] DATETIME,[Network #] VARCHAR(250),MS VARCHAR(510),[Invoice Number] VARCHAR(50),
[Invoice $ to date] decimal(18,2),[Invoice Date] DATETIME,[Invoice $ Delta] DECIMAL(18,2),[Comments] VARCHAR(2000),
SalesOrderCount INT,InvoiceOrderCount INT
);
INSERT INTO #tempSVC ([Inquiry Number], InquiryNumberWithSquaredBrackets, [CPO Number] )
SELECT MWDB.[Inquiry Number],
'[' + MWDB.[Inquiry Number] + ']',
[PO Number]
FROM REACT.dbo.[MicroWave DB] AS MWDB WITH ( NOLOCK )
LEFT JOIN bcamdb.dbo.CPOA WITH ( NOLOCK ) ON Inquiries = '[' + MWDB.[Inquiry Number] + ']'
WHERE MWDB.[Quote Type] = 'SVC'
AND cpoa.[sales org] = 'AT&T'
AND NOT MWDB.[Inquiry Number] IS NULL;
CREATE NONCLUSTERED INDEX ix_InqNum ON #tempSVC ( [Inquiry Number] ) INCLUDE ( [CPO Number] );
CREATE NONCLUSTERED INDEX ix_InqNum2 ON #tempSVC ( InquiryNumberWithSquaredBrackets ) INCLUDE ( [CPO Number] );
And last but not least: The mixture of inquriy# stored with and without brackets is a bad handling. You should stick to one usage only.
ASKER
Hi ste5an,
Thanks for your suggestion.
I have modified script as per your suggestion and there is no change in execution time.
Pls review the attached script with execution plan.
temphw.sql
temphw-plan.sqlplan
Thanks for your suggestion.
I have modified script as per your suggestion and there is no change in execution time.
Pls review the attached script with execution plan.
temphw.sql
temphw-plan.sqlplan
I think you've got a lot redundant updates. Also using RTRIM(LTRIM()) repeatedly as a non-sargable condition is not that good.
Check this one carefully:
Check this one carefully:
INSERT INTO #tempHW
( [Inquiry Number] ,
InquiryNumberWithSquaredBrackets ,
[CPO Number]
)
SELECT MWDB.[Inquiry Number] ,
'[' + MWDB.[Inquiry Number] + ']' ,
[PO Number]
FROM REACT.dbo.[MicroWave DB] AS MWDB WITH ( NOLOCK )
LEFT JOIN bcamdb.dbo.CPOA WITH ( NOLOCK ) ON Inquiries = '[' + MWDB.[Inquiry Number] + ']'
WHERE MWDB.[Quote Type] = 'HW'
AND NOT MWDB.[Inquiry Number] IS NULL;
CREATE NONCLUSTERED INDEX ix_InqNum ON #tempHW ( [Inquiry Number] ) INCLUDE ( [CPO Number] );
CREATE NONCLUSTERED INDEX ix_InqNum2 ON #tempHW ( InquiryNumberWithSquaredBrackets ) INCLUDE ( [CPO Number] );
UPDATE #tempHW
SET ID1 = MWDB.ID1 ,
[Location] = MWDB.Location ,
[Market] = MWDB.market ,
[Quote Name] = MWDB.[quote name] ,
[CASPR] = MWDB.caspr ,
[CASPR Target] = MWDB.[CASPR Target] ,
[Quote Type] = MWDB.[Quote Type] ,
[CRM ID] = MWDB.[crm id] ,
[Urgent Request] = MWDB.[Urgent Request] ,
[Business Case] = MWDB.[Business Case] ,
[TAQOS Status] = MWDB.[TAQOS Status] ,
[Total of Lines] = MWDB.[Total of Lines] ,
[Total Quote Value] = MWDB.[Total Quote Value] ,
[TAQOS Submitted Date] = MWDB.Created ,
[E-mail Submitted Date] = MWDB.[Mail Date] ,
[REACT Status] = MWDB.[REACT Status] ,
[REACT Number] = MWDB.[REACT Number] ,
[REACT Submitted Date] = MWDB.[REACT Submitted Date] ,
[REACT Release Date] = MWDB.[REACT Release Date] ,
[Request Date] = MWDB.[Request Date] ,
[HW Quote Generated By] = MWDB.[HW Quote Generated By] ,
[NOVA] = MWDB.[NOVA] ,
[Type1] = MWDB.[Type1] ,
[Site Description] = MWDB.[Site Description] ,
[FA Code] = MWDB.[FA Code] ,
Cabinet = MWDB.Cabinet ,
NOTES = MWDB.NOTES ,
BATCH = MWDB.BATCH ,
[Final Quote Submitted] = MWDB.[Final Quote Submitted] ,
[Quote Need by Date] = MWDB.[Quote Needed by Date] ,
Requestor = MWDB.Requestor ,
[Service Type] = MWDB.[Service Type] ,
[Contract #] = MWDB.[Contract #] ,
[Assigned ASR] = [UserInfo].[Title] ,
MS = MWDB.MS ,
Comments = MWDB.Comments
FROM #tempHW WITH ( NOLOCK )
INNER JOIN REACT.dbo.[MicroWave DB] MWDB WITH ( NOLOCK ) ON #tempHW.[Inquiry Number] = MWDB.[Inquiry Number]
LEFT JOIN react.dbo.[UserInfo] WITH ( NOLOCK ) ON MWDB.[Assigned ASR] = [UserInfo].[ID];
WITH temp
AS ( SELECT Inquiries ,
[PO Number] ,
MAX(Revision) AS MaxRevision
FROM bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND CPON.[PO Number] = #tempHW.[CPO Number]
GROUP BY Inquiries ,
[PO Number]
)
UPDATE #tempHW
SET [CPO Rev] = temp.MaxRevision
FROM temp
WHERE temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND [PO Number] = #tempHW.[CPO Number];
WITH temp
AS ( SELECT Inquiries ,
[PO Number] ,
MAX(Amount) AS CPOAmount
FROM bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND CPON.[PO Number] = #tempHW.[CPO Number]
GROUP BY Inquiries ,
[PO Number]
)
UPDATE #tempHW
SET [CPO Value] = temp.CPOAmount
FROM temp
WHERE temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND [PO Number] = #tempHW.[CPO Number];
WITH temp
AS ( SELECT Inquiries ,
[PO Number] ,
MAX([Received Date]) AS ReceivedDate
FROM bcamdb.dbo.CPOA CPON WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND CPON.[PO Number] = #tempHW.[CPO Number]
GROUP BY Inquiries ,
[PO Number]
)
UPDATE #tempHW
SET [CPO date received in ONE] = temp.ReceivedDate
FROM temp
WHERE temp.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
AND [PO Number] = #tempHW.[CPO Number];
UPDATE #tempHW
SET [SO Number] = RTRIM(LTRIM(CUST.[SO])) ,
[SO Date] = CAST(CUST.[SO Created On] AS DATE) ,
[Network #] = CUST.Network
FROM #tempHW WITH ( NOLOCK )
INNER JOIN [BCAMDB].dbo.CPOA CPON WITH ( NOLOCK ) ON CPON.Inquiries = #tempHW.InquiryNumberWithSquaredBrackets
INNER JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH ( NOLOCK ) ON CUST.[Customer PO] LIKE #tempHW.[CPO Number] + '%';
CREATE NONCLUSTERED INDEX ix_InqNum3 ON #tempHW ( [SO Number] );
WITH temp
AS ( SELECT [SO Number] ,
COUNT([SO Net Value]) AS SOTOTAL
FROM bcamdb.dbo.SAP_ZVBAK BAKSales WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON BAKSales.[SO] = #tempHW.[SO Number]
GROUP BY [SO Number]
)
UPDATE #tempHW
SET [Sales Order $ to date] = temp.SOTOTAL ,
SalesOrderCount = temp.SOTOTAL
FROM temp
WHERE temp.[SO Number] = #tempHW.[SO Number];
WITH temp
AS ( SELECT [SO Number] ,
SUM([Net Value]) AS SOTOTAL
FROM [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON SAP_ZVBRPINV.SO = #tempHW.[SO Number]
GROUP BY [SO Number]
)
UPDATE #tempHW
SET [Invoice $ to date] = temp.SOTOTAL ,
InvoiceOrderCount = temp.SOTOTAL
FROM temp
WHERE temp.[SO Number] = #tempHW.[SO Number];
UPDATE #tempHW
SET [Invoice Number] = [Billing Document] ,
[Invoice Date] = [Created On]
FROM [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV WITH ( NOLOCK )
INNER JOIN #tempHW WITH ( NOLOCK ) ON SAP_ZVBRPINV.SO = #tempHW.[SO Number];
UPDATE #tempHW
SET [Invoice $ Delta] = [Sales Order $ to date] - [Invoice $ to date];
UPDATE #tempHW
SET [SO Number] = '' ,
[SO Date] = '' ,
[Network #] = ''
WHERE SalesOrderCount > 1;
UPDATE #tempHW
SET [Invoice Number] = '' ,
[Invoice Date] = ''
WHERE InvoiceOrderCount > 1;
UPDATE #tempHW
SET [Request Date] = CASE WHEN [Request Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [Request Date]
END ,
[Final Quote Submitted] = CASE WHEN [Final Quote Submitted] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [Final Quote Submitted]
END ,
[Quote Need by date] = CASE WHEN [Quote Need by date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [Quote Need by date]
END ,
[TAQOS Submitted Date] = CASE WHEN [TAQOS Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [TAQOS Submitted Date]
END ,
[E-mail Submitted Date] = CASE WHEN [E-mail Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [E-mail Submitted Date]
END ,
[REACT Submitted Date] = CASE WHEN [REACT Submitted Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [REACT Submitted Date]
END ,
[REACT Release Date] = CASE WHEN [REACT Release Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [REACT Release Date]
END ,
[CPO date received in ONE] = CASE WHEN [CPO date received in ONE] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [CPO date received in ONE]
END ,
[SO DAte] = CASE WHEN [SO DAte] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [SO DAte]
END ,
[Invoice Date] = CASE WHEN [Invoice Date] <= '1900-01-01 00:00:00.000' THEN NULL
ELSE [Invoice Date]
END;
UPDATE #tempHW
SET [REACT Status] = PR.[status] ,
[REACT Number] = PR.Req_id ,
[REACT Submitted Date] = PR.submitted_date ,
[REACT Release Date] = PR.req_del_date ,
[Inquiry Number] = PR.mus_inq_num
FROM #tempHW WITH ( NOLOCK )
INNER JOIN REACT.dbo.ps_requests PR WITH ( NOLOCK ) ON PR.mus_inq_num = #tempHW.InquiryNumberWithSquaredBrackets;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for rewriting script.
I tried with above query and now it was taking 1 hr 20 minutes for 220000 rows
Old script execution time is 25 minutes for 220000 rows.
Any other suggestions?
I tried with above query and now it was taking 1 hr 20 minutes for 220000 rows
Old script execution time is 25 minutes for 220000 rows.
Any other suggestions?
Id Start with using resource monitor and watch ram and disk queue. Some SQL tracing things were already listed above.
ASKER
--
In order for that index to be used, it has to be a Search Argument (SARG)-able value, which afaik a text concatenation is not.
>ON CUST.[Customer PO] LIKE tempHW.[CPO Number] + '%'
Also, any wildcard will be slow, and I'm guessing a wildcard in a JOIN will really be slow.