Jet Read Cache Test - What's Up?

[farmerjohn1958]

VB Front End -> ADO -> OLE DB Prov -> Net Share MDB on LAN
OplocksDisabled = 1 on Client.

db is an Open Connection to MDB - Shared Mode - One Active User

rs.Open "Select * From UserTab Where LoginId = 'person'", db,
adOpenStatic, adLockOptimistic

Repeated 4 times in a row

Log from db_WillExecute & db_ExecuteComplete events

Ticks: 561 Select * From UserTab Where LoginId = 'person'
Ticks: 550 Select * From UserTab Where LoginId = 'person'
Ticks: 551 Select * From UserTab Where LoginId = 'person'
Ticks: 721 Select * From UserTab Where LoginId = 'person'

Ticks are large due to net latency on the LAN, But ....
Are pages supposed to be cached locally in the Connection's read cache
so the latter 3 queries should in theory be faster?

 

[aaron.kempf]

dude if you give a crap about performance?

use SQL Server
Access Data Projects are faster development, debugging,
troubleshooting and performance

it is criminal neglect to still use MDB for anything

 

[farmerjohn1958]

Oh. I forgot to mention that I don't need to be told about migrating
to SQL Server, or Oracle, or Dot Net, or Java, or C# or ADP (hehe),
or ...

I just want to know if the 3 queries are supposed to be read from
locally cached data.
And If so, why that isn't happening.

Page Timeout is the default 5000ms

 

[aaron.kempf]

STFU kid

move to Access Data Projects and you won't have to worry about this
kinda crap

SQL Server 'just works'
and MDB my farmer friend?

IT DOESN'T

 

[farmerjohn1958]

i suppose this isn't a surprise in a pub newsgroup.

 

[Tony Toews [MVP]]

i suppose this isn't a surprise in a pub newsgroup.

Regretfully no. Aaron is well known to us.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[farmerjohn1958]

VB Front End -> ADO -> OLE DB Prov -> Net Share MDB on LAN
OplocksDisabled = 1 on Client.

db is an Open Connection to MDB - Shared Mode - One Active User

rs.Open "Select * From UserTab Where LoginId = 'person'", db,
adOpenStatic, adLockOptimistic

Repeated 4 times in a row

Log from db_WillExecute & db_ExecuteComplete events

Ticks: 561 Select * From UserTab Where LoginId = 'person'
Ticks: 550 Select * From UserTab Where LoginId = 'person'
Ticks: 551 Select * From UserTab Where LoginId = 'person'
Ticks: 721 Select * From UserTab Where LoginId = 'person'

Ticks are large due to net latency on the LAN, But ....
Are pages supposed to be cached locally in the Connection's read cache
so the latter 3 queries should in theory be faster?

I know this Q was an anachronism, but legacy is a bitch.

- set db.cursorlocation to adUseServer
- set rs = db.Execute SQL instead of above (i.e. forward only, read
only)
- no rs.close between db.Execute statements (only when really done)
- now much faster

Conversion from Jet will happen $omeday.

 

[aaron.kempf]

well when you migrate from Jet I militantly recommend ADP

serious

right-click import and you're 2/3rds done

 

[aaron.kempf]

MDB does NOT support caching of anything

you must be thinking of SQL Server

 

[aaron.kempf]

locally cached data?

what are you smoking?

MDB doesn't spport working across a network

 

[farmerjohn1958]

I know this Q was an anachronism, but legacy is a bitch.

- set db.cursorlocation to adUseServer
- set rs = db.Execute SQL instead of above (i.e. forward only, read
only)
- no rs.close between db.Execute statements (only when really done)
- now much faster

Conversion fromJetwill happen $omeday.

My prob was the jet provider reading table metadata from the mdb on
rs.Open.
Slow & expensive over a high latency LAN. By keeping a recordset for
the table open, subsequent queries don't require the provider to go
back over the wire to read the mdb. it uses the already loaded
metadata.

So by doing something like this up front:
cacheRS.Open "Select * From UserTab Where 0=1", db, adForwardOnly,
adReadOnly
and leaving it open, the provider has local access to the table/column
info

The rest of the code is basically unchanged & it runs much faster.

In my case, once the table data pages are loaded into the provider's
read cache, the Ticks for subsequent queries goes to 0. Very
helpful when it takes a bunch of queries to populate an application
screen.

 

[aaron.kempf]

In an alternate world; maybe Jet might be usable

but here and now?

it lost it's crown due to stability and performance

little crap workarounds like this?
this is just not necessary when you use a 'real mans database'

like Access Data Projects for example

 

[farmerjohn1958]

In an alternate world; maybeJetmight be usable

but here and now?

it lost it's crown due to stability and performance

little crap workarounds like this?
this is just not necessary when you use a 'real mans database'

like Access Data Projects for example








- Show quoted text -

Aaron, I have to admit you are pretty funny.

The alternate world is legacy apps with no business case for a re-
write. Little crap workarounds abound in the alternate world. VB6/
Jet certainly wouldn't be used on a here&now new start.

Now where's my baling wire .....

 

[aaron.kempf]

so are you really a farmer?

we used to have a dozen cows growing up; nothing too exciting.. they
were mainly pets LoL

and a couple of horses and pigs..

but vegetables are what I really get excited about

I need to move out to the sticks and get a tractor myself.. get out of
this whole development thing..

I love databases.. I just don't agree with .NET.. I just don't see it
as a practical solution for desktops or webservers.

If _EVERY_SINGLE_VENDOR_ in the world sold Server 2003 Web Edition on
EVERY POSSIBLE SERVER then it might be palatable.

but as it is? paying for Windows 2003 Standard for a webserver?
ROFL

deploying VB.net apps on the desktop?

ROFL

I just find it humorous-- all of the other office vendors have
somethnig similiar to ADP.
OpenBase can 'hook' directly to a mySql database; no horsing around
just tables and server side objects, if I recall it correctly

I would just like to see something from A-Z that would look and feel
JUST LIKE ACCESS but webbased and based on php / mysql

that would rock

as it is; Microsoft had a Full House a decade ago; and they've
squandered their lead on crap like the Xbox and 47 new programming
languages.

VB won the popularity war.

And Microsoft betrayed us all--stabbed us all in the back-- by not
making a practical alternative to Vb6 and MDB.

ADP is close. It is a friggin great database... but because these
people spread mis-information (like how it isn't in 2007.. rofl)

But would I start a large project in ADP today? Of course I would.

I just wish--- for the life of me-- that I could build a time machine;
get an M16 and go back and shoot the mother fuckers that invented MDB

seriously-- just like the terminator movies-- go back in time and
shoot those kids that spawned a crap database upon the world-- and
convince someone at MSFT to take ADP mother friggin seriously.

As it is; Microsoft will never be competitive with Crystal Reports
because they don't take MS Access dead cold sober serious.

they need to completely seperate the DATABASE from the FRONTEND.

MS Access makes a killer frontend.

but anyone using MDB for anything should be fired and then spit upon.

I do believe that is a halfway-politically correct maneuver

fire.. and then spit on them.

if it was the other way around (Spit and then Fire); you might have
some legal troubles LoL


-Aaron