T-SQL Technique for Huge Insert .... Select
Query with No Blocking
I have been looking for a query technique like this on
the web and could not find any, anywhere, so I wrote one myself to solve this
problem.
I have a huge query from business community wanting to
run a report querying over 25 tables with many outer joins and returns 5 million
records.
The query first runs estimated 5 hours. I added 3 indexes now it
runs 10 minutes. However, SQL Server select places "Share Lock" on default Read
Committed Isolation Level.
I don't want these source tables to be locked for
insert update for 10 minutes.
So I use SNAPSHOT Isolation Level by doing the following
command
ALTER DATABSE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION on;
I also don't want the destination table
appRpt1.TableInsert to be on Exclusive Lock for 10 minutes during insert. I
wrote a recursive loop to insert incremental number of rows "@rIncnum" like the
following:
You can put a waitfor command in between to allow time for other
queries on your busy server or use smaller @rIncnum.
Nobody got blocked. All queries are happy. The only
lockes SNAPSHOT Isolation Level obtains is "Schema Stability" and on some locks
on SQL internal objects. That has no effect to queries but makes sure schema is
locked for no changes.
--------------------------------------------------------------------------------------------
ALTER DATABSE AdventureWorks
SET
ALLOW_SNAPSHOT_ISOLATION on
--ALTER
DATABSE AdventureWorks SET READ_COMMITTED_SNAPSHOT on
USE
AdventureWorks
GO
SET
TRANSACTION ISOLATION LEVEL SNAPSHOT
--SET
TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @rnum
bigint; declare @rIncnum bigint;
SET @rnum =
1; SET @rIncnum = 999;
WHILE
(@@ROWCOUNT > 0)
BEGIN
With Q AS (
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS
Row_Number, s.SalesYTD, a.PostalCode FROM Sales.SalesPerson s JOIN
Person.Contact c on s.SalesPersonID = c.ContactID JOIN Person.Address a ON
a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL AND SalesYTD <>
0 )
insert into
appRpt1.TableInsert select * from Q where Row_Number >= @rnum and
Row_Number < @rnum + @rIncnum
if @@ROWCOUNT
= 0 break;
else SET @rnum =
@rnum + @rIncnum
Continue;
END