Re: Can Indexes effect Bulk Insert/Update Operations

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/31/04 

Date: Fri, 31 Dec 2004 12:23:07 -0500

Siz wrote:
> Thanks Tom,
>
> Let me clear you the current scenario.
> I am using an iterative approch to fill in the table (using a loop to
> do this
> becuase some operations need to be performed before insert/update the
> record).
> The data comprises 50K records and certainly its taking a huge time
> when updated
> that way. I need a better solution.
>
> In fact, there is a stored procedure which retrieves a record and
> inserts/updates
> the data into three disparate tables with another stored procedure,
> like this.
>
>
> oRs1.Open "spSelectRecords", oCon, ...... ' From table1
>
> while not oRs1.Eof
> ocmd.commandText = "{call spInsertUpdate(?,?,?,?,.....)}" '
> Inserts in table2 & table4, update table3.
> ocmd(0) = oRs1.Fields("FirstName")
> ocmd(1) = oRs1.Fields("Age")
> ....
> ocmd.Execute
> ors1.movenext
> wend
>
> I have left some code snippet (like variable declaration,
> initialization, etc.).
>
> Can dropping and recreating indexes help in this scenario?
>
> - Siz
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:OSxwW2z7EHA.1400@TK2MSFTNGP11.phx.gbl...
>> Depending on how much data you have, you typically drop all indexes
>> and unique/pk constraints before bulk loading the tables. Then, you
>> add them back afterwards. It's best to test with a typical amount
>> of data. That said, if the table has no data, then drop the indexes
>> first. If it has data in it, then you have to make a judgment call.
>>
>> --
>> Tom
>>
>> ---------------------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>>
>> "Siz" <dotnet_guru@hotpop.com> wrote in message
>> news:eW%23jVLz7EHA.3820@TK2MSFTNGP11.phx.gbl...
>> Hi,
>>
>> I have a suspicion about using Bulk Insert/update on indexes tables
>> whether they bogged down performance or not. Or dropping indexes
>> and/or constraints at start of bulk operation and then creating them
>> again at the end will help or not ???
>>
>> Can Indexes effect bulk insert and update operations.
>>
>> Help in this, will be highly appreciated.
>>
>> Regards,
>> Siz

I think a more set-based approach will help here. Even if you turned the
entire process into a master stored procedure that pulls rows from a
temp table, you're likely to get much better results because all the
code is running on the server. All those round trips to the server are
surely slowing things down. Instead of cursors, you can try adding a
"processed" flag to the temp table (with an index on the flag and the PK
columns) and pull a single row at a time for processing using SELECT TOP
1 syntax. Even better would be to insert using a complete set-based
solution from the temp table. Functions, although they add some overhead
to this type of process, can help you transform your data using a
set-based solution.

As Tom mentioned, post your code and we'll see what help we can provide. 

-- 
David Gugick
Imceda Software
www.imceda.com