Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slimt_slimt
Aged Yak Warrior
746 Posts |
 Posted - 2009-05-28 : 05:08:04
|
| Hi,i have a table with cca 20mio rows and on daily basis, table is growing by additional 100.000 rows. table has some 25 columns (different types, no identity, 6 nonclustered indexes), but the problem is that daily data insertion is taking up to 5 minutes, which is too long.what should be best practise in order to shorten this time?how to diagnose this problem? how to solve this? what is slowing down the insertion?thank you for help and all directions where and what to search to find best solution.thank you |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-28 : 05:51:31
|
| your table is a heap? -- No clustered index? -- no primary key?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2009-05-28 : 11:52:34
|
| yes, it is a heap table, since i'm doing a lot of insert|update|delete against the table and therefore no clustered index. I only have nonclustered indexes against the table for faster retrieval and i have composite primary key (two fields int + varchar(10)).i've rebuild all indexes due to fragmentation, but still insert is very slow.any ideas?thank you |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-28 : 12:01:00
|
| the presence of nonclustered indexes will also slow down the insertion process.how frequently will insertion be happening? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-01 : 04:43:04
|
| I too thought that heaps would be good for insert performance but after discussions here it seems that the best performing table for inserts uses a strictly increasing clustered index. (like an identity column or similar). Apparently in a heap the db engine still tries to look for a free page instead of just appending to the end of some list -- however with a clustered index it knows that the record needs to go right at the bottom of the tree. (as long as you can guarantee that new records have strictly higher values for CI than older ones).As Visakh16 said also - -the non clustered indices still impose an overhead for writes.Are you sure that the sql doing the inserts is optimised?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
insert data into table