CODEWITHSUNDEEP
sql-serverperformanceloopsinsertwhile-loop
user2814724
user2814724

Reputation: 11

Slow insert with "While Exists" loop

I'm trying to insert a lot of records to a table. This is the scenario:

  1. SQL Server 2008 (DB is 2005)

  2. The destination table has a Clustered Index (PK). This field should be an Identity, but the developer of the DB (we couldn't change it, as it will affect the program) create it as an Integer. Everytime the program needs to add a row to the table, look at the max id (historyno on this case) and sum one.

  3. This affect our performance when we need to insert a lot of records at the same time, so we create a process to insert rows from a temporary table (AKT_ES_CampTool_TempHist) out of production hours.

  4. The problem is that, in one hour, it only inserts 8K rows. Considering that we need to insert more than 120K, we run out of hours.

The code we use is the following. Please, if someone has any idea to improve it, it will be appreciate.

DECLARE             @HistNo         AS INT

WHILE EXISTS (SELECT * FROM AKT_ES_CampTool_TempHist WHERE Inserted = 0)
BEGIN

    SELECT  @HistNo=MIN(HistoryNo) FROM AKT_ES_CampTool_TempHist WHERE Inserted = 0

    INSERT INTO NOVADB.dbo.niHist   (
        HistoryNo,ObjectType,ObjectNo,SubNo,ReferenceNo, 
        Time,Type,Priority,Collector,Code,
        Action,RemainingAmount,Obliterated,SubType,ActSegment,
        Data,FreetextData,quantity
                                        )
        SELECT      
        (SELECT  max(historyNo)+1 
        FROM    NOVADB..niHist),ObjectType,ObjectNo,SubNo,ReferenceNo,
        Time,Type,Priority,Collector,Code,
        Action,RemainingAmount,Obliterated,SubType,ActSegment,
        Data,FreetextData,quantity 
        FROM        AKT_ES_CampTool_TempHist 
        WHERE       HistoryNo=@HistNo

        UPDATE  AKT_ES_CampTool_TempHist
        SET     Inserted=1
        WHERE   HistoryNo=@HistNo

END

Upvotes: 1

Views: 1154

Answers (3)

Gary Walker
Gary Walker

Reputation: 9134

You should never use the max+1 strategy you are using for assigning an index. Assuming you can't use identity and the main table and you are not using the lastest version of sql server -- Create a shadow table based on a identity field and use that to generate sequence numbers

i.e.

create table AKT_ES_CampTool_Shadow
(
  id int identity(1234,1) not null -- replacing 1234 with a value based on max+1
, dummy varchar(1) null
)

Then to gen an id -- less expensive than max+1 -- no locking problems

create proc AKT_ES_CampTool_idgen(@newid output)
(
  declare @newid int
  begin tran

  insert into dbo.AKT_ES_CampTool_Shadow (dummy) values ('') 
  select @newid = scope_id()
  rollback  
)

You don't say how big AKT_ES_CampTool_TempHist is. If it is large, you may have performance issues there (esp. if there is no index on the field "inserted")

You could start by created a table var containing the relevant columns.

declare @TempHist table
(
  HistNo int
, inserted int
, etc.

primary key(...)
)

Then populate @TempHist with a single insert query. If you don't have an appropriate PK for this table, used use a generated RowID s the PK

Now, you can loop through this table without causing lock contention. Just select top 1 from @TempHist and the delete the corresponsding row from @TempHist when you are done processing it.

You won't have use a cursor nor have a large Batch operation

Upvotes: 0

James S
James S

Reputation: 3588

obviously the proper answer is to change that historyNo column to an identity, but as you can't do that why not use ROW_NUMBER over the entire set to get an incrementing number to add to the prev max historyNo?

Then you could alter the insert to just 

DECLARE  @OldMaxHistNo  AS INT
SELECT @OldMaxHistNo = MAX(historyNo) FROM NOVADB..niHist

INSERT INTO NOVADB.dbo.niHist   (
    HistoryNo,ObjectType,ObjectNo,SubNo,ReferenceNo, 
    Time,Type,Priority,Collector,Code,
    Action,RemainingAmount,Obliterated,SubType,ActSegment,
    Data,FreetextData,quantity
                                    )
    SELECT      
    @OldMaxHistNo+ ROW_NUMBER() OVER(ORDER BY ObjectNo)
    FROM    NOVADB..niHist),ObjectType,ObjectNo,SubNo,ReferenceNo,
    Time,Type,Priority,Collector,Code,
    Action,RemainingAmount,Obliterated,SubType,ActSegment,
    Data,FreetextData,quantity 
    FROM        AKT_ES_CampTool_TempHist 
    WHERE       Inserted = 0

    UPDATE  AKT_ES_CampTool_TempHist
    SET     Inserted=1

Might have to lock the tables inside a transaction whilst doing it though

Upvotes: 2

bummi
bummi

Reputation: 27377

You could select the data which should be inserted into an temporary table with a new HistoryNo generated by Rownumber() and changed with max(historyNo) FROM NOVADB..niHist.

SELECT  ROW_NUMBER() OVER (Order by ID) as NEW_HistoryNo , * 
into #tmp
FROM AKT_ES_CampTool_TempHist 
WHERE Inserted = 0
ORDER BY HistoryNo

Update #tmp set NEW_HistoryNo=NEW_HistoryNo + (SELECT  max(historyNo) FROM    NOVADB..niHist)

INSERT INTO NOVADB.dbo.niHist   (
        HistoryNo,ObjectType,ObjectNo,SubNo,ReferenceNo, 
        Time,Type,Priority,Collector,Code,
        Action,RemainingAmount,Obliterated,SubType,ActSegment,
        Data,FreetextData,quantity )                                 )
SELECT      
        NEW_HistoryNo,ObjectType,ObjectNo,SubNo,ReferenceNo,
        Time,Type,Priority,Collector,Code,
        Action,RemainingAmount,Obliterated,SubType,ActSegment,
        Data,FreetextData,quantity
from #tmp

Update AKT_ES_CampTool_TempHist  set Inserted = 1
from #tmp
Where #tmp.HistoryNo=AKT_ES_CampTool_TempHist.HistoryNo and AKT_ES_CampTool_TempHist.Inserted = 0

Drop Table #tmp

Upvotes: 1

Related Questions