Jet Performance Question

Jet Performance Question

Post by don » Sat, 26 Sep 1998 04:00:00



I have a performance question regarding the Jet Engine supplied with VB.
For performance reasons I would like to open a query when the program loads
that serves as a sub query for several queries that are run by the
application or users.  Basically the sub query functions like a View,
bringing together relevent data for groups of users.  

My question is:  if a user invokes a query using the loaded query as a
sub-query, does the jet use the query recordset already loaded in memory or
does it load another recordset object?
eg
qryView_Query recordset loaded at program start.
the app, or the user then invokes a new recordset object based on the SQL
statement: SELECT * FROM qryView_Query WHERE Emp_ID = xx;, is a new
qryView_Query recordset object loaded or is the previously loaded recordset
object used?

Thanks
Don

 
 
 

Jet Performance Question

Post by Glen Kayn » Sat, 26 Sep 1998 04:00:00



>I have a performance question regarding the Jet Engine supplied with VB.
>For performance reasons I would like to open a query when the program loads
>that serves as a sub query for several queries that are run by the
>application or users.  Basically the sub query functions like a View,
>bringing together relevent data for groups of users.

>My question is:  if a user invokes a query using the loaded query as a
>sub-query, does the jet use the query recordset already loaded in memory or
>does it load another recordset object?
>eg
>qryView_Query recordset loaded at program start.
>the app, or the user then invokes a new recordset object based on the SQL
>statement: SELECT * FROM qryView_Query WHERE Emp_ID = xx;, is a new
>qryView_Query recordset object loaded or is the previously loaded recordset
>object used?

>Thanks
>Don

I believe the original is used.

I also have a related question concerning performance and speed.

I read that using saved queries is faster that creating the SQL statements
in code.  As a result, I use Access to design my queries and load them using
CreateQueryDef from VB.  In many cases I display the resulting recordset
along with summary information.  Would it be faster to create the query for
the summary information with an SQL which queries the loaded recordset or
load a saved query which provides the summary information.

 
 
 

Jet Performance Question

Post by Bill Slate » Sun, 27 Sep 1998 04:00:00


Saved queries in Access are "precompiled" and that is why they are faster. If
you create a query on the fly with SQL it will have to be compiled the first
time it is run, negating any speed improvement.

Quote:> I also have a related question concerning performance and speed.

> I read that using saved queries is faster that creating the SQL statements
> in code.  As a result, I use Access to design my queries and load them using
> CreateQueryDef from VB.  In many cases I display the resulting recordset
> along with summary information.  Would it be faster to create the query for
> the summary information with an SQL which queries the loaded recordset or
> load a saved query which provides the summary information.

  vcard.vcf
< 1K Download
 
 
 

1. Jet DB Performance (Jet 3.51 vs. Jet 4.0)

I'm evaluating the query performance against Jet DB.

I have compared two different DB environments:

1) Jet 3.51 Driver against Access database (either 97 or 2000)
2) Jet 4.0 Driver against Access database (either 97 or 2000)

I noticed that the DB performance for Jet 4.0 Engine was much slower
than for Jet 3.51 Engine (i.e. Jet 4.0 - 40 seconds while Jet 3.51 - 11
seconds). The DB performance against Access 97 or Access 2000 didn't
make any difference. Only the Jet Engine caused the performance
differences.

Do you know why Jet 4.0 is slower? What should I do in optimizing DB
performance?

Thank you, Jake

2. Threads and Cursors in Pro*C!!!!!

3. ADO Jet 4.0 performance problem vs Jet 3.51

4. saving data into exe's possible?

5. Jet Engine performance question

6. Spaces between fields in Visual Fox LIST reports?

7. New JET Engine PERFORMANCE MONITOR and TRACE utility!!!

8. VB5 performance to JET database is awful!

9. bizarre difference between performance of first and second run of ODBC/Jet writer application

10. MS jet 4.0 ADO trouble & performance

11. slow ADO performance with Jet 4.0

12. Jet ADO threads and performance