Make-table query very slow

[Katy44]

Hi,

I have a query which is incredibly slow. It's a make table query but when it's changed to a select query it runs in <3 seconds (still slow I know, but not half an hour!) Does anyone know why this is happening and how it could be fixed?

Thanks for any help

 

[Trevil]

A little more info would be nice. How complex is the query -- number of tables data is selected from, volume, number of output fields and rows. Across a network? Front-End / Back-End or all in same database? Does it really take 'half an hour'?

As a test, run the query as a select query, then navigate to last record. How long does that take? I have optimized some 2 or 3 minute queries down to a few seconds (there were 20+ tables involved with multiple selection criteria) by splitting into two queries where first only selected records with criteria, second selected all other records.

Code: Where the vision is often rudely introduced to reality!

 

[Katy44]

Thank you for your response.
The query selects from probably about 30 or 40 tables.
There are about 200 fields and the number of rows depends on the parameter values- but it's normally in the thousands.

The select query, as I mentioned takes about three seconds, when it's a make-table it takes 15 minutes (no exaggeration) before it even prompts for the input parameters.

 

[BNPMike]

That is wierd. I can't imagine why there would be the difference.

As a next step I would create the table separately and then turn the query into an update.

Well I suppose firstly I would compact the database just in case it is getting clogged up.

 

[Katy44]

That's not a bad idea - the table structure will always be the same as they normally just select *.
Didn't know you could do that - thanks! I'll see if it's any quicker.