March 21, 2017 at 12:07 pm
Hello Experts,
I'm trying to tweak a query a 3rd party supplier wrote a while back. this query runs once every few minutes in the current logic and I'd like to cut the current high run time.
the current query is as follows :
update SBO set sbo.u_wms_status = lv.Status
from
(
select doctype, right(lv.docnum,LEN(lv.docnum) - Charindex('-', lv.docnum )) docentry ,lv.DocNum , max(lv.Status ) status
from Lv_DocStaus LV
where
Lv.Company = 'DD'
and LV.DocType = '17'
and lv.DocNum like '%-%'
group by DocType,docEntry,DocNum
)lv
inner join [SQLSRV].[MAINDB].[dbo].[ODRF] SBO on cast( sbo.docentry as nvarchar(50)) = cast( lv.docentry as nvarchar(50))
and lv.status <> isnull(SBO.u_wms_status,'')
this query runs between linked servers
when I'm commenting out the update and using select on the two columns I need e.g. status/ docentry I get the corresponding matched records (sometimes no records at all query ) real quick (2-3 seconds) , but when using the update it takes up to a minute to complete.
I reverted to using a cursour on the select as follows :
DECLARE @docentry int
DECLARE @status nvarchar
DECLARE ODRFCursor CURSOR FOR
select sbo.docentry,lv.Status
from
[SQLSRV].[MAINDB].[dbo].[ODRF] SBO
INNER JOIN
(
select doctype, right(lv.docnum,LEN(lv.docnum) - Charindex('-', lv.docnum )) docentry ,lv.DocNum , max(lv.Status ) status
from Lv_DocStaus LV
where
Lv.Company = 'DD'
and LV.DocType = '17'
and lv.DocNum like '%-%'
--and right(lv.docnum,LEN(lv.docnum) - Charindex('-', lv.docnum )) = 577
group by DocType,docEntry,DocNum
)lv
ON
cast(sbo.docentry as nvarchar(50)) = cast( lv.docentry as nvarchar(50))
where
lv.status <> SBO.u_wms_status and SBO.u_wms_status is not null
OPEN ODRFCursor
FETCH NEXT FROM ODRFCursor into @docentry, @status
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [SQLSRV].[MAINDB].[dbo].[ODRF] --SBO_ODRF
SET u_wms_status = @status
WHERE DocEntry=@docentry
select @docentry, @status
FETCH NEXT FROM ODRFCursor
END
CLOSE ODRFCursor
DEALLOCATE ODRFCursor
This takes about 6 sec to complete. is this a bad practice?
Is there a better way to tweak the update statement without altering the tables ?
Thank you in advance for your input
March 21, 2017 at 12:30 pm
First, is there a reason for this: cast(sbo.docentry as nvarchar(50)) = cast( lv.docentry as nvarchar(50)). This is non-sargable and precludes the use of indexes.
Second, you are pulling data from the remote server, joining it to data on a local server then updating the data back on the remote server. Is there a possibility to flip this process and run it on the server where the update is being completed and pull the necessary data from the server currently doing the work?
March 21, 2017 at 2:20 pm
Thank you for your reply.
I think the cast is due to the fact that linked server column is int while the local is varchar that contains extra symbols on some of the cells .
I suppose I can try and reverse it with casting to int on the local for values that ISNUMERIC()=1 would that be better ?
I'll look into flipping the query to the remote server but I'm not sure I'll be able to do that since it's being called from a complied 3rd party software.
one way would be to alter the local SP to EXEC the flipped sp on the remote server does this sound reasonable ?
March 22, 2017 at 5:49 am
You're also looking at table scans from this bit of code:
and lv.DocNum like '%-%'
I'm not sure if you can move around that or not in some fashion, but because of that wild card at the start of the LIKE comparison, you'll see scans.
I'd suggest capturing the execution plan for just the SELECT and capturing it for the UPDATE to see what the differences may be. The overall issue with the UPDATE could be caused by things other than your SELECT statement. It might be due to foreign key constraint checks and other things along these lines. The execution plan will help you determine this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2017 at 6:44 am
Lynn Pettis - Tuesday, March 21, 2017 12:30 PMFirst, is there a reason for this: cast(sbo.docentry as nvarchar(50)) = cast( lv.docentry as nvarchar(50)). This is non-sargable and precludes the use of indexes.
Second, you are pulling data from the remote server, joining it to data on a local server then updating the data back on the remote server. Is there a possibility to flip this process and run it on the server where the update is being completed and pull the necessary data from the server currently doing the work?
flipping the query improved the query run time dramatically.
Thank you for the input.
March 25, 2017 at 10:39 pm
Lynn Pettis - Tuesday, March 21, 2017 12:30 PMFirst, is there a reason for this: cast(sbo.docentry as nvarchar(50)) = cast( lv.docentry as nvarchar(50)). This is non-sargable and precludes the use of indexes.
Second, you are pulling data from the remote server, joining it to data on a local server then updating the data back on the remote server. Is there a possibility to flip this process and run it on the server where the update is being completed and pull the necessary data from the server currently doing the work?
Amen to that!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply