Re: Explorer CPU Usage while accessing database

Davor wrote:
> Hello
>
> What has explorer.exe to do with DB accessing?

Since Jet is a file-based database, the answer is: more than one would
realize. Changes to files must be tracked by explorer (LastModified date for
example)

> Here a simple code:
> ------------------------------------------------
> Set cDB = CreateObject("ADODB.Connection")
> Set rsSession = CreateObject("ADODB.Recordset")
> cDB.open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
> "db1.mdb" & ";"
> rsSession.Open "[Session]",cDB,0,3,adCMDTable
>
> Do while true
> rsSession.AddNew "SomeNr", clng(rnd*10000)
> wscript.sleep 10*1000
> loop
> -------------------------------------------------------
>
> Here is the thing: If I change the wscript.sleep to 10 seconds, approx
> inbetween the "add's" the explorer.exe will jump tot about 25-40% CPU
> time. This occurs if i put the sleeptime to 5 sec too... BUT NOT if I
> put it to 1 Second. Then explorer.exe stays at 0-1%max!.
>
> I don't understand how this is possible. It's like the connection is
> 'closing' if the time between add's is bigger than some value, but i
> can't find this 'value' in the object connections's properties...
>
> Has someone any clues? I am trying to update the DB about every 10
> seconds but with as little CPU overhead as possible.
>

I suspect you are seeing the Jet delayed-write system in action. The Jet
provider optimizes performance by caching writes to the database, doing the
actual writes when the system is idle. Your Sleep command effectively
creates an idle period, during which Jet (and any other processes that are
waiting for an idle period) flushes its cache, which could lead to a chain
reaction of the other activity you see from Explorer. Granted, the
Jet-related activity should be finished fairly quickly (I'd be shocked if it
took more than half a second, unless other activity involving this database
file is in the cache ...), so I suspect the longer sleep times are likely
allowing non-database related activity to occur, activity that usually takes
place during idle periods - perhaps a defragger or scanner at work?


If you REALLY want to optimize performance, you will stop using a cursor
(recordset) for an operation that can much more efficiently be accomplished
by an Insert statement. Also, take advantage of OLE DB Session Pooling by
opening your connection before each insert and closing it immediately after
the insert. The less time you are actually connected to the database, the
better for performance and scalability (if you are interested in
scalability, that is - perhaps that's not an issue in this case?)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: Closing Jet Database seems unreliable
    ... Could it be that JET has not finished writing the cache to the database? ... and the connection ... I want to do this so that my app can backup or restore the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: test connection failed
    ... .udl contains connection string and path to database (for Microsoft Jet ... Check also that you have Jet provider installed or install Jet SP from ...
    (borland.public.delphi.database.ado)
  • Re: ADO Connection Timeout
    ... connection to my Access database becomes unavailable. ... Jet, and it's free. ...
    (microsoft.public.data.ado)
  • Re: Explorer CPU Usage while accessing database
    ... it has to do Jet delayed-writing and explorer 'tracking' ... > provider optimizes performance by caching writes to the database, ... > creates an idle period, during which Jet (and any other processes that are ...
    (microsoft.public.scripting.vbscript)