| Author |
Topic |
|
ann
Posting Yak Master
220 Posts |
 Posted - 2008-02-21 : 14:20:35
|
| I have a large db (sql 2005 express) that is, well, basically a mess. It is not normalized properly and contains masses amount of data (due to so much repeating data). To make a long story short - this db needs to be redesigned, but management said no, so that is not an option - so please, no one suggest that that's what I do.My application creates reports based on this db - the problem is, the sp's are slow - and when a report needs to run several reports, it takes a long time to run. The sp's and db have been optimized as best I can (adding indexes etc.,)I was wondering if there is a way to split the db - what I want to do is just retain, say 2 years of data in 1 db, and store the rest of the data in the other db, as 2 years worth of data is 95% of what will be queried. I did copy over 2 years worth for testing, and reports that took 30 minutes in the existing db, take less than 1 minute (sometimes even faster) in the new db - a huge improvement.My problem is how to deal with the times that I need more than 2 years worth of data - how do I query both db's to get my application to read the data from both db's so that it seems that I am only running one db? The new db would be updated daily with new data, but not the old db - so if I had to query 10 years worth of data, I need 2 years from the first db, and then the 8 yeas from the second db.If anyone can provide some feedback or point me in the right direction of what I should research in order to accomplish the above - I would appreciate it.If anyone knows of a better solution - please don't be shy - speak up! :)Thanks - ann |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 15:50:50
|
| Max db size in express is 4gb, it's not big. How often do you rebuild indexes or update statistics? |
 |
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2008-02-21 : 15:53:28
|
| Well, if you run reports that only need the most recent data off of that database only, and moved the rest of the data to another database; could you create a new database that is just a set of views that does UNION selects between both databases to use when you have to report on recent and historical data?Scooter McFly |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-21 : 15:56:49
|
| Why not just put the historical information in history tables rather than splitting the database? Then you could have the application access a view that would union the tables together (historical and current) and therefore be transparent to the application. Here's an example using dumb names:Table1_Current (Column1, Column2)Table1_History (Column1, Column2)Table1 (view: SELECT Column1, Column2 FROM Table1_Current UNION ALL SELECT Column1, Column2 FROM Table1_History)So the application would be referencing Table1 already. Since we modify the current table name to Table1_Current and then use Table1 as the view name, we don't have to make any application changes. You should look into partitioned tables as well since that handles the historical/current case as well. It's new to SQL Server 2005. In the past, we only had partitioned views available.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2008-02-21 : 16:00:51
|
| This may sound stupid - but what exactly is a view and what purpose does it serve? I"ve never used views before ..... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-21 : 16:10:54
|
| They act just like tables to an application but they contain no data and are stored queries instead. Here is an example:CREATE VIEW View1 AS SELECT Column1 FROM Table1You can then run SELECT * FROM Views and see the results of "SELECT Column1 FROM Table1".Check out SQL Server Books Online for more details on views.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2008-02-21 : 16:13:38
|
| Are they faster than running regular sp's? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 16:16:37
|
| Same if they have same code. |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2008-02-21 : 16:18:08
|
| If they are the same in terms of performance as a sp - what is the advantage of a view? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-21 : 16:22:30
|
| You can create index on view, can treat it like table. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-02-21 : 16:24:19
|
quote:
Originally posted by ann
If they are the same in terms of performance as a sp - what is the advantage of a view?
You can store a query and treat it as a table. This allows for faster programming. Another advantage to views is security. You can give access to a subset of data in a table by adding a WHERE clause to the view. In your case though, the advantage here is that my approach to your problem doesn't require any application changes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
splitting a database