CODEWITHSUNDEEP
t-sqlsybase
czchlong
czchlong

Reputation: 2574

Improving the performance of the following TSQL code

I am using Sybase DB with TSQL.

The follow snippet of TSQL code is very simple and I need to perform it several 100,000 times (large database) so I would really like to improve its performance in any way possible:

BEGIN TRANSACTION
INSERT INTO
  DESTINATION_TABLE
SELECT
  COLUMNS
FROM
  SOURCE_TABLE
WHERE
  ORDER_ID = @orderId

DELETE FROM
  SOURCE_TABLE
WHERE
  ORDER_ID = @orderId
COMMIT TRANSACTION

As one can see, I am inserting and removing the same set of rows based on the same condition.

Is there a way to improve the performance of this simple query?

Thanks.

Upvotes: 0

Views: 113

Answers (1)

womp
womp

Reputation: 116977

If you are inserting more than a few rows, you really need to do a bulk insert. Calling this method 100,000 times, passing it an ID every time, is a linear-processing mindset. Databases are for set operations.

Construct a temporary table of ID's that you need to insert and delete. Then do a bulk insert by joining on the ID's in that table, and similarly a bulk delete.

Upvotes: 1

Related Questions