DB Backend programming?

[cliopia]

Where can I find a good source (book, website etc) for best practices
for programming database applications? I inherited a c# distributed
application using OleDB/ADO.with an Jet backend and am puzzled by some
of the choices that wer made. The Jet database initially made a good
back-end because of its small footprint (nothing else to install), and
ease of customer use (they can write their own custom reports with
Access etc.) but we are now running up against some issues.

For example, the decision was made to use a single connection for all
database calls, both updates and selects. This, not surprisingly, is
causing some issues. Is using one connection even advisable? I'm
more used to opening and closing a connection per task, but this also
seems a bit excessive when potentially tens of thousands thousands of
records are being inserted or updated at a after the completion of each
task. Should we be using transactions?

Other questions I've been researching include whether we should we
consider changing our back end due to the large number of records
updated, inserted and selected concurrently? If we allow the users to
move the back end (currently on the same machine as the application ),
will the Jet database become less robust and more prone to corruption
If we do change back ends, would a MySQL/ MSDE etc. backend increase
the footprint and development time and technical skill needed by the
customer?

Any links, suggestion or advice would be appreciated.

Thanks,
mpf

[Albert D. Kallal]

<cl*****@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

For example, the decision was made to use a single connection for all
database calls, both updates and selects. This, not surprisingly, is
causing some issues.
You don't mention what issues here? You also don't mention if a network is
involved either.

Is using one connection even advisable?
Sure, it really does not matter in this case, since you are talking about a
file that is being opened like any other file (word, excel etc.). This is
just a file we are taking about.
I'm
more used to opening and closing a connection per task, but this also
seems a bit excessive when potentially tens of thousands thousands of
records are being inserted or updated at a after the completion of each
task.
I have to agree with the above 100%. You can likely pull in 30 to 50,000
records in he time it takes to establish and open a connection. This is a
instance waste of resources, and causes huge delays in the processing. You
are best to use one connection here. And, father, if you are using a network
(file share, more then one user), then you should in fact keep a persistent
connection open to the back end, as that can eliminate some MAJOR delays in
opening tables etc.
Should we be using transactions?
Gee, I don't think so?

You also don't mention the numbers of records, and the numbers of users you
have here?

I mean, if you only got an application with 50-60 highly related tables, and
say 5-6 users pounding the database all day,a nd the tables sizes are small,
say only 50,000 to 150,0000 records in a table, then response times should
be instant.

And, if no network is involved, and again you have such tiny tables as
above, then you generally will find the JET database about 40% to 200%
faster then sql server.

So, not knowing if a network is involved, and multi-users, it is hard to
make some recommends here. If we do change back ends, would a MySQL/ MSDE etc. backend increase
the footprint and development time and technical skill needed by the
customer?
Well, if the customer is going to run sql server (or any server based
system), the of course, the additional issues are that in setting up the
install, and having someone to maintain, and manage the server software.

As I mentioned, not know record counts, and number of fuses (if in fact this
is multi-user), and not knowing the num ber of users working at the same
time, this is really a shot in dark on my part.

If we allow the users to

move the back end (currently on the same machine as the application ),
will the Jet database become less robust and more prone to corruption

You have to trade off the issues of running sql server. Especially if this
is single user application on one pc. (you now have to have sql server
startup..and start running when your application starts. And, if the user
quits, do you shut-down, and un-load the server based software running? Or,
do you leave the server running, using up large amounts of memory on the
users computer?).

If you have a single user application here, then I don't know why there
would by ANY issues of corruption here? There are MANY commercial products
in the marketplace that use the ms-access "JET" database engine. The very
popular Simply Accounting for example (and, I NEVER heard of corruption
problems for that accounting package). I had MANY clients use applications
in single user mode, and again NEVER had any corruption problems.

It is very possible that your application has in fact outgrown JET, and
perhaps something else is in order...

After doing a compact and repair..how large is the mdb file?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[cliopia]

> You don't mention what issues here? You also don't mention if a
network i

involved either.
Sorry to be so skimpy on the details in the original post, as I was
looking for a recommendation for a book or website on best procedures,
but I certainly appreciate any advice given here.

Currently there is no network involved. The database and the
application reside on the same machine. There is a single user (the
application interface) per install. There are 20 interlinked tables.
Size ranges based on how heavily the application is used, but our
robust installations can easily get over 1 gig. There are several
tables which can get into the hundreds of thousands of rows.

When this project was started, we did not anticipate the volume of
records which would be processed. Thus it was tested on smaller data
sets with good results (i.e. no errors). When processing large numbers
of records (insert, update and select) I've been encountering
problems with a) poor interaction between threads and 2) volume of
records.

There are several threads in this applications. When a process is
running, one thread will refresh the console with the results of the
second process thread which, while it is running, constantly updates
or inserts into the database. Depending on the task the user is
performing, this could run into the 10s of thousands of records with
~30 fields in the table. Occasionally we get the error: '
ExecuteNonQuery requires an open and available Connection. The
connection's current state is Open, Executing.' usually on the
update side of things. This despite the fact we lock the database when
we do the updates. This is my main issue with the one connection.
I'm wondering if using two connections (one for the console, one for
the update thread) might alleviate this issue.

The volume of records issues comes when those 10s of thousands of
records need to be loaded and displayed (sometimes when an update
process is running in the background). I'm sure there are design
flaws here - the records take forever to load. We use a OleDBReader
and even though we paginate (i.e. display only 1000 rows at once) it
still takes a while to advance through the recordset to reach the
proper point. What is proper method of pulling large chunks of data
at one time?
I mean, if you only got an application with 50-60 highly related tables, > and say 5-6 users pounding the database all day, and the
tables sizes > are small,say only 50,000 to 150,0000 records in a
table, then response > times should be instant.

Our application certainly falls into these parameters (except the
number of records which can get much higher), but we are not achieving
anything like instant response time.
And, if no network is involved, and again you have such tiny tables as above, then you generally will find the JET database about 40% to 200% faster then sql server.
We would like to stay with our current setup, but might migrate to a
network based connection in the future.
Well, if the customer is going to run sql server (or any server based system), the of course, the additional issues are that in setting up the install, and having someone to maintain, and manage the server software.

Yes, that is one reason I would like to fix what we have rather than
throw money at what is essentially a design problem. Besides, we
already have one product to support, who wants to do two.
It is very possible that your application has in fact outgrown JET, and perhaps something else is in order... From your earlier comments, this doesn't seem to be the case. Somewhere there is a design flaw and solving that will solve many of
our issues.
After doing a compact and repair..how large is the mdb file?

The mdb file size varies with installation, but the volume issue seems
to occur with recordsets are greater than 10,000 records.

Thanks Again,
mpf

[David W. Fenton]

cl*****@hotmail.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

The mdb file size varies with installation, but the volume issue
seems to occur with recordsets are greater than 10,000 records.

That number of records is absolutely trivial -- you shouldn't notice
anything about speed until you get to 500K records or so (unless
you're doing something really inefficient with those records, like
selecting or sorting on non-indexed fields or expressions), so it's
quite clear that there's something wrong with your data access
methods, either the actual method or with the SQL you've written to
do it.

Since you're talking about "connections" I suspect you're using ADO,
and while ADO is great for talking to SQL Server, it's inferior to
DAO when talking to Jet.

And why worry about connections with a single-user database that
isn't even on a network?

What's the planned eventual deployment architecture? That's what
should set your choice of methods.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc