newbie: Database vs. CVS file

[Russell]

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a secondary
table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store the
matrix values, then also used an array3[a] and array4 to store the row
and column info.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a secondary
table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store the
matrix values, then also used an array3[a] and array4 to store the row
and column info.

It is probably slightly faster to read data in from the CSV files
to memory and access it there.

But I think there are many good reasons to use a database:
- you will be much better positioned if data becomes
too big to be in memory
- much better support for accessing data via .NET classes
- reuse of data by other applications
etc.etc..

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

To extend the advice that Arne offered, I would say that if your app is
the only one accessing this data, then using Access would be ok. However, I
wouldn't come to rely on it. I think that a much better option is to use
SQL Server Express.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a
secondary table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store
the matrix values, then also used an array3[a] and array4 to store the
row and column info.

It is probably slightly faster to read data in from the CSV files
to memory and access it there.

But I think there are many good reasons to use a database:
- you will be much better positioned if data becomes
too big to be in memory
- much better support for accessing data via .NET classes
- reuse of data by other applications
etc.etc..

Arne

 

[Bruce Wood]

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a secondary
table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store the
matrix values, then also used an array3[a] and array4 to store the row
and column info.

First of all, if you can use more modern configurations, I would
investigate SQL Server Express, MS's new freely distributable
mini-database. Access has its advantages: it's quick and easy to write
a little app / spreadsheet for maintaining your data being the big one.
However, it's not scalable to a larger (multi-user) database, if you
should ever want to do that, and although Access can be pressed into
service as a multi-user solution, it's really not good at it.

That said, this all depends upon whether this is information that your
application just reads in, or if it modifies the information. If it
just reads it in, does the information change often, or is it
relatively fixed? There are various storage solutions depending upon
what you need to do.

1. Application config files. Especially in .NET 2.0, there is healthy
support for application configuration files. Now, I tend to take the
name at its word, and use those only for storing information about how
my app is configured. However, others here swear by using these for
information that changes, such as window sizes and positions. I would
say that if your information is meant to be shipped with the
application, and a change to that information would sort of be
considered a 'new release", then consider using a config file.

2. XML, or "roll your own config file," as it were. I would prefer this
to comma-separated values. XML has the disadvantage that you can't
easily manipulate it from Access (I don't think), but you _can_ add new
attributes without breaking your existing applications. This may or may
not be a consideration.

3. Access (MDB), which I must grudgingly admit is a good solution if
you need to whip together some simple screens for maintaining your
information. This supposes that it has to be very easy to modify the
information because untrained people will be doing it, or because it
has to be done often.

4. SQL Server Express. Harder to change the information (you would have
to write your own maintenance screens, probably in C#), but if you ever
need to port it to a multi-user world (full SQL Server) then it will be
very easy to do that.

There are probably lots more. I leave it to the more data-savvy here to
fill out the list (and correct my probably naive assessments of some of
the options).

 

[Carl Daniel [VC++ MVP]]

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a
secondary table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store the
matrix values, then also used an array3[a] and array4 to store the row
and column info.

Are these data static, or does your program update the data at runtime?

If the data is static, I'd keep it as a CSV file. You can open a CSV file
with ADO.NET and read it just as if it was a real database. If at some
future time the data has to change at runtime, you can easily swap in a true
database while making only very minor changes to your code.

On the other hand, if the data is not static, then you definitely want some
kind of database, in which case I'd be strongly inclined towards SQL Server
Express. The only big downside to SQL Express is that it has a rather
larger installer, and a large footprint once installed, compared to smaller
embedded databases. The big upside to using SQL Express is that it IS
SQLserver, so you have the full power of an industrial strength database
engine at your disposal.

-cd

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

To extend the advice that Arne offered, I would say that if your app is
the only one accessing this data, then using Access would be ok. However, I
wouldn't come to rely on it. I think that a much better option is to use
SQL Server Express.

SQLServer has some huge advantages over Access.

But there are certainly also some scenarios where
Access is a better choice due to simpler deployment.

One size does not fit all.

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

Arne,

True, but to be honest, there is little advantage, if any, that access
has over SQL Server express. It's not necessarily easier (it's an MSI
install for SQL Server Express, you need to have the ole db provider
installed for access) to install, and SQL Server express is definitely much
more robust.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

True, but to be honest, there is little advantage, if any, that access
has over SQL Server express. It's not necessarily easier (it's an MSI
install for SQL Server Express, you need to have the ole db provider
installed for access) to install, and SQL Server express is definitely much
more robust.

You need to make sure that SQLServer gets installed and not to
mess up an existing installation.

I belive that most PC'es has the Access provider installed. The
last time I think I saw a PC without was a Win95.

Arne

 

