Are Jet 4.0 Stored queries faster than sql w/ criteria specified as the record source of a form?

[bobalston9]

I am using Access 2003 with Jet 4.0. And Jet 4.0 supports Stored
Queries, something I have not paid any attention to until now.

http://msdn2.microsoft.com/en-us/library/aa140021(office.10).aspx

It seems that if I use the ADO syntax correctly, I can define a stored
query, with a parameter, for which I set the parameter value in VBA
(probably a global variable ??) and then use the query name in the
record source. I think that this will give me a stored procedure on
the BE jet4.0 that only passes the selected records.

MVPs (and others knowledgeable in this), does this sound correct to
you?

Anyone done any performance comparisons to see if the above ADO
process is worth the additional effort vs. the alternative of defining
a sql statement with the criteria as the record source?

Bob

 

[Rick Brandt]

I am using Access 2003 with Jet 4.0. And Jet 4.0 supports Stored
Queries, something I have not paid any attention to until now.

http://msdn2.microsoft.com/en-us/library/aa140021(office.10).aspx

It seems that if I use the ADO syntax correctly, I can define a stored
query, with a parameter, for which I set the parameter value in VBA
(probably a global variable ??) and then use the query name in the
record source. I think that this will give me a stored procedure on
the BE jet4.0 that only passes the selected records.

MVPs (and others knowledgeable in this), does this sound correct to
you?

Anyone done any performance comparisons to see if the above ADO
process is worth the additional effort vs. the alternative of defining
a sql statement with the criteria as the record source?

Bob

With a jet (mdb) back end all processing is always local. The server/drive
where the back end file sits is just a hard drive on the end of a really long
wire.

 

[Allen Browne]

The Views and Procedures of the ADOX catalog are nothing more than the
queries you see on the Queries tab of the Database window, i.e. the
QueryDefs of DAO.

Even if you type a SQL statement into the RecordSource of your form, Access
creates a hidden QueryDef for the form, so there is no difference.

If you dynamically assign a SQL statement to the form's RecordSource (say in
Form_Open), Access has a little more work to do in determining a query plan.
In practice, this takes milliseconds, so there is no perceivable difference.

The dynamic SQL statement is so flexible that it makes perfect sense for
many cases. It may even execute faster if building the string dynamically
avoids unweildy WHERE clause phrases such as:
WHERE (([Forms].[Form1].[Text0] Is Null)
OR ([City] = [Forms].[Form1].[Text0]))
AND ...

 

[Albert D. Kallal]

I don't think your are correct.

Note clearly what was said: All processing occurs on he client side, and the
other end is essentially just a disk drive. That statement does not
contradict, or change the fact that JET is smart, and can use a index to
pull ONLY parts of the file. However, Rick is still 100% correct in stating
that all processing STILL occurs on the client side. the other end is ONLY
acting as a disk drive. If access can use a index, then the amount of data
read from the file is reduced (the location of the mdb file being on your
local drive...or down the hall on the network DOES NOT change this
behaviours). The mdb file is still just a plain Jane file that JET reads.

How jet reads this file does not change when you throw in a network. 100% of
the processing of data occurs client side.

So, just because we state that ALL processing occurs on the client side,
that does not imply that all data is transferred. Two very different
issues....

It is a good and correct way to think of the file just sitting on the end of
a long wire to a disk drive....

 

[Rick Brandt]

I don't think your are correct.

There have been many discussion on this topic over the years e.g. this
one:

http://groups.google.com/group/micr...8bf85b8e85/8b2d6c028b45b461?#8b2d6c028b45b461

Its simple conclusions:

"No index means Access must pull all of the data over the network
"Index present means Access can pull only the required data"

Jamie.

The fact that the server's role is merely that of a remote hard drive does not
equate to "all of the data is pulled over". That should be obvious since a
query against a local file doesn't cause "all of the data" to be read either.

My point was that whatever data is retrieved when the table in on the local
drive that SAME data is retrieved when the file is on the LAN somewhere and then
the processing of that data is happening on the local PC.

 

[David W. Fenton]

(e-mail address removed) wrote in

I am using Access 2003 with Jet 4.0. And Jet 4.0 supports Stored
Queries, something I have not paid any attention to until now.

http://msdn2.microsoft.com/en-us/library/aa140021(office.10).aspx

It seems that if I use the ADO syntax correctly, I can define a
stored query, with a parameter, for which I set the parameter
value in VBA (probably a global variable ??) and then use the
query name in the record source. I think that this will give me a
stored procedure on the BE jet4.0 that only passes the selected
records.

None of this is new in Jet 4. What *is* different is how you're
viewing it (through ADO).

But no, these are not server-side views. They are just saved SQL
statements (with saved compilation for the query plan). They don't
get processed server-side, though the saved compiliation may very,
very slightly speed things up (in that Jet doesn't have to compile
the SQL statement before executing it), but not to any degree that's
going to have a significant noticeable effect on performance except
for edge cases (such as very slow networks, where pulling the
metadata to evaluate the SQL adds a significant amount of overhead;
the fewer the records returned, the more likely there is to be a
noticeable effect).

 

[Rick Brandt]

Could you help me figure this one out, please?

Consider a requirement for a validation rule to prevent a row from
being added to a table that doesn't match the 'current' date e.g.

=DATE()

or similar. Obviously I want the 'current' datestamp to be applied
centrally rather than the user's local time according to regional
setting (time zone) and -- I know users don't really do this --
situations where the local machine has had the clock turned back. Is
this doable? TIA.

Jamie.

I do that all the time with server databases. You just ask the database server
to supply the time. There is no way to ask Jet to do this as far as I know.
There is a DOS command that will ask a network server for its current time and
you might be able to redirect the output of that command to a text file and then
read in the file.

I recall that being discussed previously in these forums, but am not familair
with the details.

 

[David W. Fenton]

You just ask the database server
to supply the time. There is no way to ask Jet to do this as far
as I know. There is a DOS command that will ask a network server
for its current time and you might be able to redirect the output
of that command to a text file and then read in the file.

It's much easier to just keep your workstation clocks synchronized
with your domain controller.