Improving queries resource usage

[John]

Hi

I have a 10 user access backend/front end app which runs sufficiently ok.

Recently I deployed a winform/ado.net app that runs a sequence of queries on
the access backend db using OleDb ExecuteNonQuery method.

Problem is some users have started reporting frozen front end access app. I
can not say for sure that my ado.net app is responsible but problem seems to
have come up in the same time frame. Is there any way I can avoid this sort
of blocking and specifically if I can have a shorter timeout period for
queries if a record/table is being used and can not be made available to the
query?

Any other ideas to help the situation would be much appreciated.

Many Thanks

Regards

 

[William Vaughn \(MVP\)]

Ah, Access/JET is notorious for this kind of issue. When you hammer the
client engine, it postpones any changes to the shared database file until it
goes into idle mode. Of course, a better choice might have been SQL Server
Express, but I expect it's too late for that now. Make sure that you don't
have too many indexes as that slows down the updates. You can set the
Command timeout in the Command object--see the CommandTimeout property.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[John]

Is it good idea to use transactions in this case?

Thanks

Regards

 

[Larry Linson]

I have a 10 user access backend/front end app which runs sufficiently ok.

Recently I deployed a winform/ado.net app that runs a sequence of queries on
the access backend db using OleDb ExecuteNonQuery method.

Problem is some users have started reporting frozen front end access app. I
can not say for sure that my ado.net app is responsible but problem seems to
have come up in the same time frame. Is there any way I can avoid this sort
of blocking and specifically if I can have a shorter timeout period for
queries if a record/table is being used and can not be made available to the
query?

Any other ideas to help the situation would be much appreciated.

For an application you can do in Winforms, there's a good likelihood that
you should put down the glass of Kool-Aid, back away slowly, and create the
application in Access to use the Jet or ACCDB database. Single user,
workgroup applications, or client applications to server databases running
on a LAN are just what Access was intended to handle, and it handles them
very well indeed. And, Access Queries are processed by Jet, in the native
language of the Jet database engine.

It's far from being "notorious", and, by the way, from what you stated, it
does not seem to me that Access nor Jet is involved at all, in any case. As
far as I am aware neither ADO.NET nor OLEdb employs the Jet database engine
to access a Jet-format database, nor, for that matter, an Access 2007
ACCDB-format database. But, as I don't use either ADO.NET nor OLEdb, my
understanding could be wrong.

If this were a client application using server DB tables (which I understand
it is not), I'd advise (as does the Access team, now) using MDB and DAO for
client applications with tables linked via ODBC.

Bill's suggestion regarding indexes is good advice for any database I've
used, though I didn't see anything in your post to indicate you had
over-indexed. But if your application were an Access application, I'd
suggest first looking at the locking options. Maybe you could specify that
in ADO.NET or OLEdb.

Larry Linson
Microsoft Office Access MVP

 

[William Vaughn \(MVP\)]

Transactions would "serialize" the operations so that other users would be
blocked while the operation is taking place but force the engine to write
immediately. Is this the behavior you want? IMHO, the problem you're facing
is that JET is a local (client-based) DBMS engine and has to perform all
physical IO over the network. Every disk seek, read and write has to be done
over the network as if the file was local to the system--it's not. This
process is repeated by every single user against the same file using the
same network. These operations have to share the network bandwidth with
every other operation on the wire. Note that the JET locking mechanism is
fairly crude so it does not take much to lock out other users or lose
something in the UPDATE process (as when some user gives up and turns off or
reboots their system). I wish there was some magic bullet to fix these JET
issues but there isn't--not short of redesigning your application to use SQL
Server or some other serious DBMS. And no, this is not simply a matter of
moving your data to SQL Server or some other service-based DBMS. While some
take that approach, they often find that things are not much better as their
fundamental design hinges on JET's strength and around JET's weaknesses.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[William Vaughn \(MVP\)]

Ah no. Accessing a pre-2007 "Access" database via OLE DB through ADO or
ADO.NET invokes a version of the JET engine specifically written for Access
and those applications that need to access .MDB database files. For 2007 MS
found the need to break away from the OS-installed JET engine and use the
ACE engine which is an extension to JET.

I've been documenting JET and VB since VB3 (where I wrote the VB2. 3, 4 and
5 Microsoft documentation) and written several books that discuss these very
old issues. ADO.NET is not an engine at all, nor is OLE DB-both are data
access interfaces designed to access specific back ends via a managed
interface (as those built into the Framework or via an ODBC or OLE DB data
driver or provider-specific to the target engine. At this point in time
Access/JET databases are accessed through the .NET OleDb data access stack
and the new ACE.OLEDB.12.0 provider which is new for the 2007 version of
Access. It's derived from JET but is different than the JET now shipping
with the OS (see this article). DAO is a data access interface specific to
JET but I doubt if it will work against the 2007 version. Yes, as with any
native interface, it can provide more functionality than any
"one-size-fits-all" interface as exposed by OLE DB or ODBC.

For me and my customers, Access has been the root cause of too many serious,
complex and tough-to-solve issues over the last 15 years. Many of my books
have large sections that talk about accessing JET and preventing or
mitigating these pitfalls. I have worked with many customers (often large,
sophisticated corporations with many, many small departments) that report
users (often "paradevelopers" having tried to use Access databases and ended
up with a totally undisciplined mess of unsecured, unreliable and poorly
performing databases. These companies have characterized the proliferation
of JET/Access databases as a virus and I agree. Some report that tens of
thousands of JET databases are scattered all over the organization and the
IT department cannot rein them in to protect them, back them up or replace
them with something more suitable for their organizations. This means the
users have data that should be protected (by law) but isn't, unhappy clients
and unhappy IT people trying to sort it all out.

Is this always the case? Hardly. Is this all Access' or JET's fault?
Hardly. However, Access makes things so simple, less sophisticated
"developers" (what I call paradevelopers) have created these applications
thinking that they could go a lot further than they really could. There are
lots of small companies, private organizations and individuals (myself
included) that use Access databases without issue. My point is that Access
has its limits and developers need to know these limits before they choose
this approach for applications that have even the remotest possibility of
scaling or where data must conform to IT standards or accounting standards
or where a company's reputation depends on the performance, scalability and
reliability.

No, you're right. SQL Server is not a panacea-it is not the ultimate
solution to all of the world's database ills. Nor is Visual Studio the
ultimate front-end tool. IMHO it's far from it. I have worked for many many
years both inside and outside of MS to get both of these to work better
together and leverage some of the innovations that Access affords. SQL
Server is, however a far more serious (feature-rich) DBMS that can better
protect data and provide scalability from single-user to thousands of users
and beyond. It can protect data as Access/JET cannot using sophisticated and
time-tested logging and rights management paradigms. When it's part of the
design, the developer has only to buy a more sophisticated license to
leverage the additional horsepower of the advanced SQL Server engines.

The problem (as I see it) with Access is that customers are led to believe
that it's scalable from dozens to "up to 255" users. They're not told that
their data is relatively insecure and subject to corruption through some
very well-documented failure scenarios. While there are cases where a very
sound Access/JET design and implementation can stretch into the dozens of
users under the right circumstances, even those (excellent) implementations
cannot be scaled further or take advantage of the copious features these
customers need when their data requirements get more complex-as they so
often do even though they had not expected or planned for them to do so.
When customers build SQL Server solutions (with the exception of the SQL CE
platform) they have a seamless upgrade path. That cannot be said about
Access or Visual Basic applications built against its databases.


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________