multiple databases for multiple days

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"?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: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Johnny Ruin" <schafer.dave@.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
"Johnny Ruin" <schafer.dave@.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.
>|||Thanks for your comments, fellas. I'll reconsider my aproach.|||Johnny Ruin wrote:
> 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.
24 GB is a small database. At that size, good indexing will certainly
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
> just go with one table period?
I'd certainly go with one table unless there was proven evidence of
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
"Johnny Ruin" <schafer.dave@.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?
>