|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
multiple databases for multiple daysI'd like get some opinions on this matter before I go Godzilla in the
next meeting. My application fills a database during the day, while a webservice reads from the data. At the end of the day I update statistics, defrag indexes, do a shrink file and make it read only. Then I create a new database at midnight and start filling that one. The databases don't get too large, around 50-100 megs per day. The webservice makes queries on the older data as well. I choose the multiple database technique because customers could easily control their data and because I thought it'd be faster than one large database. Well, now a sub contractor is cryin' because they need to make multi-day queries for their reports. They say it'd be too difficult to do that, and I should just have one db for all of the data. This is where my newbieness shows. I know there aren't too many specifics here, but does this request seem absurd? Is it a such a big deal to do "use db1;select * from table;use db2;select * from table"? Hi
Why separate the data into days? One big DB is a lot easier to manage, backup and report on than lots of small ones. This is not the old days of ISAM DB's. One big DB is the way to go. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Johnny Ruin" <schafer.d***@gmail.com> wrote in message news:1133479375.134870.146930@g14g2000cwa.googlegroups.com... > I'd like get some opinions on this matter before I go Godzilla in the > next meeting. > > My application fills a database during the day, while a webservice > reads from the data. At the end of the day I update statistics, > defrag indexes, do a shrink file and make it read only. Then I create > a new database at midnight and start filling that one. The databases > don't get too large, around 50-100 megs per day. The webservice makes > queries on the older data as well. I choose the multiple database > technique because customers could easily control their data and because > I thought it'd be faster than one large database. > > Well, now a sub contractor is cryin' because they need to make > multi-day queries for their reports. They say it'd be too difficult > to do that, and I should just have one db for all of the data. This > is where my newbieness shows. I know there aren't too many specifics > here, but does this request seem absurd? Is it a such a big deal to > do "use db1;select * from table;use db2;select * from table"? > Well, I thought the seperate dbs would be faster. What happens in a
year, when the db is 24 gigs? It seems strange to think that it wouldn't be significantly slower than a 50 meg db. Presumably at some point you will be removing the old data -otherwise this
thing will just keep on growing? If not then in a few years you will have *hundreds* of seperate databases, then some requirement will come along that means you need to change the db design, (it will happen) and you will have a real problem keeping them all in step. Appropriate indexing can mean that all in one db can perform well. I suspect the reason they are crying is how is the report meant to know which dbs to hit - they will have to dynamically build the sql to use each db in turn, and that list is growing. What happens if the report starts at 11:55 and finishes at 00:05 - a new db has popped into existence in the middle. 24Gig is not an issue, provided you index correctly, If you ppst the DDL for the tables together with some typical queries someone is sure to suggest various options. Also I'd look at the index tuning wizard on frequent queries. Raw size will only really affect things if you are scanning large tables. Mike John Show quote "Johnny Ruin" <schafer.d***@gmail.com> wrote in message news:1133493073.595944.279090@o13g2000cwo.googlegroups.com... > Well, I thought the seperate dbs would be faster. What happens in a > year, when the db is 24 gigs? It seems strange to think that it > wouldn't be significantly slower than a 50 meg db. > Johnny Ruin wrote:
> Well, I thought the seperate dbs would be faster. What happens in a 24 GB is a small database. At that size, good indexing will certainly> year, when the db is 24 gigs? It seems strange to think that it > wouldn't be significantly slower than a 50 meg db. have much more impact on performance than partitioning the data will. -- David Portas SQL Server MVP -- So, would you guys see any benefit to doing tableMMddYYYY, or would you
just go with one table period? Johnny Ruin wrote:
> So, would you guys see any benefit to doing tableMMddYYYY, or would you I'd certainly go with one table unless there was proven evidence of> just go with one table period? some benefit from partitioning - that probably means for larger data sets than you are talking about here. I definitely would never go for partitioning of 50-100MB per table per day. That would just be totally insane, and probably even damaging to performance - depending on how the data is used. -- David Portas SQL Server MVP -- I would DEFINITELY not use table names with a naming convention of MMddYYYY
as it is ambiguous. Horizontal partitioning on a daily basis sounds excessive and will (I think) give you the same reporting problems you face with a seperate db per day. I would start with the intention of a single table, then IF it causes problems artificially split it based on a date range, define appropriate constraints on each newly created table, and a union view across them so the app still thinks it has one table. That way you are logically maintaining a single view of the data and hiding your physical tweaks that are only there for performance reasons. Mike John Show quote "Johnny Ruin" <schafer.d***@gmail.com> wrote in message news:1133535121.678475.286050@o13g2000cwo.googlegroups.com... > So, would you guys see any benefit to doing tableMMddYYYY, or would you > just go with one table period? >  |
|||||||||||||||||||||||
Other interesting topics