DB size to large for Querying

[cricketer1]

I have a database with couple of dozen people querying through either web interface or client server apps. This is an old DB and has grown to a size that is now slowing down performance and results take longer to return.

However, 90% of the time only recent data is queried, most of the old records are seldom required. Is there a way I can segrgate the database into two parts, maintaing the same table and fields etc. so querying on the recent data returns faster and the same queries/transactions work with old records i.e no modifications is required in the queries embeded in apps. Indices already exists and more indexing could further slow down the transactions (updates/deletes)

Thanks
cricketer1

 

[SQLSister]

Can't see a way to do it with no changes to the queries embedded in the app or at least the connection strings because the only way you can do this is to move the old data to a separate table or separate databse.

However, your statement about the queries being embedded in teh app is telling me a good apart of your problem with speed isn't the number of records but the queries themselves. Embedding the queries in an app is not the most efficient way to access data. You should use stored procedures instead. Since your application has not done this, it was probably programmmed by programmers unfamiliar with ways to improve efficiency of queries and thus your queries are probably less effectively constructed than they could be. I'm not sugggesting you change this now as it is probably too late without a great deal of rework. But it is something to consider on all new development for this databse or any new ones you do. Do not put the code to access data in the app.

What I would do in your case is move the old data to a separate database. And then build a new user user interface to access that data. It could be a modified copy of the current user interface with the connection strrings just changed to the new database name. Then the same queries should work assuming you set up the archive database exactly as the current database is set up. You'll also need to build a DTS process to periodically move the old records out. You might have to build some new functionality if the users have to access old and new data simlutaneously.

 

[cricketer1]

Your suggesting to build a new user interface and maintain two seperate database which I think is good for long term. But I was thinking more in the lines of having multiple files for the same databse and keeping the old records in a seperate file and more frequently accesed data in a second file. Is that possible to do.