Geek Answers Handbook

Can I optimize this remote SQL update?

I have this sql update statement that updates the remote table. Is there any way to optimize / speed up this code? It works as part of a stored procedure.

DECLARE @WIP Table(Item varchar(25), WIP int) --Get Work In Progress Numbers INSERT INTO @WIP select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP --into #WIP from [NCLGS].[dbo].[AL_ItemUPCs] UPC INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID where PO.status in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile') Group by (UPC.ItemPrefix + '-' + UPC.ItemCode) --SLOW PART, takes over 17 minutes UPDATE [Server].[Database].[dbo].[Item] SET QtyOnHand = IH.QtyOnHand, QtyWorkInProgress = W.WIP FROM Avanti_InventoryHeader IH INNER JOIN [Server].[Database].[dbo].[Item] I ON I.ItemNumber = IH.ItemNumber LEFT JOIN @WIP W ON IH.ItemNumber = W.Item WHERE isnumeric(left(IH.ItemNumber, 2)) = 0
+4
source share
5 answers

I realized the real problem with the query, it updated thousands of records, even if these records did not change. Therefore, I requested changes to the records, saved them in a table variable, and updated only the changed records. The whole procedure (and not just this part) went from 16min 44sec to 1min 26 sec.

 --BEGIN EXPERIMENTAL ITEM UPDATE SECTION DECLARE @WIP Table(Item varchar(25), WIP int) --Get Work In Progress Numbers INSERT INTO @WIP select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP --into #WIP from [NCLGS].[dbo].[AL_ItemUPCs] UPC INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID where PO.status in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile') Group by (UPC.ItemPrefix + '-' + UPC.ItemCode) Declare @Remote Table(Item varchar(25), QtyOnHand int, WIP int) INSERT INTO @REMOTE Select ItemNumber, QtyOnHand, QtyWorkInProgress from [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item] DECLARE @ItemsToUpdate Table (Item varchar(50)) INSERT INTO @ItemsToUpdate Select R.Item From @Remote R Inner join Avanti_InventoryHeader IH ON R.Item = IH.ItemNumber LEFT JOIN @WIP W ON R.Item = W.Item Where R.QtyOnHand <> IH.QtyOnHand OR R.WIP <> W.WIP --Select * from @ItemsToUpdate UPDATE [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item] SET QtyOnHand = IH.QtyOnHand, QtyWorkInProgress = W.WIP FROM Avanti_InventoryHeader IH INNER JOIN [BMPR.COM].[NCL-CustomerPortal].[dbo].[Item] I ON I.ItemNumber = IH.ItemNumber LEFT JOIN @WIP W ON IH.ItemNumber = W.Item WHERE I.ItemNumber in ( Select * from @ItemsToUpdate ) --END EXPERIMENTAL ITEM UPDATE SECTION

Any comments on this method?

0
source

I had a similar problem in the past, and I had to use dynamic SQL to improve performance.

I found out that when I joined the local temp table with the remote SQL table, I brought all the data to the local server and then filtered out what I had in the where statement.

In this case, I would try to pass the entire @WIP variable table as a nested table using SELECT UNION ALL in a dynamic query.

I'm talking about something like this:

  DECLARE @WIP Table(Item varchar(25), WIP int) --Get Work In Progress Numbers INSERT INTO @WIP select (UPC.ItemPrefix + '-' + UPC.ItemCode) As Item, SUM(PO.Quantity) As WIP --into #WIP from [NCLGS].[dbo].[AL_ItemUPCs] UPC INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID where PO.status in ('Assigned', 'New', 'UnAssigned', 'WaitingForFile') Group by (UPC.ItemPrefix + '-' + UPC.ItemCode) declare @SQL VARCHAR(MAX) set @SQL = ' UPDATE [Server].[Database].[dbo].[Item] SET QtyOnHand = IH.QtyOnHand, QtyWorkInProgress = W.WIP FROM Avanti_InventoryHeader IH INNER JOIN [Server].[Database].[dbo].[Item] I ON I.ItemNumber = IH.ItemNumber LEFT JOIN (' select @SQL = @SQL + 'select '''+w.Item+''' as Item, '''+cast( w.WIP as varchar(50))+''' as WIP union all ' from @WIP W set @SQL = @SQL + ' select NULL,0 ) W ON IH.ItemNumber = W.Item WHERE isnumeric(left(IH.ItemNumber, 2)) = 0 ' PRINT @SQL

It does not look very neat, but it can work for you, as it is for me.

+1
source

I will try to load the elements into the local variable of the table first before doing the join.

 DECLARE @WIP Table ( Item varchar(25), WIP int ) --Get Work In Progress Numbers INSERT INTO @WIP select ( UPC.ItemPrefix + '-' + UPC.ItemCode ) As Item, SUM(PO.Quantity) As WIP --into #WIP from [NCLGS].[dbo].[AL_ItemUPCs] UPC INNER JOIN al_PO PO ON PO.UPCID = UPC.UPCID where PO.status in ( 'Assigned', 'New', 'UnAssigned', 'WaitingForFile' ) Group by ( UPC.ItemPrefix + '-' + UPC.ItemCode ) DECLARE @Item TABLE (ItemNumber INT PRIMARY KEY, QtyOnHand INT) SELECT ItemNumber, QtyOnHand FROM [Server].[Database].[dbo].[Item] --SLOW PART, takes over 17 minutes UPDATE [Server].[Database].[dbo].[Item] SET QtyOnHand = IH.QtyOnHand, QtyWorkInProgress = W.WIP FROM Avanti_InventoryHeader IH INNER JOIN @item I ON I.ItemNumber = IH.ItemNumber LEFT JOIN @WIP W ON IH.ItemNumber = W.Item WHERE isnumeric(left(IH.ItemNumber, 2)) = 0

In addition, you can consider the possibility of further restricting updates by deleting records from a table variable that are not updated, and attach only the updated records to the linked server.

 DECLARE @Item TABLE ( ItemNumber INT PRIMARY KEY, QtyOnHand INT, updated BIT DEFAULT ( 0 ), WIP int ) SELECT ItemNumber, QtyOnHand FROM [Server].[Database].[dbo].[Item] UPDATE i SET i.QtyOnHand = ih.QtyOnHand, updated = 1 FROM @item i INNER JOIN Avanti_InventoryHeader IH ON I.ItemNumber = IH.ItemNumber LEFT JOIN @WIP W ON IH.ItemNumber = W.Item WHERE isnumeric(left(IH.ItemNumber, 2)) = 0 DELETE FROM @item WHERE updated = 0 UPDATE I SET QtyOnHand = IH.QtyOnHand, QtyWorkInProgress = IH.WIP FROM [Server].[Database].[dbo].[Item] I INNER JOIN @item IH ON I.ItemNumber = IH.ItemNumber
+1
source

Try creating a stored procedure on a remote server that is called by this stored procedure.

In the remote stored procedure, pull the data you need to the remote server in temporary tables. Then execute UPDATE / JOIN on the remote server.

In my experience, trying to make a connection on a linked server can be very slow ... and it is often faster to get all the necessary data on one server or another before making any connections.

0
source

PostgreSQL UPDATE has an ONLY clause that updates only the specified table. Otherwise, it tries to update all the tables you join, and this could be a bottleneck. What type of SQL are you using? If it's Postgres, maybe some others, try changing the update line to

 UPDATE ONLY [Server].[Database].[dbo].[Item]
-1
source

More articles:


All Articles