OleDb / Jet is randomly slow on a computer

[Patrice Dargenton]

Hello, I have a bug with a computer : some OleDb queries on an Access
database are randomly very slow : 3 seconds instead of 15 milliseconds
usually, it is slow only for "large" Access database (larger than 20 Mb,
with 10 Mb database, this is normally fast). It is the first time I see
the bug after many years using my application : the computer is a Windows XP
installed on a MacBook Pro with Boot Camp : I don't know why there would be
a problem with it, because everything else is OK. Does anyone has an idea
about this problem ?

I did try to log the adonet activity at a low level, but the log is too
large to find anything usefull :
Troubleshooting Windows DAC/MDAC (BidInterface)
http://msdn.microsoft.com/en-us/library/ms693114(VS.85).aspx

Notes :
Every updates have been installed : Windows, DotNet, ...
Provider=Microsoft.Jet.OLEDB.4.0
DotNet 2 / VB 2008 Express
OleDbCommand / ExecuteScalar

 

[Gregory A. Beamer]

Hello, I have a bug with a computer : some OleDb queries on an Access
database are randomly very slow : 3 seconds instead of 15 milliseconds
usually, it is slow only for "large" Access database (larger than 20
Mb, with 10 Mb database, this is normally fast). It is the first time
I see the bug after many years using my application : the computer is
a Windows XP installed on a MacBook Pro with Boot Camp : I don't know
why there would be a problem with it, because everything else is OK.
Does anyone has an idea about this problem ?

Over time, with Access, the database size will degrade query speed. With
indexing, you can reduce the problem.

The issue, however, is what is really causing the "randomness". Are you
not disposing connections and having contention, for example? Or is this
not really random.

In addition, are you running different types of programs while you are
running these queries, as something else might be "randomly" (not really
random) using CPU.

In addition, if you are running on Windows in Mac, is this part of the
issue?

Unfortunately, I cannot pinpoint a single issue that I can tell you to
fix, as there are too many variables in the equation.

peace and grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Patrice Dargenton]

Hello, thank you for your reply.

Over time, with Access, the database size will degrade query speed. With
indexing, you can reduce the problem.

Yes, but with all the over computer, queries are fast.

The issue, however, is what is really causing the "randomness". Are you
not disposing connections and having contention, for example? Or is this
not really random.

It is really random.

In addition, are you running different types of programs while you are
running these queries, as something else might be "randomly" (not really
random) using CPU.

No, it is a clean new Windows XP SP3, with MS-Office 2000, but I will check
if the Office indexer is launched.

In addition, if you are running on Windows in Mac, is this part of the
issue?

Perhaps ? But this is unlikely : I don't know why it would be different : Is
the dotnet compiled and optimised for a particular computer ? Or perhaps
there is too much RAM for Windows XP (2.7 Gb) ? I don't know.

Unfortunately, I cannot pinpoint a single issue that I can tell you to
fix, as there are too many variables in the equation.

It is possible to tune more precisely the MDAC log using Troubleshooting
Windows DAC/MDAC (BidInterface) ?

 

[Patrice Dargenton]

I found something to find a solution : In Windows XP safe mode, the bug is
fixed : everything is fast.
I have to find which process is slowing the computer.

 

[Gregory A. Beamer]

I found something to find a solution : In Windows XP safe mode, the
bug is fixed : everything is fast.
I have to find which process is slowing the computer.

Most likely a driver issue or a TSR program (like antivirus, etc) or
malware. Hope you find the culprit.

Peace and Grace,

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Arioch]

I am also seeing randomly slow queries on 2 customer's new high end systems.
This happens both with C#/ADO.Net and VB6/ADO using the Jet 4.0 provider for
an Access 2000 mdb.

In this case we have a table without about 10,000 records that takes ~5
minutes for a simple select query with a WHERE clause on a nonindexed field.
On other systems these queries on the exact same database take less than 1
second.

There's a lot more troubleshooting info I can provide but first I was
wondering what the resolution was on the issue above.

 

[Mary Chipman [MSFT]]

Your best bet at getting help is to start a new thread with your issue
instead of jumping on an existing thread regarding another issue.
You'll get more attention that way ;-)

--Mary

 

[Gregory A. Beamer]

In this case we have a table without about 10,000 records that takes
~5 minutes for a simple select query with a WHERE clause on a
nonindexed field. On other systems these queries on the exact same
database take less than 1 second.

Same queries and same database?

1. Have you profiled the query?
2. Are you sure you have the right SQL libraries on the server to talk
to your .NET app?
3. Do you have an execution plan?
4. Are both servers up to the same patches?

There are tons of reasons why a query can go south, especially if you
are talking about hitting it from an application tier of some sort
instead of using a SQL tool on the server.

I agree with Mary about asking the question again, as threads tend to
not get visited as much once they have some history.

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Everett]

Thank you. I have started a new issue in the Access Database Programming group.

 

[Everett]

Thanks, Greg. I have started a new issue with quite a few more details,
although I'm not sure how to link to it from here. The title is "OleDb / Jet
query randomly slow on high end server 2008 systems" in the access
programming group.