0

Stored procedure 1 has a select query returning a record set, dates to be specific. I am using a cursor to go through that record set and for each row another stored procedure is called.

Stored procedure 2 inserts about 20K rows into a table for each value from the cursor.

Since there are about 100 records in the cursor, total number of rows inserted amounts to 200K, which makes the query run for days until it's stopped in production.

The same query takes about 8 minutes in dev.

I tried using foreach container in SSIS (dev) and this takes 5 minutes now (dev).

Is there a faster way of inserting these records?

I considered using table valued function but the join between the two is difficult considering the first record set contains only dates.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1175126
  • 179
  • 1
  • 9
  • 21
  • Is the amount of the data same in production and dev? If so, why is it slower? – James Z Jan 21 '17 at 19:08
  • 1
    Inserting 200k rows, even inside a cursor should not take days. – James Z Jan 21 '17 at 19:09
  • Do you witness the same performance in the production system even when there is near-zero other usage of the system? – Joel Mansford Jan 22 '17 at 00:57
  • The amount of data is same in dev and Prod. However, since Prod is a busy server, queries that run smooth and fast on dev become unusually slow on Production. I see a lot of suspended queries blocking each other on production. – user1175126 Jan 24 '17 at 18:44

1 Answers1

0

Dpending on what stored procedure 2 is doing it's probably worthwhile to look at bulk insert.

See: https://www.simple-talk.com/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/

You may also want to review indexes, and configuration of the prod environment to ensure optimal performance of the load. The link above has some suggestions on how to improve insert performance.

So definitely worth a read.

Bravax
  • 10,453
  • 7
  • 40
  • 68