Slow Performance with Access MDB 
Author Message
 Slow Performance with Access MDB

I have an Access database with many linked fairly large tables.  The
linked tables are linked to native Access tables in other mdb's.  The
performance is slow when I do a query against these tables.  We use
DAO.  I don't know what version of Jet I am using.  How would I find
this out and does it matter what version of Jet we are using?  If so,
why?  I know it is an Access97 database.  Can I use the new ADO rather
than DAO to improve performance?  I don't know about ADO yet.  Could you
give me some insight?  Would OLEDB come in to play here?  If so, where
and what is it?  If I rewrote this application in VB 6.0, do you think I
would see performance gains over the Access application.

Thanks,
Keith



Wed, 21 Nov 2001 03:00:00 GMT  
 Slow Performance with Access MDB
Things to I would check...

Compacted/Repaired backends?
Have you properly set/implemented indexes?
Are your N:1 queries setting the criteria on the one side where possible?
Use a query, not SQL?.  If the query plan is already made, it will likely
improve performance.
How much is the network at fault?
Can you remove some of the fields?  avoid SELECT * FROM....
Do you have to do calcuations in the query, or can it be done in the report,
are criteria based on calculated values?
Is there a Group By in the query(Aggregate)?
Can you write the data to a temp table in the front end and run querys off
it instead?

READ Chapters 4 and 13 in MS Jet Database Engine Programmers Guide 2nd

I think syscmd has a option for version...Access97 = Jet 3.5x, 2000=Jet 4.o
I think you are still using Jet in VB...



Quote:
> I have an Access database with many linked fairly large tables.  The
> linked tables are linked to native Access tables in other mdb's.  The
> performance is slow when I do a query against these tables.  We use
> DAO.  I don't know what version of Jet I am using.  How would I find
> this out and does it matter what version of Jet we are using?  If so,
> why?  I know it is an Access97 database.  Can I use the new ADO rather
> than DAO to improve performance?  I don't know about ADO yet.  Could you
> give me some insight?  Would OLEDB come in to play here?  If so, where
> and what is it?  If I rewrote this application in VB 6.0, do you think I
> would see performance gains over the Access application.

> Thanks,
> Keith



Wed, 21 Nov 2001 03:00:00 GMT  
 Slow Performance with Access MDB
Why would a query be faster than SQL?  Does a Query have its SQL in some sort of
pre-compiled state?

Thanks,
Keith

Quote:

> Things to I would check...

> Compacted/Repaired backends?
> Have you properly set/implemented indexes?
> Are your N:1 queries setting the criteria on the one side where possible?
> Use a query, not SQL?.  If the query plan is already made, it will likely
> improve performance.
> How much is the network at fault?
> Can you remove some of the fields?  avoid SELECT * FROM....
> Do you have to do calcuations in the query, or can it be done in the report,
> are criteria based on calculated values?
> Is there a Group By in the query(Aggregate)?
> Can you write the data to a temp table in the front end and run querys off
> it instead?

> READ Chapters 4 and 13 in MS Jet Database Engine Programmers Guide 2nd

> I think syscmd has a option for version...Access97 = Jet 3.5x, 2000=Jet 4.o
> I think you are still using Jet in VB...



> > I have an Access database with many linked fairly large tables.  The
> > linked tables are linked to native Access tables in other mdb's.  The
> > performance is slow when I do a query against these tables.  We use
> > DAO.  I don't know what version of Jet I am using.  How would I find
> > this out and does it matter what version of Jet we are using?  If so,
> > why?  I know it is an Access97 database.  Can I use the new ADO rather
> > than DAO to improve performance?  I don't know about ADO yet.  Could you
> > give me some insight?  Would OLEDB come in to play here?  If so, where
> > and what is it?  If I rewrote this application in VB 6.0, do you think I
> > would see performance gains over the Access application.

> > Thanks,
> > Keith



Wed, 21 Nov 2001 03:00:00 GMT  
 Slow Performance with Access MDB
Yes a query is pre-compiled

Regards, Rob

Quote:

>Why would a query be faster than SQL?  Does a Query have its SQL in some
sort of
>pre-compiled state?

>Thanks,
>Keith


>> Things to I would check...

>> Compacted/Repaired backends?
>> Have you properly set/implemented indexes?
>> Are your N:1 queries setting the criteria on the one side where possible?
>> Use a query, not SQL?.  If the query plan is already made, it will likely
>> improve performance.
>> How much is the network at fault?
>> Can you remove some of the fields?  avoid SELECT * FROM....
>> Do you have to do calcuations in the query, or can it be done in the
report,
>> are criteria based on calculated values?
>> Is there a Group By in the query(Aggregate)?
>> Can you write the data to a temp table in the front end and run querys
off
>> it instead?

>> READ Chapters 4 and 13 in MS Jet Database Engine Programmers Guide 2nd

>> I think syscmd has a option for version...Access97 = Jet 3.5x, 2000=Jet
4.o
>> I think you are still using Jet in VB...



>> > I have an Access database with many linked fairly large tables.  The
>> > linked tables are linked to native Access tables in other mdb's.  The
>> > performance is slow when I do a query against these tables.  We use
>> > DAO.  I don't know what version of Jet I am using.  How would I find
>> > this out and does it matter what version of Jet we are using?  If so,
>> > why?  I know it is an Access97 database.  Can I use the new ADO rather
>> > than DAO to improve performance?  I don't know about ADO yet.  Could
you
>> > give me some insight?  Would OLEDB come in to play here?  If so, where
>> > and what is it?  If I rewrote this application in VB 6.0, do you think
I
>> > would see performance gains over the Access application.

>> > Thanks,
>> > Keith



Thu, 22 Nov 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Slow Performance with Access MDB

2. Slow Performance with Access MDB

3. very slow performance while accessing Web Service from client runnig Win98

4. very slow performance while accessing Web Service from client Computer

5. Access drastically slowed by Norton Anti-Virus 5 testing of .mdb files

6. Accessing .MDB file from remote file server thru slow LAN

7. TransferDatabase method from Access 95 mdb to Access 97 mdb

8. Slow Performance and DLLs

9. incredibly (!) slow performance for simplest operation

10. CreateObject seams to be slow, any ways to improve performance

11. PrivateProfileString - Slow performance

12. Very Slow Word Xp Performance

 

 
Powered by phpBB® Forum Software