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 |
|
sconard
Starting Member
18 Posts |
 Posted - 2008-03-31 : 09:20:57
|
| Monthly, I copy a table from one database to another database. I delete the original table and copy the table back speed the performance of the query on the order of 10 to 1. Why does this work? Detail:I have a legacy table that a small application queries about once a month. The table was poorly designed and the query runs a date range comparison on one field and has a sub query that runs string comparison against six fields. I cannot change the calling app or table design. When the app calls the query, the call times out due to the inordinate length of time. To fix this until next months query, I copy the table out, delete the original and copy back. What changes when the table is copied to another database and then copied back? The performance of the query changes from 10sec to 1. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-31 : 09:24:49
|
| How frequently table statistics is updated? Are there any indexes on table? How frequently those are rebuilt?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sconard
Starting Member
18 Posts |
Posted - 2008-03-31 : 10:02:03
|
| No indexes. Cannot seem to check statistics as DBCC SHOW_STATISTICS (printReq, UPKCL_auidind) returns a construct or statement is not supported on ms sql server 2005 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-31 : 22:35:08
|
| Copying table defrags it, that's why get faster. You may consider put some indexes on the table so that don't have to copy table every month. |
|
|
|
|
|
|
Copy table speeds performance