VERY Slow Table Creation

[Guest]

I have a daily query that pulls a large amount of data (800k+ records) from a
badly engineered Oracle Database. The query itself is pulling data from 3
different tables and is filtering for the current year only. When I run the
query as a Select Query, the results 'pop' in about 30 seconds. When I run
the query as a Make Table Query, it will take anywhere from 6 to 10 minutes.
As this query gets rebuilt every day, is there a way to speed up this process?

Thanks.

 

[Guest]

Others may have a better opinion on this, I've worked with large datasets
before in making a table, and it just takes a long time. One project had as
many or slightly more records than your example and took as long as 25
minutes to create a table. I think you may have to deal with the delay and
schedule lunch or coffee around it! That's what I told my client to do.

John

 

[Rick Brandt]

I have a daily query that pulls a large amount of data (800k+
records) from a badly engineered Oracle Database. The query itself is
pulling data from 3 different tables and is filtering for the current
year only. When I run the query as a Select Query, the results 'pop'
in about 30 seconds. When I run the query as a Make Table Query, it
will take anywhere from 6 to 10 minutes. As this query gets rebuilt
every day, is there a way to speed up this process?

Thanks.

When you view it as a SELECT query you are seeing results on the screen when
Access has really only processed a few data pages of the total. The
MakeTable query has to process ALL of the rows before it reports as
finished.

 

[John Vinson]

I have a daily query that pulls a large amount of data (800k+ records) from a
badly engineered Oracle Database. The query itself is pulling data from 3
different tables and is filtering for the current year only. When I run the
query as a Select Query, the results 'pop' in about 30 seconds. When I run
the query as a Make Table Query, it will take anywhere from 6 to 10 minutes.
As this query gets rebuilt every day, is there a way to speed up this process?

Thanks.

It may or may not help, but I'd suggest having your local table stored
permanently. Rather than deleting the query and then running a
make-table, simply run an unconditional delete query to empty it and
run an Append query to populate it from your Oracle tables.

John W. Vinson[MVP]