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 |
|
noamg
Posting Yak Master
215 Posts |
 Posted - 2002-10-21 : 14:10:28
|
| hi,I have to insert many rows into a table (bulk insert), very high speed. this table will not be indexed.But other process has to query this table, so it needs the indexes.what to do ?Noam Graizer |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-21 : 14:36:33
|
| Easy, drop your indexes before inserting the data, and then readd the index once you are done. However, while the bulk insertion of data is happening, ideally no one should be querying the data. You should pick the downtime when activity is low.*************************Someone done told you wrong! |
 |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-10-21 : 20:26:13
|
Actually, there's a formula to decide when to drop indexes and when to keep them when doing bulk inserts. It revolves around how much data is being added in comparison to the amount of data already present.It's in the Optimizing Bulk Copy Performance section of the BOL.- Clustered index only 30%
- Clustered and one nonclustered index 25%
- Clustered and two nonclustered indexes 25%
- Single nonclustered index only 100%
- Two nonclustered indexes 60%
Also, if the bulk data is already sorted on the clustered index columns, then it is sometimes slower to drop the index and rebuilding it opposed to inserting it (and using the ORDER hint to let bcp know that the data is ordered.)Helping with the simultaneous bulk insert/querying is a little beyond my ken though. It depends on how much data is being loaded compared to how much is there already. My suggestion, find a time that the system is down to do it. (like in the evening or whatever.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2002-10-22 : 03:37:18
|
| the insert and the select queries are running always together.i can not drop and recreate indexes.Noam Graizer |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-10-22 : 09:05:16
|
| My suggestion is to then batch insert the records. Maybe in batches of 10000.*************************Someone done told you wrong! |
|
|
|
|
|
|
bulk insert and select stimulately