Re: Can Indexes effect Bulk Insert/Update Operations

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/31/04 

Date: Fri, 31 Dec 2004 09:47:16 -0500

I'm thinking that maybe it's your iterative approach that's making things so
slow. We would rally have to look at your table design, specs, etc. to come
up with a set-based solution. 

-- 
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:%23BnOiL07EHA.2192@TK2MSFTNGP14.phx.gbl...
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
>
>
Loading