When query is first opened it takes 30 seconds. Afterwards it is very fast.
My database had a form that ran VERY slowly. Each browse request took 30 seconds.
I have an ugly fix, and I am hoping someone can give me a cleaner solution.
I cut and pasted the sql from the form's record source into a new query named NewQuery
NewQuery's syntax is
Select [tblSource].[Field1]
from tblSource where field2 = 1234 and field3 = 4567 and field4 > 8910.
Whenever NewQuery is run immediately after opening the database it takes 30 seconds. If I close the query and reopen it, it runs instantly.
So, I fixed my database form by always running NewQuery at database open, and I LEAVE THE QUERY OPEN FOR THE WHOLE SESSION.
W o n d e r f u l results !! The form now browses in less than 2 seconds.
Everybody is happy except that now it takes 30 seconds just to open the database.
By the way, the constants that I embedded in NewQuery are almost arbitrary. The form can quickly browse to ANY records in tblSource, not just the records returned by NewQuery.
One theory is that MS Access builds a temporary index the first time NewQuery is run, then leaves that index in place until the database is closed.
So, if that is true, I should be able to eliminate the need for NewQuery by permanently adding the "correct" index to tblSource.
But, I can't figure out what the "correct" index would be.
I tried making tblSource primary key be (field2, field3, field4, field1) then compacted database, but that did not accomplish anything -- I still need to run NewQuery.
I could try all 12 different permutations, but I am hoping somebody can save me that grief.
Does anybody know how to find out what MS Access is using for its temporary index?
I have an ugly fix, and I am hoping someone can give me a cleaner solution.
I cut and pasted the sql from the form's record source into a new query named NewQuery
NewQuery's syntax is
Select [tblSource].[Field1]
from tblSource where field2 = 1234 and field3 = 4567 and field4 > 8910.
Whenever NewQuery is run immediately after opening the database it takes 30 seconds. If I close the query and reopen it, it runs instantly.
So, I fixed my database form by always running NewQuery at database open, and I LEAVE THE QUERY OPEN FOR THE WHOLE SESSION.
W o n d e r f u l results !! The form now browses in less than 2 seconds.
Everybody is happy except that now it takes 30 seconds just to open the database.
By the way, the constants that I embedded in NewQuery are almost arbitrary. The form can quickly browse to ANY records in tblSource, not just the records returned by NewQuery.
One theory is that MS Access builds a temporary index the first time NewQuery is run, then leaves that index in place until the database is closed.
So, if that is true, I should be able to eliminate the need for NewQuery by permanently adding the "correct" index to tblSource.
But, I can't figure out what the "correct" index would be.
I tried making tblSource primary key be (field2, field3, field4, field1) then compacted database, but that did not accomplish anything -- I still need to run NewQuery.
I could try all 12 different permutations, but I am hoping somebody can save me that grief.
Does anybody know how to find out what MS Access is using for its temporary index?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Without the where clause, any delay in running the query after opening the database?
ASKER
No, dropping the where clause makes NewQuery open quickly. BUT, the problem form returns to its very slow behavior.
In fact, if I drop any 1 of the 3 predicate clauses, the behavior becomes slow.
In fact, if I drop any 1 of the 3 predicate clauses, the behavior becomes slow.
ASKER
I am no longer sure if I am correct in thinking that things are slow because of a temporary index being built.
Every attempt I made to add extra indexes failed:
I even took every permutation of the 3 search fields in NewQuery:
field1, field2, field3 field1, field3, field2 etc etc all 6 permutations.
Also, it appears the network is causing some of the problem. TblSource is in a back end database which is on an SBS2003 network drive.
When I copy the back end to my C: drive and relinked to it, I no longer need the run NewQuery -- things work fine without it.
Every attempt I made to add extra indexes failed:
I even took every permutation of the 3 search fields in NewQuery:
field1, field2, field3 field1, field3, field2 etc etc all 6 permutations.
Also, it appears the network is causing some of the problem. TblSource is in a back end database which is on an SBS2003 network drive.
When I copy the back end to my C: drive and relinked to it, I no longer need the run NewQuery -- things work fine without it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am making some progress.
1) I am now 95% sure that compact and repair does NOT resequence my large table. I suspect the original table was entry sequence, and I suspect entry sequence, whereas the table would work a lot faster if it was key sequence.
2) If I create a tblSourceNew table copying the structure then appending all records, the new table performs extremely well in NewQuery -- there is no 20 second delay.
3) Also, I noticed an error in my orginal post. Sorry for the error.
NewQuery's ACTUAL syntax is
Select [tblSource].[Field1]
from tblSmall inner join tblSource ON tblsmall.field5 = tblSourceField5 where field2 = 1234 and field3 = 4567 and field4 > 8910.
That helps explains why adding indexes to tblSource did not work. Whatever the Access is doing the first time NewQuery runs is related to TWO tables, not just one.
I am getting a headache on this problem, plus there is other work that is piling up. I am going to have to abandon this for a few days.
rberke
1) I am now 95% sure that compact and repair does NOT resequence my large table. I suspect the original table was entry sequence, and I suspect entry sequence, whereas the table would work a lot faster if it was key sequence.
2) If I create a tblSourceNew table copying the structure then appending all records, the new table performs extremely well in NewQuery -- there is no 20 second delay.
3) Also, I noticed an error in my orginal post. Sorry for the error.
NewQuery's ACTUAL syntax is
Select [tblSource].[Field1]
from tblSmall inner join tblSource ON tblsmall.field5 = tblSourceField5 where field2 = 1234 and field3 = 4567 and field4 > 8910.
That helps explains why adding indexes to tblSource did not work. Whatever the Access is doing the first time NewQuery runs is related to TWO tables, not just one.
I am getting a headache on this problem, plus there is other work that is piling up. I am going to have to abandon this for a few days.
rberke
ASKER
I am closing this problem. my "solution" was to remove referential integrity from a relation,
AND to put a dummy query into my database open routine.
I wish I had more time to document details, but I have wasted too much time on this crud already.
For anybody who wants to see some of my false starts, here is a summary of the related problems.
When query is first opened it takes 30 seconds. Afterwards it is very fast.
https://www.experts-exchange.com/questions/25860761/When-query-is-first-opened-it-takes-30-seconds-Afterwards-it-is-very-fast.html
How to reoganize all tables sequencing them by primary key?
https://www.experts-exchange.com/questions/25877487/How-to-reoganize-all-tables-sequencing-them-by-primary-key.html
referential integrity makes my query run 30 times longer.
https://www.experts-exchange.com/questions/25959786/referential-integrity-makes-my-query-run-30-times-longer.html
AND to put a dummy query into my database open routine.
I wish I had more time to document details, but I have wasted too much time on this crud already.
For anybody who wants to see some of my false starts, here is a summary of the related problems.
When query is first opened it takes 30 seconds. Afterwards it is very fast.
https://www.experts-exchange.com/questions/25860761/When-query-is-first-opened-it-takes-30-seconds-Afterwards-it-is-very-fast.html
How to reoganize all tables sequencing them by primary key?
https://www.experts-exchange.com/questions/25877487/How-to-reoganize-all-tables-sequencing-them-by-primary-key.html
referential integrity makes my query run 30 times longer.
https://www.experts-exchange.com/questions/25959786/referential-integrity-makes-my-query-run-30-times-longer.html
"this is why MS Access is a limited product and not used as a major database technologies "
Limited? Absolutely false.
mx
Limited? Absolutely false.
mx
ASKER
There were no suggestions for either.
Bob