what are causes for slow insert operations in table ?

Question

what are causes for slow insert operations in table ?

AKP

SSCommitted

Points: 1753
More actions
January 7, 2007 at 4:08 pm

#175445

I got very slow insert operations in table which consists of millions of data already.
what factors can affect an insert operations ? please give me some idea how can make fast insertion in table.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Bradley SSC Enthusiast Points: 193 More actions · Answer 1

I am no expert but if you are trying to insert alot records and there is triggers set to create a newid for record then it will be very slow. You can disable the trigger before doing the insert and it will be much faster.

Grant

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Too many disks IO on the server

Too many indexes to reorder

Not enough memory

Temdb too small or on a slow disk (or the same disk as the insert and transaction log).

That's just to name a few. Can you give us more details on the server and the insert in question?

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

Usually, the culprit is a CLUSTERED PRIMARY KEY... change it to non-clustered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Is this a plain insert that you are talking about, or does it contain lots of joins and/or a where clause? It could also be that it isn't the insert itself that is slow, but the other operations (selecting the data to be inserted). Try to run the select portion separately, without inserting, to find out how quick it works.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 5

Of course, we could stop guessing if you'd post the code, Ashok.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)