| Author |
Topic |
|
sengchai
Starting Member
3 Posts |
 Posted - 2008-04-09 : 22:57:04
|
I am currently having a problem in the implementation of a Database for my C#.net winform application. Look at the 2 diagrams below: Diagram 1 : A typical databse design where it usees only 1 mdf file. Diagram 2: A new proposed approach to implement the Database with multiple mdf files of the same schema!!In the new proposal of the DB, each mdf files is kept separately and an additional module is required to attach and detach these databases as and when required. The purpose: the performance of all actions on the small data is not affected by the size of the large dataHow does this 2 methods measure up in terms of resource usage, performance, etc?? Need comments and views. Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-09 : 23:23:15
|
| If those files are in same file group, sql needs all of those to work. And don't see why attaching 4 files are faster than attaching one. |
 |
|
|
sengchai
Starting Member
3 Posts |
Posted - 2008-04-10 : 03:32:54
|
| Oh the files are non-dependant as they represent different test results. But if they ever do have to be queried, they can be dynamically attached to form a new database.This would lessen the burden of the database and make the selection faster? Is this thought correct? If not, how and why not? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-10 : 23:29:51
|
| In that case, yes. Query smaller table usually faster if have same schema and index. |
|
|
|
sengchai
Starting Member
3 Posts |
Posted - 2008-04-10 : 23:55:38
|
quote:
Originally posted by rmiao
In that case, yes. Query smaller table usually faster if have same schema and index.
Thanks. Any idea where I can find anything evidence relevant to that statement?Reason is I need to justify this for my boss for an implementation of my current project. I need to find out which way is better. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-11 : 00:01:27
|
| Just run a test to show them this. It would be fairly easy to prove it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
m_k_s@hotmail.com
Insecure what ??
38 Posts |
Posted - 2008-04-29 : 17:38:30
|
| I'm talking out my ass here (I'm an app developer), but I think you're way off... and being lead down the wrong path...The people that write the DBMSs are incredibly smart. They've already figured this stuff out. In DB2, there is a thing called a tablespace and you can prefix your queries for tables, etc. with the specific section you put the data. The design you have below looks just like the tablespace design that the super nerds already figured out. I'm not sure what the SQL Server version of table space is called, but I'm sure that those guys also have a version of this concept where you can specify what file to look at for a query.Your concept sounds interesting, but I think there would be significant overhead in switching things out that would make you lose any performance gains you have.I'd have a good dba audit your table structures and indices and performance tune your queries. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-29 : 17:44:51
|
| maybe you should look into partitioning and putting each partition on it's own disk drive.EDIT:oh it's sql server 2000... forget partitioning.look into filegroups._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-29 : 17:48:47
|
| Partitioned views exist in SQL Server 2000. Mladen, why shouldn't he use partitioning in 2000?Partitioned tables are new to SQL Server 2005, but people usually want partitioned views anyway.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-29 : 18:01:08
|
i meant partitioned tables and indexes.as i understood his problem he wants to modularly spread the db functionality around.a few tables here and a few tables there.partitioned tables and indexes can achive a part of that. i don't think partitioned views in 2000 can phisicaly split stuff.so i thought he'd partition the tables, put each paritions on it's own filegroup and that would be it.that would work if he wanted to split one table into multiple partsjust filegroups would probably do exactly what he needs, though. put some tables on first disk, some on the second and some on the third.or maybe i'm completly missing his problem...  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
Views on Attaching/Detaching mdf files