Access Query taking long to run

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.
K-9Asked:
Who is Participating?
 
K-9Connect With a Mentor Author Commented:
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
 
puppydogbuddyCommented:
see sp2 upgrade and other suggestions at this link:
              http://www.granite.ab.ca/access/performancefaq.htm
0
 
SimonCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
8080_DiverCommented:
Have you tried creating an index on the table that matches the ORDER BY of your SQL Statement?
0
 
GRayLCommented:
Are the tables indexed on the ID fields?  If not you should see quite a difference.
0
 
K-9Author Commented:
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
 
K-9Author Commented:
"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
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
K-9Author Commented:
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
 
GRayLCommented:
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
 
8080_DiverCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.