| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-01-23 : 04:58:28
|
| hi back again with performance issue...index with improve the performance and too many will not help...now how many are too many.... comming to my problemi have a huge base around 5 million record (10 gb)i query data for billing and for reports with diffrent condition and joins currently i create the indexs requied for the query. remove the existing index. so for every query i delete the old index and create the new one... but latter i need to do all this in stored procedure.can i create index in stored procedure before the query/ job executesor do we have any other option .... thanks for help in advancekhalikAsk to your self before u ask someone |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-23 : 05:47:55
|
| Indexes are meant to be static objects. Creating and dropping them sounds like a good way of corrupting a database. I hope you are taking backups before doing this.Note that reporting databases (e.g. billing) are usuallt set up diffently from oltp databases so you might consider taking a copy of the database onto another server for the billing.When I do something like this I usually install a transfer mechanism to get the data onto the reporting server in the format for the report.To anwaer the question - yes you can drop and create the indexes in a stored proc. Any SPs run afer that will need to be recompiled - this should happen automatically but it would be safer for the SP that does the index change to use sp_recompile or set the SPs to recompile on every run.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-23 : 20:53:41
|
| thanks for the info ... i was not aware of this... so how to solve my problem... if i am not wrong what u suggest is i should have a billing server and reporting server so that i can have index created once.... am i right... then how many index can i create which will not effect the performance....Ask to your self before u ask someone |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-23 : 22:15:14
|
| Khalik,"Performance" is relative.Generally,Indexes speed up SELECT and slow down INSERTAs nr has said, a "reporting" (No Inserts etc..) DB should be heavily indexed.By dropping and creating the indexes you are absolutely killing your performance......STOP DOING IT NOW!!!!!DavidMTomorrow is the same day as Today was the day before. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-23 : 22:47:07
|
| thanks a lot .. reporting has no insert... so i can go head and create several (not many.. only requied ones) so no more droping and creating.. thanks a lot guys .... so for no problem with it, but i will follow ur advice...thanks a lot..byeAsk to your self before u ask someone |
|
|
|
fisherman_jake
Slave to the Almighty Yak
159 Posts |
Posted - 2002-01-23 : 23:35:46
|
Is it true that after a heavy/massive INSERT/DELETE, the indecies associated with the table should be dropped and re-built, this is so the indecies will be balanced? Since I do huge data loads I pretty much destroy and rebuild my indecies on the STAGING tables. This helps me in 2 ways. 1) I get a performance boost (no indecies to hamper BULK INSERT or INSERT), 2) I'll have a healthier index from rebuilding it from the fresh table.   ==================================================World War III is imminent, you know what that means... No Bag limits!!!Master Fisherman |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-24 : 00:33:45
|
| which one is better...create table ,load the data and create index orcreate tabel create index and load data...do we have any rebuild option for index....======================================Ask to your self before u ask someone |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-24 : 00:49:13
|
| khalik,If you think about what is happening at the IO level.....the only conclusion you can draw is that indexes will slow down inserts...Fisherman Jake is spot on....When loading large dataset, there should be no indexes if you want maximum speed..Then you apply the indexes.........DavidMTomorrow is the same day as Today was the day before. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-01-24 : 01:43:25
|
| thanks davidm======================================Ask to your self before u ask someone |
|
|
|
|
All about Indexs