Passing huge parameter to linked server

• SriSun

  Say Hey Kid

  Points: 684

  More actions

  August 14, 2014 at 10:49 am

  #289047

  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.

• SriSun

  Say Hey Kid

  Points: 684

  More actions

  August 14, 2014 at 12:22 pm

  #1738173

  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.