Datasets vs Tables

[Guest]

Hi

Which is likely to be more efficient and/or best practice
1 Dataset with 10 table
o
10 Datasets with 1 tabl

(The tables are not related

Thank
Dav

 

[Eric Marvets]

It would be a smaller memory footprint with 1 dataset with 10 tables. As
far as what would be more efficient, it really depends. I think it would be
negligable either way.

Whats wrong with just having 10 independant tables if they are not related?

--
Eric Marvets
Principal Consultant

the bang project

<shameless self promotion>

Email (e-mail address removed) for Information on Our Architecture and
Mentoring Services

</shameless self promotion>

 

[William Ryan eMVP]

For many reasons, 1 dataset with multiple tables is going to be the 'better'
approach.

1) It will be a lot harder to remember the names of all datasets
2) Storing the tables in one dataset mirrors your real world database (or
at least mirrors it more closely) in almost every situation out there, so
logically the placement makes more sense.
3) You can iteratively access each table in a dataset much easier than
accessing one table in 10 datasetz (this goes with #1).
foreach(DataTable dt in myDataSet.Tables{
//Do something
}
or, if you had an array of dataadapters that corresponded to the positions
in a dataset, youi could use a for loop and call update on each dataAdapter
using the interator as an index
for(int x = 0; x< myDataSet.Tables.Count; x++){
((SqlDataAdapter)AdapterArray[x]).Update(myDataSet.Tables[x]);

}

If you later decide tht the tables would benefit by relations or something
like that, it'll be logically easier to reference and use.

Since DataSet are colllections of datatables, then it follows that having a
collection of objects is more logically consistent than having collections
of one object.

Sure there are probably instances where this doesn't all hold or that one of
the reasons may not be applicable, but in the majority of cases, one dataset
is all you need. It will be easier for trading partner and other
programmers to undrestand a more mainstream design etc.

HTH,

Bill



www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

 

[Cor Ligthert]

Hi Bill,

Do you mind if I have a small note by this.

In my opinion I would not choose for 10 in one when you want to serialize a
part of it, because by instance that you want to use it on the Interernet,
when you have to place it in a session or more of this kind of reasons..

Than I go for the smallest dataset which is possible, which does not
directly denies what you are writting however it can conflict with that,
therefore just a note.

Cor

 

[William Ryan eMVP]

Good point Cor.

I guess my main point was that in general, grouping tables in one dataset
follow the collections methodology throughout the framework and follows the
Local Database metaphor.

Like you mention, there are going to be instances where this doesn't fit. I
should probably have been more articulate about when it would make sense to
have multiple datasets.

In the web example, you use, let's say that you have a bunch of large tables
but only need one or two of them for most of the app and at the end you
needed to write the data to xml as you mention and transfer it to a PDA.
While you could store the whole dataset in session state, you'd invariably
put a whole lot of stuff in SS that you don't need which is a waste of
resources. You could opt to store only the two tables of the dataset that
you'll use all the time in session state, but then you'd need to extract
them from Session variables and add them to one (or in this case) two
separate datasets if you wanted to write them to XML. In this type of
scenario, it may well be easier to use multiple sets and it may also be much
more efficient. I can also think of a few others where it would be simpler
to implement using multiple datasets, no doubt about it.

But on the whole, particularly on desktop apps, you can do most of what
you'll need to do with one dataset. Actually, you can do just about anything
just using one dataset but like you point out, it may not be the most
efficient way or the easiest to code.

The 'exceptions to the rule' would probably be a really good article to
write about ;-).

As always, you have some really good insight and raise some really good
points.

Thanks again,

Bill

--
W.G. Ryan MVP Windows - Embedded

www.devbuzz.com
www.knowdotnet.com
http://www.msmvps.com/williamryan/

 

[Guest]

Thanks guys. Precisely what I needed to know.

 

[int]

The fact that you are bringing in 10 tables (either way you choose) means
something is architecturally wrong.

The fact that no one even brought that up is a clear indication that there
are no practicing DBA's who replied to your question.

10 tables in anything, one has to wonder if there was a better way in the
first place. I certainly hope it wasn't a JOIN. I can see a UNION....but no
way, a JOIN.......

 

[Jon Skeet [C# MVP]]

The fact that you are bringing in 10 tables (either way you choose) means
something is architecturally wrong.

The fact that no one even brought that up is a clear indication that there
are no practicing DBA's who replied to your question.

10 tables in anything, one has to wonder if there was a better way in the
first place. I certainly hope it wasn't a JOIN. I can see a UNION....but no
way, a JOIN.......

You think 10 tables is too many to have in an application? It's
perfectly easy to have *way* more than that.

I would rather have lots of tables with few columns than a few tables
with masses and masses of columns.

Would you also like to limit applications to having fewer than 10
classes?

 

[Cor Ligthert]

Hi Int,

Do you really understand what is written, I did not see anywhere about
bringing in 10 tables.
In my opinion was the question using one dataset container for those tables.

The fact that you are bringing in 10 tables (either way you choose) means
something is architecturally wrong.

My point is that when you would do that in a very disconnected situation you
would have to clean that before you could serialize that, while that is in a
windowsituation not is necessary.

I see nothing wrong when you want to make a basic dataset which contains all
data for general use in the most common situations.

It is of course forever good thinking what you are doing, taking all columns
in a dataset with 100000 rows is different from taking one column . However
when it are one or two rows than I would not think to long about it.

It is an approach, when there was only one good approach there would not
have been the possibility to choose.

Just my thougth,

Cor

 

[Manu]

I agree it is always better to have only necessary columns then all the
columns... also the approach to include all the tables in one dataset is i
believe the more cleaner and logically understandable as it closely
resembles the Database. It also adds up to the benifits when using
DataRelations..

Manu

 

[William Ryan eMVP]

Whatever. That's such a silly statement to make it's hardly worth arguing.
First off, A dataset doesn't necessarily have anythign to do with a
Database. Totally decoupled BY DESIGN. You can use a DB to fill a dataset,
but you can use an Excel File, a CSV file or half a dozen other things.
What if you had 12 excel sheets in one workbook? What would a genius DBA
recommend here? Loading 12 different datatables? Ooops, that's what we'd
do anyway. Load 12 different datasets? Boy that'd be a great waste or
resources and add a ton of complexity where it wasn't needed.

Let me ask you a few questions then. First, why do datasets allow for 10
tables, or even 100 if it's so bad? Perhaps this in and of itself isn't
proof but it's just as good an indication as your comment. BTW, I was
trained in both Oracle and SQL Server administration and spent the first
part of my career doing Oracle administration. What in the heck that has to
do with a Dataset is beyond me. Datasets aren't the same thing as databases
although they attempt to follow a similar metaphor. Again though, what does
that have to do with anything?

Also, what if you read in 10 different XML Files, 5 of which were directly
related and 5 that weren't? You do know that XML is a primary way you get
data for datasets right? Or didn't they teach you that in DBA School?

And how exactly would you do a Union in ADO.NET? Fill the same table from
two different sources? Boy that would be fun to update and make a whole lot
of sense.

Actually, I'm sorry for being so abrupt. I just think it's totally
unnecessary for someone who doesn't have a clue to make such silly
statements that just serve to confuse people. You don't know what you are
talking about, even a little bit in this regard. You may be super DBA and if
so, I'm sure there are people over in the SQL Server ng or Oracle NG or
whatever DB you administer that could use some of your brilliance. If you
want to learn, than by all means stick around. Just please refrain from
saying such patently incorrect things.

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

 

[William Ryan eMVP]

Just so there's no confusion, my comments were directed to int... it look
like I was criticizing Cor but I wasn't. sorry for any confusion.

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/