Make-Table Query much faster in a macro...Why?

[sgidley]

We have a Database with a lot of tables and relationships. Queries that call other queries. The top level query that the user uses was taking 5-8 seconds. To get rid of this delay, we decided to do a daily make-table query, get all of the data in one large table and index it. So yes now all of the data is duplicated but the users have 0 wait time with their queries so everybody is happy.

Where it starts to get weird is... when I first set up this Make-Table query, all I did was take the user's top level query, copy it and change it to a Make-Table query. This Make-Table query, when ran in the Access UI, would take 15 minutes to run. I could never figure out why, since its only 20,000 records, allthough the end result is about 120 fields. Still, the Select query takes 5-8 seconds, so why 15 minutes for a Make-Table.

Where it really gets weird is...I wanted to automate this process so that this 15 minute query could be run at night, so I put it in a macro. All the macro does is open the very make-table query that I made above, with the UI and all, but it only takes 15 seconds instead of 15 minutes now. And guess what, if you go back to the Access UI and run the Make-table query from there, it only takes 15 seconds again. I can no longer make it take 15 minutes like it did originally. I deleted the tables just to be sure its actually doing its thing but sure enough, it re-creates them and all the data is there - 50 times faster.

So I'm obviously not complaining, this is what I wanted from the beginning, but does anybody have any ideas why? It seems pretty detrimental if other people's Make-Table queries go so slow if they don't really need to.

My Macro:
Set Warnings - Warnings On=NO
OpenQuery - Query Name = <The make-table query I made in the UI>; View = Datasheet; Data Mode=Edit

In case it's relevant:
The make-table query actually creates a table in another database, and the other database is on the Network.

 

[sgidley]

Ok, I stand Corrected, my apologies. I thought I had tried this several times before and ruled out the following, but now it doesn't appear to be the case:

It still takes a very long time the FIRST time the make-table query is done. Then every one after that is very fast AS LONG AS I DON'T COMPACT/REPAIR the database (2003). I doubt that this is macro related like I thought. Looks like the db is optimizing the query and saving the optimization information, but that info gets wiped out if the database is compacted. Bummer since we have to do that often because we are often importing and replacing tables in the DB and it gets large very fast.

 

[fneily]

You're correct in that when a query is first ran, Access optimizes it and then uses this compiled version for future use. If you make any changes to the query, then the process is repeated.
Also, when you do make-table queries, it increases the size of the database. Maybe just make one table and then delete the table's data first and reuse the table.
You're also "importing and replacing" tables, again increasing the size of the database.
Then, of course, there's the network consideration.

All this make table stuff, running slow, is your tables normalized? Interesting to see your table structures, eg.
tblCustomers
CustID
Fname
Lname
etc.

 

[MasterRacker]

I second fneily. Whenever I need scratch tables, I clear the data and reload them.

When you think it from a high level, it doesn't make much sense to delete a table definition only to recreate the exact same definition immediately afterward.

I also agree the speed is suspicious. Unless your hardware is seriously underpowered it shouldn't take 15 minutes to create a table and dump a few thousand records into it.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[BNPMike]

I doubt its the optimiser. That wouldn't take trillions of cycles. I would guess it's cache. Switch you pc off and on, then re-run the optimised query. I would guess it will run slowly again for the first run.

A couple of weeks ago I was involved in a high-load banking application (SQL Server as it happens). It was performance tested but when it went live it started to slow down. It turned out it was running a query that returned hundreds of thousands of big records when really it was targeting only one or two. The huge cpu power and real memory masked this until the bank branches started building up work.

I have seen Access queries that are mind bogglingly inefficient but run in a second. But it's an illusion - excess memory is disguising the underlying threat.

I too think your load must be wierd to take so long.

 

[sgidley]

Hmm. Thanks for the insight Mike. What do you mean by "Your load must be wierd to take so long" Inefficent queries?