We have a table that keeps track of access to a particular Application. Just few days back, the web app is reported to be slow, I did a check via (SQL Profiler) and a number of SQL commands that were slow, but one that caught my eye was one that called an SP, it Took 17sec to execute!
A check on the table shows, table has 2,000,000++ records, and also the table has no primary key or index.
So this is what we did,
1. create an identical table
2. declare primary key on this table
3. create index (for columns that are mentioned in the sp's where clause)
4. import data from old table to new table
5. rename old table to _old
6. rename new table to the table name
Performance! improvement?
for a start, just before we did the change, it was taking 1,000 - 4,500 ms to execute, when we did the change the time taken to execute drastically dropped to (0-5ms)! thats HUGE improvement over performance! Im Shocked! of how of a big difference index makes to insert/update/select.
A check on the table shows, table has 2,000,000++ records, and also the table has no primary key or index.
So this is what we did,
1. create an identical table
2. declare primary key on this table
3. create index (for columns that are mentioned in the sp's where clause)
4. import data from old table to new table
5. rename old table to _old
6. rename new table to the table name
Performance! improvement?
for a start, just before we did the change, it was taking 1,000 - 4,500 ms to execute, when we did the change the time taken to execute drastically dropped to (0-5ms)! thats HUGE improvement over performance! Im Shocked! of how of a big difference index makes to insert/update/select.
No comments:
Post a Comment