Query Slow to Save and Slow to Open

[toilettenseife]

I created a query in Access and it takes FOREVER to open or save. It's like Access is running the query before it opens it or saves it. Any ideas on how to bypass this or speed it up?

 

[toilettenseife]

I created a query in Access and it takes FOREVER to open or save. It's like Access is running the query before it opens it or saves it. Any ideas on how to bypass this or speed it up?

By slow to open, I mean slow to open in design view.

 

[Joe4]

How big is your database?
Does the data reside in an Access table, or is it linked to a data source somewhere else?
How many people are using this database simultaneously?
What exactly does the query do?

 

[toilettenseife]

How big is your database?
Does the data reside in an Access table, or is it linked to a data source somewhere else?
How many people are using this database simultaneously?
What exactly does the query do?

- It's 900MB
- Some data resides in Access and some is linked
- At most, two. On average, one.
- The query selects fields from a pass-through pre-query and joins them with a table that resides in Access. The pass-though query opens in design view instantaneously and saves quickly as well. But when I join it with the Access table it slows down considerably.

 

[Joe4]

This is just a theory, but because it is a pass-through query joining with data that resides in Access, it may be bringing back the data so it can relate the two.
This would seem to mesh with the behavior you described, in that the query is fast when it is the pass-through query by itself.

There are certain things you can do to try to help performance (though I don't know if they will help here):
- Keep your database "lean" by running "Compact and Repair" on it, as needed
- Make sure that all your tables have indexes
- If using a pass-through query, you may want to see if there is anything you can do regarding connection speed to wherever it is the data resides

 

[toilettenseife]

Ok, I'll try those things but I'm not too concerned with the query itself being slow. It's opening the SQL for editing or saving. I don't understand why this takes so long. It's just txt I'm editing. I'm not even running the query. I just want to open it for editing and save it. It's frustrating lol

 

[Joe4]

I seem to remember that being the case too, when I used to work with Pass-Through Queries in Access a few years ago. It was be trying to make the connection, even though you are not running it at that point.

 

[xenou]

That is the typical behavior I have found also. However, you shouldn't need to do that often - once the query is working you shouldn't have to view it in design view.