'Make-Table Query' - speed issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm running a make table query (1) which is based on a query (2) which does various bits of calculations and analysis
Query (2) takes about 10 seconds to run... not lightning, but fast enough

Query (1) which does no analysis except only display relevant fields and change the field names into something more user friendly... takes about 1/2 an hour (or longer!

There are 71,000 records (rows) in each and 84 fields (columns) in Query 1 and 39 fields (columns) in Query 2

I'm running Windows XP, Access 2000 and the file size is about 200 mb and I regularly compact and repair the database to remove bloating associated with the creation and deleting of tables. I note that in task manager, Access does not exceed 50% of CPU Usage (it sits on about 50% throughout the processing of this query)

Why would this be happening
What can I do to speed this up
Is there alternatives
Any ideas

Thanks
Marcu
 
Marcus,

You might get a better response if you posted the SQL for
your two queries. One thing though that comes to mind,
is that while query 2 takes only 10 seconds or so to
display results, you're just getting a small portion of
the records, not the entire dataset. For example, it
pulls 256 records, enough to fill a few screens, but it
hasn't actually run through the entire data. Try,
running your query 2 and moving to the bottom of the
records, it probably will take longer than 10 seconds.

Access isn't optimized for Not Like or Not In's so if
your queries include those, it takes a long time to run.

Mark
-----Original Message-----
Hi,

I'm running a make table query (1) which is based on a
Click to expand...
query (2) which does various bits of calculations and
analysis.
Query (2) takes about 10 seconds to run... not lightning, but fast enough.

Query (1) which does no analysis except only display
Click to expand...
relevant fields and change the field names into something
more user friendly... takes about 1/2 an hour (or longer!)
There are 71,000 records (rows) in each and 84 fields
Click to expand...
(columns) in Query 1 and 39 fields (columns) in Query 2.
I'm running Windows XP, Access 2000 and the file size is
Click to expand...
about 200 mb and I regularly compact and repair the
database to remove bloating associated with the creation
and deleting of tables. I note that in task manager,
Access does not exceed 50% of CPU Usage (it sits on about
50% throughout the processing of this query).
 
Back
Top