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 |
|
Vishakha
Starting Member
40 Posts |
 Posted - 2006-12-12 : 23:38:37
|
| My application imports lots of data from an external interface.Does some validation and inserts it in the database (sql server 2K).There are already around 1 million records and it is growingWe also need to run lots of transactions on the same database. In order to speed up selects we want to do the following1. create temporary indices2. Do transaction validations,(mostly selects)3. drop the indices4. insert or update.I am aware of the fact that there is a cost associated with creating and dropping indices.My question are1. Are there any flaws with this approach.2. Is there any better way to achieve this. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-12 : 23:48:56
|
| Why create temporary indexes?We have strategy like:1. Import data from external system into one or more staging tables using scheduled jobs (these tables have minimal indexes)2. Do processing on Staging tables3. Transfer processed data from staging to final table. (This table has all the necessary indexes)Creating and dropping indexes every time create more overhead than having them in place and importing data.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Vishakha
Starting Member
40 Posts |
Posted - 2006-12-13 : 00:04:20
|
| Thanks for the reply, we are also thinking on the similar linesbut it will take some time to do this, meanwhile we have to solve this problem thus asking the question.So in your opinion we might not gain anything by programatically creating and dropping the indices. |
|
|
|
|
|
|
indexing question