[Nicholas Paldino [.NET/C# MVP]]

Arne,

We aren't talking about SQL Server, but SQL Server express. The MSI for
SQL Server express is incredibly non-involved and easy to install, and
handles multiple installs for you easily.

 

[Russell]

The data is strictly constant. The only reason for having it outside the
code is for maintenance. Not wanting to have to recompile the code if we
need to add, remove or change motor information.

Thanks to all who replied - it gave me additional insight for future
applications.

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a
secondary table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions per
minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store
the matrix values, then also used an array3[a] and array4 to store the
row and column info.

Are these data static, or does your program update the data at runtime?

If the data is static, I'd keep it as a CSV file. You can open a CSV file
with ADO.NET and read it just as if it was a real database. If at some
future time the data has to change at runtime, you can easily swap in a
true database while making only very minor changes to your code.

On the other hand, if the data is not static, then you definitely want
some kind of database, in which case I'd be strongly inclined towards SQL
Server Express. The only big downside to SQL Express is that it has a
rather larger installer, and a large footprint once installed, compared to
smaller embedded databases. The big upside to using SQL Express is that
it IS SQLserver, so you have the full power of an industrial strength
database engine at your disposal.

-cd

 

[Bruce Wood]

The data is strictly constant. The only reason for having it outside the
code is for maintenance. Not wanting to have to recompile the code if we
need to add, remove or change motor information.

Thanks to all who replied - it gave me additional insight for future
applications.

I would say that the application config file is the way to go, then.

 

[Carl Daniel [VC++ MVP]]

I would say that the application config file is the way to go, then.

I'd tend to agree, but if it's more than a handful of numbers, formatting
columnar data into the app config file will be more than a bit unnatural.
Writing a custom section handler would make it bearable, and it'd certainly
be better from a memory usage standpoint than dragging in all of ADO.NET,
OLE-DB and the Jet engine to read a csv file.

If (for example) the data is already being maintained in a spreadsheet, then
CSV file has a natural advantage since that can be exported directly from
Excel.

-cd

 

[Barry Kelly]

To extend the advice that Arne offered, I would say that if your app is
the only one accessing this data, then using Access would be ok. However, I
wouldn't come to rely on it. I think that a much better option is to use
SQL Server Express.

Don't forget about Firebird, folks (www.dotnetfirebird.org) - as it's
embeddable, you only need to distribute a .DLL along with the other
assemblies in your app, and you've got full SQL etc. embedded in your
app, no further install needed.

-- Barry

 

[Barry Kelly]

On the other hand, if the data is not static, then you definitely want some
kind of database, in which case I'd be strongly inclined towards SQL Server
Express. The only big downside to SQL Express is that it has a rather
larger installer, and a large footprint once installed, compared to smaller
embedded databases. The big upside to using SQL Express is that it IS
SQLserver, so you have the full power of an industrial strength database
engine at your disposal.

I'd note that Firebird embedded doesn't have limits like SQL Express
does, you can keep the DB in a single file, and it has a very small
footprint (1.5 mb) and no install needed. It's pretty hard to beat.

-- Barry

 

[Cor Ligthert [MVP]]

Russel,

If the data is completely constant, than in my idea the DataSet file is the
way to go.

If it has not to be updated, than it combines the advantages of a flat file
with the possibilities a database gives you (don't think than an SQL
transact code it has its own methods).

To make an XML dataset on disk is nothing more than roughly typed here.

\\\
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataColumn cn1 = dt.NewColumn("cn1");
DataColumn cn2 = dt.NewColumn("cn2");
dt.Columns.Add(cn1) //for this are more methods
dt.Columns.Add(cn2)
//fill the rows for this are a lot of methods
ds.WriteXml("c:\mywhatever.xml")

To read it
ds.ReadXml("c:\mywhatever.xml")
///

I hope this helps,

Cor.

The data is strictly constant. The only reason for having it outside the
code is for maintenance. Not wanting to have to recompile the code if we
need to add, remove or change motor information.

Thanks to all who replied - it gave me additional insight for future
applications.

What is faster or cleaner, a MDB database or comma delineated file?

The type of data that would be stored:
Hydraulic motors, and pertinent descriptive attributes, plus a
secondary table that would store matrix style data.

The matrix data are entries for a given gallons per minute of hydraulic
fluid at a given pressure the output of the motor is iRPM revolutions
per minute and the torque is sngTorque. Each motor will have a matrix
approximately 8 rows and 15 columns with each entry having two values.

In previous application I used an array1[a,b] and array2[a,b] to store
the matrix values, then also used an array3[a] and array4 to store
the row and column info.

Are these data static, or does your program update the data at runtime?

If the data is static, I'd keep it as a CSV file. You can open a CSV
file with ADO.NET and read it just as if it was a real database. If at
some future time the data has to change at runtime, you can easily swap
in a true database while making only very minor changes to your code.

On the other hand, if the data is not static, then you definitely want
some kind of database, in which case I'd be strongly inclined towards SQL
Server Express. The only big downside to SQL Express is that it has a
rather larger installer, and a large footprint once installed, compared
to smaller embedded databases. The big upside to using SQL Express is
that it IS SQLserver, so you have the full power of an industrial
strength database engine at your disposal.

-cd