Running queries with low priority in ado.net on access database

[John]

Hi

We have an access database with several users. I run a number of queries in
sequence via ado.net on the same database form time to time. The problem is
that when the queries are run the users are almost locked out of database
due to low responsiveness of the database during the period the queries are
run. I am running the queries via OleDBCommand.ExecuteNonQuery().

Is there any way to run these queries at low priority? Also what is the
correct way to give delay between queries to allow access database to be
responsive to other users?

Many Thanks

Regards

 

[Sylvain Lafontaine]

If the database is located on another machine than the machine on which the
queries are run, then try running them directly on that remote machine in
order to diminish the traffic jam. This is also a situation where you'll
probably find an advantage remplacing the mdb file with SQL-Server as the
backend database.

 

[John]

Hi

Thanks. The database is on the same machine (server) physically. Its not
easy to switch to sql server for now as this will involve rewriting the
front apps.

Thanks again.

Regards

 

[Sylvain Lafontaine]

To my (limited) knowledge, you cannot lower the priority of this process.
Furthermore, because Access puts locks at various place when it's modifiying
the database, slowing it down will have the catch-22 effect of slowing down
everyone else because they will possibly be put on hold because of these
locks. Beside buying a more powerful machine, there are general guidelines
for increasing the performance of an Access database; see:

http://www.granite.ab.ca/Access/performancefaq.htm
http://office.microsoft.com/en-us/access/HA102103471033.aspx

Some of these recommandations will have no effect when both the frontend
(the user interface) and the backend (the database itself with the data) are
on the same machine.

 

[William Vaughn \(MVP\)]

Ah when you chose Access/JET you chose to develop with a toy database engine
that's been shown to lack the sophistication to share data efficiently. For
each engineer that has been able to optimize their JET systems to support
more users, there are a thousand more that have not.

Hi

We have an access database with several users. I run a number of queries
in
sequence via ado.net on the same database form time to time. The problem
is
that when the queries are run the users are almost locked out of database
due to low responsiveness of the database during the period the queries
are
run. I am running the queries via OleDBCommand.ExecuteNonQuery().

Is there any way to run these queries at low priority? Also what is the
correct way to give delay between queries to allow access database to be
responsive to other users?

Many Thanks

Regards

--
__________________________________________________________________________
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]

et tu bill?

Ah when you chose Access/JET you chose to develop with a toy database
engine that's been shown to lack the sophistication to share data
efficiently. For each engineer that has been able to optimize their JET
systems to support more users, there are a thousand more that have not.



--
__________________________________________________________________________
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)
____________________________________________________________________________________________