Re: Improving queries resource usage by William
William
Tue Jul 22 10:38:08 CDT 2008
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)
____________________________________________________________________________________________
"Larry Linson" <bouncer@localhost.not> wrote in message
news:#pTGqC56IHA.2348@TK2MSFTNGP06.phx.gbl...
> "John" <info@nospam.infovis.co.uk> wrote
>
> > 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
>