Increase in DB size leads to slower response times of the application.
-
I need to suggest ways to increase the speed of an application, which is designed to use SQL SERVER 2000 or Oracle 8i (depending on the client licensing requirements) as the back end. The front end is ASP.
No stored procedures.
My understanding of the system is at a very initial stage, though I have extensively worked on other systems, and I am very much comfortable with SQL Server (well so I’d like to think). I do have a good understanding of the application for which I am to propose the solution.
Every client of ours uses the system according to his needs; hence the population of data in the system is not uniform, as some prefer using module “A” while some use modules “ABC” extensively. Also I cannot archive the data straight off because I face the same problem which a number of DBA’s face, where the user needs the entire data to be online so that he can execute reports for data which is present since the beginning of the system. If I split a table and keep, say for example data which was used for last year offline, the user will get an empty screen if he requests for last years data.
"There is currently no archiving system in place."
Here’s how I plan to go about the system
1. Understanding the section of the db, which get over populated.
2. Checking out the queries in the reports, if they require any optimisation.
3. Checking the indexes and maintenance plans. Setting up a trace and feeding it to the Index tuning wizard.
4. I am planning to do some benchmarking to get the existing response time and desired response times, from the front end, as that’s what finally matters. It may totally be possible that the current stare of the db is highly optimized and the only way to improve the response times is to increase the Hardware or archive the data, which is not used.
The issue for me is that the client might want to run a report which could contain the archieved data, and if I archieve it then it obviously will not be available.
Has anyone come across a similar situation before, and were you able to find a solution for it.
Oh and btw I have gone thru all the amazing links for archiving on this website and I’d like to thank the contributors, it got me well and truly started.
-
My personal preference follows the 80:20 rule. 20% of the data caters for 80% of the needs. Therefore I prefer to have a small amount of live data to give maximum performance for the majority and then have historical tables that keeps old data on-line but has to be specifically requested.
If you know that your data is going to grow by 'x' over the next year then rather than expand your physical database a little at a time, particularly if you let it do it for itself, I expand it by one great big chunk on a defragged disk.
Yes, this increases the backup time but my window is large enough not to worry about this. I would be worried about trying to fit in a database expansion in an ad-hoc manner.
-
Thanks David.
That was an interesting point you made about increasing the physical db by one big chunk.
The point will hold true in rapidly increasing databases especially the ones which have small increments. I can understand why it would be a better solution to configure the database for bulk increases.
The only part, which bothers me, is "and then have historical tables that keeps old data on-line but has to be specifically requested". The clients are not always cooperative. Also what could be the distinguishing parameter for slicing the data from the main database into the historical db? Could it be a time field as is the usual practice?
Also there will have to be changed made in the application. If one of the queries refers to a row, which has been moved into the historical database, the result could be wrong. How does one go about documenting this information?
-
OK, I worked on a database that had several million transactions per country per week appended to a database. I established a rule early on that said only one calendar month's data would be available within the main tables and up to 12 calendar months data available on-line in total.
Data beyone 12-months was available by specific request but only as an aggregate. In reality, if the need was THAT great we could get the transactions back from any period but we did not advertise that fact.
I could be cynical and say that as most statistics were concocted to proove a point rather than to find new information old data would not be that important in any case.
-
I tend to agree with David. We are currently working on an archiving strategy that will provide same reports as "live" system for last fiscal year. This way at max there would be 15 - 16 months of data versus years and years...
We have an archive process in place (have never executed yet) the system has been "live" for 1 year. We are starting the reporting phase now. Our front-end is ASP and crystal reports. We are planning on having views in the live system point at the historical database(s) and retrieve from them. Depending on what date range is selected will point at either historical OR live. We will not provide the ability to comingle the data at this time.
Another option is to have a VERY robust reporting server where ALL reports point to and have new data replicated to it and then the production system is happy and the users (IF they won't budge on archiving) will be happy and will learn to live with retrieval times > 30 minutes for a report that will be used 1x time (if that)
<stepping off soapbox>
Good Hunting!AJ Ahrens
webmaster@kritter.net -
There are very few moments in a DBA's life when he thinks of a solution and someone agrees with it. AJ you almost read my mind.
I have been fiddling with the view thinge for some time but I wasn’t too sure to go ahead with it. I was planning to move the old data from the main table to a historical table, and have a view to do a union "all” on these two tables. After reading your post, I think I can very well go ahead with it and try it.
If I do the slicing of the data, which would be tagged as historical data properly, I think this solution could work very well.
I'll be posting my progress on here.
Thanks guys, that was a lot of help.
-
Lots of good discussion here. However the bottom line is that the solution must meet the 'business' needs of the user community. There may or may not be room for compromises based on business rules and such. As DBA's it's our job to provide the alternatives, like historical and/or reporting databases/tables. As for the final decision, well, it's just our mandate to maintain and support.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
From the pack of suggestions, the one which was chosen was this one.
Listed here under are some of the suggested approaches..... blah blah blah......
The “inactive” data from the tables would have to be moved to a historical table. A view would be created which would be a union between the online and the historical table and the query from the report will refer to this view as if it were the online table.
The only issue would be to make sure that the historical data has been moved cleanly, i.e. if there are any rows which the online data needs to refer to, then there needs to be a strategy for making sure that the data is there when it is refered to.
As usual the BIG ONE is making sure that a clean "slice" of the data is moved. If there are any references to the data in the online table (foreign keys etc), its going to be a trick thing to explain the users as to why some queries are returning data and some arent.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply