August 14, 2014 at 10:49 am
Hi,
I have two SQL Servers L1 & L2, I am trying to delete data from one of the base table in L2 from L1 based on few input parameters in L1.
This process was taking huge amount of time deleting the data and also creating locking & blocking (Table lock).
To work around I create a stored procedured in L2 and moved the code from L1 into this procedure. Calling this new procedure from L1 as EXEC L1.dbname.dbo.procname @vars varchar(max).
My Question:
The @vars variable contains atleast 10,000 or 25,000 or 50,000 to 100,000 characters in length, a comma seperated string. I take this huge string in, split that and insert into a temp table and then finally join with base table in L2 for delete operation.
Does passing huge parameter over the network creates any issue ?
Is there any better approach you can suggest ?
Thank you.
Sun.
August 14, 2014 at 12:22 pm
This is SQL query that is getting executing in L1 (linked server).
DELETE a
FROM L2.dbname.dbo.Remote_Table1 a
WHERE a.ID = @ID
AND EXISTS
(SELECT b.invoiceId
FROM Local_Table1 b
INNER JOIN Local_Table2 c ON c.Idno = b.Idno
WHERE a.ProcessID = @ProcessID
AND b.invoiceId = a.invoiceId
)
I replaced this code was
Exec L2.dbname.dbo.delete_remote_table1 @ID, @invoiceIds
Note:
@ID INT, @invoiceIds VARCHAR(MAX)
Remote_Table1 is a heavily used table lot of DML operations happens.
Thank you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply