database speed is getting an issue

[petko]

Hi, I have a quite complex database for running my company. Tables are on the server, five users are linked to this, using different modules like warehouse, bookkeeping, service etc. I developed this through years for covering the company's need, however the speed is getting critical. I'm trying to decide which way to go to improve it but I need help from more experienced fellows. I would definitely keep the modules as they required tremendous development time.
I appreciate any hints, ideas.
Peter

 

[Galaxiom]

You haven't given us much to go on.

Have you got indexes on the fields that are used in queries?

Are your RecordSource queries only returning the minimum data required?

Are your queries constructed for speed? One common problem is applying functions to records before selecting them.

 

[Simon_MT]

First establish where it is going slow - generally or particular parts of the database.

Also check:

BE - FE deployment
Persistency - a file always open in the FE connected to the BE
Access 2007 - Table Subdatasheet = Null
Indexing Foreign Table descriptors

Simon

 

[petko]

You haven't given us much to go on.

Have you got indexes on the fields that are used in queries?

Are your RecordSource queries only returning the minimum data required?

Are your queries constructed for speed? One common problem is applying functions to records before selecting them.

Dear Galaxiom,

- I use indexes only that are set by Access automatically. Actually I don't know what indexes are for and how I'm supposed to set them. Is this okay?
- Please specify a bit what you mean by queries returning the minimum data required. What I have noticed is that particularly slowing down happens when I define a record set from FE, filtered out from a queries or tables. How could I make queries with minimum data output?
- I think this is the point: my queries are not fast; they are rather very slow. How can I make them faster? Typically I do not apply functions but just very seldom

Thanks for your further hints, I really appreciate them!

Peter

 

[petko]

First establish where it is going slow - generally or particular parts of the database.

Also check:

BE - FE deployment
Persistency - a file always open in the FE connected to the BE
Access 2007 - Table Subdatasheet = Null
Indexing Foreign Table descriptors

Simon

Dear Simon,

Thanks a lot for your feedback.
Most of your terms are not really clear.
Particularly slowing down happens when I set a record set from a FE form. For example these lines down here take minutes to perform:

Set dbs = CurrentDb

 feltNaptar = "Select * From AlapNaptar Where Year(AlapNap) = " & Me.Ev & " AND Month(AlapNap)= " & Me.Honap
 
        Set rstNaptar = dbs.openrecordset(feltNaptar, , False)
         
         With rstNaptar
             
             .MoveFirst
             
             Do While Not .EOF
                 
                 .edit
                 
                 If DCount("KiszállásDátum", "BerszamfejtesSegedAlapnaptarhoz", "CLng(KiszállásDátum) = " & _
                     CLng(rstNaptar("Alapnap")) & " AND Szerelö = '" & NevBekero & "'") > 0 Then
                 
    feltMunkaido = "Select * From BerszamfejtesSegedAlapnaptarhoz Where CLng(KiszállásDátum) = " & _
                         CLng(rstNaptar("Alapnap")) & " AND Szerelö = '" & NevBekero & "' ORDER BY MunkaidöID"
                     
                     Set rstMunkaido = dbs.openrecordset(feltMunkaido, , False)
                     
                         With rstMunkaido
                             
                             .MoveFirst
                            ...

Please specify what you mean by BE - FE deployment
Persistency - a file always open in the FE connected to the BE: I run one form cosntantly from FE, connected to BE on purpose. This has one boolean only. (few years ago this was a suggestion for faster performance.) Could this cause any problem?
These terms I can't really understand: please give me some input data how I can check these:
Access 2007 - Table Subdatasheet = Null
Indexing Foreign Table descriptors

Appreciate your patient and help

Peter

 

[Galaxiom]

An index allows the engine to determine which records match the Where clause criteria without actually getting the rows and reading them. They should be used on any field that is used as criteria in a query. (Same if a domain function is used against them.) They make a vast difference to the speed.

The down side of an index is the extra time it takes to rebuild the index during inserts and updates so they shoul dnot be used unnecessarily.

A typical example of a function being applied to every record would be:

WHERE Year(datefield) = 2013

which can be literally hundreds of times slower than:

WHERE datefield BETWEEN #1/1/2013# AND #12/1/2013#

The second clause allows the engine to use the index. The first one must apply the function every record.

Applying a filter after loading the entire table is much slower than including the filter as a Where clause in the original recordsource of a form or report. So if possible, give the user a form to define the criteria before the form displaying the results is opened and pass the criteria as the Where clause argument in the OpenForm method.

 

[petko]

Dear Galaxiom,

Your explonation on index helped a lot! Actually the speed of the system became brilliant by using indexes. Obviusly it needs some experience where to use and where not but it is obviously a key thing in improving performance.

Thanks a lot!