?
Solved

Access Query taking long to run

Posted on 2009-12-21
12
Medium Priority
?
658 Views
Last Modified: 2013-11-28
I have a split database, backend is on a server.  Tables are linked.  I'm having an issue with one particular query/form.  There are two tables, callserv and mastername.  Each table holds about 50,000 records.  They are joined by callserv.cid -> mastname.nameid.  callserv.cid is a unique value, where as mastname.nameid can have duplicate values.

When running the query by itself it is taking around 10 seconds to run, when I open the form that uses this query, the form takes about 10 seconds to run the first time, however subsequent times the form opens instantly.

Normally I wouldn't mind, but this form is used very heavily and I would like it to open instantly the first time.

I have the query sorting as well, based on two fields (incnoyear and incnoseq) which are numbered fields.  I have tried all sorts of things, even running the query at the beginning of my application, which then results in the form opening right away, however the application seems like it's 'hung' for 10-15 seconds which is unacceptable.

I'm at a loss and was hoping one of you geniuses could help me out.  I could post the code/tables if needed.
0
Comment
Question by:K-9
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 26097457
see sp2 upgrade and other suggestions at this link:
              http://www.granite.ab.ca/access/performancefaq.htm
0
 
LVL 18

Expert Comment

by:Simon
ID: 26097517
Please post the sql statement for the query in question and samples from the tables.

If the running the query takes a minimum of 10 seconds you will not get a form bound to that query to open in less than 10 seconds (unless you run the query at startup and somehow delay/distract the user for minimum of 10 seconds before allowing them to try to open the form).

The answer is to change the query or the form design. Depending on your scenario there are a number of strategies.

Does the query return many records or a single record based on search criteria entered on a different form?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 26098029
Have you tried creating an index on the table that matches the ORDER BY of your SQL Statement?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 44

Expert Comment

by:GRayL
ID: 26098040
Are the tables indexed on the ID fields?  If not you should see quite a difference.
0
 

Author Comment

by:K-9
ID: 26098215
Here is the query (attached as a code snipper):

The indexes on the table are the following:

CallServ:
Incno (PrimaryKey)
IncnoYear (Duplicates OK)
IncnoSeqNumber (Duplicates OK)

MastName:
NameID (Primary Key)

I will attach the tables (with about 100 records each) and the query.  Though with only 100 records i'm sure it'll run alot faster than the 50,000 records.

I am pulling all the data in, because I need it.  This system is used by police departments, the callserv is the main form when a compliantant calls the PD about a situation they need help with.  The CID is linked to the MastName.NameID, in where the MastName is the Master Name record which holds all their personal data (address, phone, ssn, height, weight, etc..).  When they change the CID (from a combo box) on the callserv form, that individual's address,etc.. appears on the callserv.

I have to have the mastname in the query otherwise they can't search on the callserv form, for a person's name, etc..
SELECT CALLSERV.*, MASTNAME.NameID, MASTNAME.FULLNAME, MASTNAME.ADDRESS1, MASTNAME.Address2, MASTNAME.CITY, MASTNAME.STATE, MASTNAME.Zip, MASTNAME.HomePhone, MASTNAME.DOB, MASTNAME.WorkPhone
FROM CALLSERV INNER JOIN MASTNAME ON CALLSERV.CID = MASTNAME.NameID
ORDER BY CALLSERV.IncnoYear, CALLSERV.IncnoSeqNumber;

Open in new window

0
 

Author Comment

by:K-9
ID: 26098269
"If the running the query takes a minimum of 10 seconds you will not get a form bound to that query to open in less than 10 seconds (unless you run the query at startup and somehow delay/distract the user for minimum of 10 seconds before allowing them to try to open the form)."

That's what is weird.  It's almost like access caches data when it's in a form.  If I run the query by itself it takes about 10 seconds each time.  If I open the form the first time, it takes 10 seconds, however subsequent opens on that form produce the data immediately (also I don't see the 'Running Query' progress meter) after the form is opened a second time.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 26098374
You might want to try creating an additional indexon the CALLSERV table that includes the CALLSERV.IncnoYear and CALLSERV.IncnoSeqNumber.  That would, potentially, reduce the need for sorting.  
If I understand correctly, it would appear that you are opening the query without any constraints when you first open the form.   You are initially getting back 50,000 rows.  Do you really need to start with that many?  Or, does your form rovide a means for selecting the CALLSERV row (or, at least, narrowing them down) that is of interest? You might want to have the form open with something selected as a default in whatever fields are used to constrain the search and then open the query with those constraints involved.  
From your original post:
callserv.cid is a unique value, where as mastname.nameid can have duplicate values.

However, based upon your latest post, MastName.NameID is a PrimaryKey . . . which means that the NameID CANNOT have duplicate values.  You have, by definition, a 1-to-1 ratio between the CALLSERV and MASTNAME tables.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 26098401
It's almost like access caches data when it's in a form.  
That may be pretty much what is happening.  You are sucking the entir CALLSERV table (plus the MastName columns) across the network and getting a local copy of it in memory (or, at least, as much as can fit ;-).  
Which version of Access are you using?  
0
 

Author Comment

by:K-9
ID: 26098565
I'm using Access 2007 and it is a one-to-one join.  IncnoYear and IncnoSeq are indexed.  It's basically the CallServ.Incno (eg. 20092832) is split (IncnoYear: 2009), (IncnoSeq: 2832).  I had to do this, because Incno is a text field, which had to be done to accomidate alot of other code (We have about 30,000 lines of code in this application).  IncnoYear and IncnoSeq are Long intergers.

They require all the records that exist to be shown in the callserv form.  They never know when they will need to get to a certain record (this is about 10 years worth of data).  We have reports done such as records checks, etc.. however they are still wanting all the callserv records to show in the form.  I am opening it without any contraints.

Attached is a screenshot of the callserv form so you can get an idea of what it looks like.  I'll be attaching the query/tables soon.
cs.jpg
0
 
LVL 44

Expert Comment

by:GRayL
ID: 26098698
When you maintain linked tables in a mdb they are not populated until they are opened.  Once opened, they remain populated until the mdb is closed.  You could execute some code on opening the mdb that would populate the linked tables, using a splash screeen to 'eat up' the 10 seconds.  Then you should be good to go when the user opens the form.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 26098792
IncnoYear and IncnoSeq are indexed.
I realize that they are each indexed but what I am suggesting is that they be both indexed.  In other words, a new index created that has both columns in it.
They require all the records that exist to be shown in the callserv form.  They never know when they will need to get to a certain record
 What is the probability that the initial record shown is the one they need to access when they initially open the form?  In other words, when they open this form, do they immediately tend to perform a search to find the record they need?  Or do they commonly initially open this form beacues they need to add a new record.  (I'm just trying to figure out if there is a way to, in effect, initially select a subset of the data and still let them find whatever they need to whenever they need to. ;-)
Also, when they click on the "Find" button, what happens?  Are they taken to a form that lets them provide criteria for the search or does it just, somehow, change the functionality of fields on the form?
0
 

Accepted Solution

by:
K-9 earned 0 total points
ID: 26574970
Fixed it by copying the field I used in the mastname into callserv.  I then changed code to update both tables.  It was the only way to increase the speed.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question