1

I have a table that is used for reporting purposes and data is inserted each time a user runs a report from the web. The insert can vary from a single row to several thousand depending on the parameters of the report. The select statement used for the insert can run for up to 60 seconds for the insert. It has been optimized but due to the complexity of the database i can not tune it further. My question is - when is the table locked for insert? Is it when the stored procedure is called, when the select statement is executed, or when the select statement is finished executing? I would like to limit the time the table is locked so other users are not affected when a large report, up to 50,000 rows, is run.

ex.

INSERT INTO reportTable
SELECT
   Column a,
   Column b
FROM
    Table a
    INNER JOIN
    Table b
       on b.ident = a.Bident

Thank you

Michael J. Lee
  • 12,278
  • 3
  • 23
  • 39
kuda
  • 68
  • 4

1 Answers1

3

Just run this:

ALTER DATABASE [<dbname>] SET READ_COMMITTED_SNAPSHOT ON;

And stop worrying about insert locks blocking reports. See Choosing Row Versioning-based Isolation Levels.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569