Move data between two databases, where do I start?

[mtb1996]

Hello All. I am a relative SQL novice. I have a 3rd party application that logs alarm information to a SQL database that has 1 ugly flat not-normalized table. Unfortunately I have no control over this table and it's design, the 3rd party app creates the table and logs data to it.

This table has grown to over 2 million rows and performance for queries on the data has gotten very bad (+60 secs or more).

I'm looking for suggestions on what I can do to remedy this situation. Since they are primarily interested in recent data within the last 12 months, my initial thought was to create a second database or table to move the old data to. If that seems like a good idea, then I could use some advice on what to use to do it, DTS, stored procedures, etc.

This is SQL server 2000. If there's any other information that I need to post or if I should post this in another forum, please let me know. Thanks.

 

[SQLBill]

Very basic idea:

1. set up a second database with the same structure as the 'production' one, give it a similar name. For example, if the database is MyDB, you could use MyDB_archive.
2. you need to determine two things. a - how much data do you need to keep in the production db and b - how long is the data to be kept in the archive one.
3. create a script that finds the appropriate data in the production database tables (WHERE datecolumn between ? AND ?) and insert those values into the archive tables.
4. delete the rows archived from the production tables.

This can be done via stored procedures and run in jobs, or one DTS package created and run by a job.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mtb1996]

Thanks SQLBill. I've only ever written SQL dealing with one database at a time. So if I create a stored procedure on one database, how do I select/insert/delete in another database? Will I get the same potential performance increase if I just move data between 2 tables in the same database?

The end user tells me that he needs the last year of data accessible. I just ran a COUNT query for that last year and it still gives me 421,657 records.

The other thought that I had was even though I have no control over the design of the table where the data comes into SQL server, I do have full control over the client application that reads it. Therefore, maybe I could create a better design of tables and use a trigger on the ugly table to move the data as it comes in. Then my client app could run it's queries against that data.

 

[SQLSister]

421,657 records is nothing in a table. I have databases with millions of records in the various tables and they still query quickly.

To run queries between servers you need to have linked servers set up. Then use the four part name (servername.databasename.owner.table). If you are accessing another db on the same server use a three part name. No need for linked servers in this case.

Another consideration would be indexing the table. It is possible it is so slow because the indexes are wrong or don't exist or because the indexes need reindexing and the statistics need to be updated. If you aren't familiar with these things, read about them first in Books Online, then come back if you have questions. These are basic issues that anyone who uses Sql Server needs to know about.

"NOTHING is more important in a database than integrity." ESquared

 

[mtb1996]

Thank you SQLSister... I do that reading on indexes.