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 |
|
pelegk2
Aged Yak Warrior
723 Posts |
 Posted - 2006-05-10 : 11:13:46
|
| i have added about 2.5 million or more new rows at once to a tableand when i make search on it it feels that the search time has drameticlly slowencan i update its index or something else to imporve the search time?htnaks in advancepelegIsrael -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-10 : 11:43:08
|
| Have a look at update statistics.You might need to rebuild the index too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-10 : 14:21:18
|
| SProcs [and maybe tables] may need recompiling too (or Stop&Start SQL Service)Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-10 : 17:00:36
|
| During any kind of mass load like that it would be best for performance on the load and after to drop all of the indexes on the table, add the data, then recreate the indexes. I just read yesterday that in SQL Server 2005 you can just disable the indexes and then reenable them but if you are using 2000 or before just drop the indexes, add the data, then recreate the indexes. The reason is that each insert is slower with indexes in place, and when adding that many rows that time is multiplied obviously. Plus the indexes are getting fragmented out the wazoo as you are doing it making them useless and causing them to need to be rebuilt anyway.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-10 : 17:03:08
|
| Peleg - I plan on living in heaven eternally and won't need any place "after" heaven. But as Israel is God's chosen land I'm sure it must be a nice place to live in and/or visit "prior" to going to heaven. (I know exactly what you mean I'm just picking on the actual words.)Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2006-05-11 : 01:20:13
|
quote:
Originally posted by druer
just drop the indexes, add the data, then recreate the indexes.
how do i do that execlly?do u mean by indexe's to auto incrementae column of the table or the indexes that help for faster search?thanks i nadavncepelegp.s. : come to israel i will invite u to a cop of beer on me:)Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-11 : 07:22:14
|
| I mean the indexes that help with faster search. You can use DROP INDEX (see books on line.)If you have say 5 indexes on the table that you are doing the massive load for. Each time you insert 1 row all 5 indexes have to be updated to reflect the new data. Each time you insert 1 row to an index it could force the page that the value is in to become full and a new page needs to be written which can contain that value. This goes on all day, every day with every insert/update/delete that happens. It isn't a huge cost but all of that activity does cost and slow down the insert/update/delete activity to maintain those indexes. Additionally as new pages are being created and values have to be moved around etc, those indexes are then becoming fragmented. Meaning when you do a search that uses one of those indexes, it may end up having to progress through many extra pages of data that only have 1 or 2 values in order to find the value(s) the query is looking for. This is why you need to maintain all of the indexes in the system on a very regular basis using either the index defrag or index rebuild. If you do a month or two's volume of inserts in a batch mode as you are doing that just means that you have rapdily fragmented your indexes. In that case you certainly need to rebuild the index as quickly as you can or as you've seen your performance on those tables will fall apart. If you plan to rebuild the indexes anyway (which you must do to get back on track with performance) then you might as well drop the indexes before doing the batch insert so that the inserts can occur without any performance hit to maintain the indexes.Hope that makes sense,DaltonPS: Thanks for the offer that is very kind of you.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
|
|
|
|
updating table performance