Slow query - How to speed up?
The table does have 945k records in it and is housed in a back end DB. I realize that is a lot of records, but this seems to be running abnormally slow.
Here is why I ask.I have a form that provides a search function which is based on this query. The query is not called upon until the user needs to search for an employee.
However, for some reason unknown to me the query runs every time the form is opened. I DO NOT have the query running at the open/load of the form. The only time the query is to run is when the user clicks on a search button.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
When I query the data, particularly when I join two large tables, or even one small table with one large table, Access can take up to several minutes to process the query.
I don't understand why Access is so slow in processing the query when the tables are linked; I thought linking tables would reduce network traffic and speed database performance.
Could someone please provide suggestions for how I may be able to speed up the performance of my queries?
The database itself is only 1.56 MB in size (it’s only a test database as I’m in the early stages of development. But if the database is this slow not, I can only imagine when I start adding forms and reports).
When I execute the query, it takes less than a minute to run.
If I create a "make table query" with the same, I.e. I just want to store the results of the query in a permanent table, it takes forever (been running for 30 mins, looks like Access is frozen).
I've indexed most of the join fields to speed up the query.
What can I try to speed it up
Multiple Windows 7 workstations are all slow and all Windows XP machines run the query quickly.
I even used JetShowPlan on both workstations to compare and the plans are identical. Two tables are involved with rushmore used on the first and a full scan on the second table of about 150,000 records.
Even in XP Mode on the Windows 7 machine the query still takes only about 10 seconds. Anyone have any ideas why Access/Jet on Windows 7 can be so slow?
The queries take around 2.5 minutes to open! This is too slow.
I have imported the tables into the DB and the same Queries run on imported (not linked) tables take around 5 seconds to open. So huge difference.
The Problem is that those Tables are in the other DB's for a reason and they need to stay there.
Is there anything I can do to improve speed of queries that are based on Linked Tables from other Databases?
it talks about setting the Subdatasheet Name property to [NONE].
After splitting the database, my passthrough query has become sluggish and slow where my users are not very happy.
When I run a test prior to splitting my database, it would take about 1-5 seconds to retrieve the records using a passthrough.
But after splitting, now that passthrough query will take anywhere from 3 minutes - 10 minutes.
In my sub CompanyHistoryByPeriodTransactions, I added a couple of message boxes to gauge how long it took to/from the passthrough query. This is where I see my slow down occurring.
The JET engine uses the Windows/TEMP directory to process these queries in.
I would like to force access to use a temp directory of my choosing on the high speed drive to speed up the process and cut down on disk i/o. I tried changing the User environment temp directory to the high speed drive but Access is still using the system temp folder on the low speed drive.
I don't want to move the Windows/Temp folder to the high speed drive because Idon't want to fill that drive with other stuff Windows jams in there.
How can I force Access to use the a temp directory on the high speed drive and leave the windows/temp folder on the system drive?
I have created an indexed field . Still execution is slow.