SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Views on Attaching/Detaching mdf files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sengchai
Starting Member

Singapore
3 Posts

Posted - 04/09/2008 :  22:57:04  Show Profile  Reply with Quote
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 data

How does this 2 methods measure up in terms of resource usage, performance, etc??

Need comments and views. Thanks.

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/09/2008 :  23:23:15  Show Profile  Reply with Quote
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.
Go to Top of Page

sengchai
Starting Member

Singapore
3 Posts

Posted - 04/10/2008 :  03:32:54  Show Profile  Reply with Quote
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?
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 04/10/2008 :  23:29:51  Show Profile  Reply with Quote
In that case, yes. Query smaller table usually faster if have same schema and index.
Go to Top of Page

sengchai
Starting Member

Singapore
3 Posts

Posted - 04/10/2008 :  23:55:38  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 04/11/2008 :  00:01:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Just run a test to show them this. It would be fairly easy to prove it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 04/29/2008 :  17:38:30  Show Profile  Visit m_k_s@hotmail.com's Homepage  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/29/2008 :  17:44:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 04/29/2008 17:47:16
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37471 Posts

Posted - 04/29/2008 :  17:48:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/29/2008 :  18:01:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 parts

just 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 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 04/29/2008 18:02:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000