SELECT query vs MakeTable query - performance question

M

macroapa

Hi, I have a select query that takes about 5 seconds to run. If I
take the same query and turn it into a make table query, it takes
about 15 mins to run. I'm almost at the stage of running a SELECT
query and copying the data and pasting into the table. Is there
anything I can do to increase performance of the make table query?

=============================================================================
SELECT sql

SELECT tblaaTRTvsExtTRT.workitemid, tblaaTRTvsExtTRT.stepname,
tblaaTRTvsExtTRT.workitemstepid, tblaaTRTvsExtTRT.Available,
tblaaTRTvsExtTRT.Worked, tblaaTRTvsExtTRT.StepStarted,
tblaaTRTvsExtTRT.TouchStopped, qryTrueStepStop.TrueStepStop,
tblaaTRTvsExtTRT.workitemdate, tblaaTRTvsExtTRT.status_desc,
tblaaTRTvsExtTRT.wistopdate, workdaysdiff([available],[worked]) AS
aaTRT, workdaysdiff([stepstarted],[truestepstop]) AS wholeTRT
FROM tblaaTRTvsExtTRT INNER JOIN qryTrueStepStop ON
tblaaTRTvsExtTRT.workitemstepid = qryTrueStepStop.workitemstepid;


=============================================================================
Make table sql

SELECT tblaaTRTvsExtTRT.workitemid, tblaaTRTvsExtTRT.stepname,
tblaxTRTvsExtTRT.workitemstepid, tblaaTRTvsExtTRT.Available,
tblaaTRTvsExtTRT.Worked, tblaaTRTvsExtTRT.StepStarted,
tblaaTRTvsExtTRT.TouchStopped, qryTrueStepStop.TrueStepStop,
tblaaTRTvsExtTRT.workitemdate, tblaaTRTvsExtTRT.status_desc,
tblaaTRTvsExtTRT.wistopdate, workdaysdiff([available],[worked]) AS
aaTRT, workdaysdiff([stepstarted],[truestepstop]) AS wholeTRT INTO
tblaaTRTandWholeTRT
FROM tblaaTRTvsExtTRT INNER JOIN qryTrueStepStop ON
tblaaTRTvsExtTRT.workitemstepid = qryTrueStepStop.workitemstepid;
 
D

Dirk Goldgar

macroapa said:
Hi, I have a select query that takes about 5 seconds to run. If I
take the same query and turn it into a make table query, it takes
about 15 mins to run. I'm almost at the stage of running a SELECT
query and copying the data and pasting into the table. Is there
anything I can do to increase performance of the make table query?

=============================================================================
SELECT sql

SELECT tblaaTRTvsExtTRT.workitemid, tblaaTRTvsExtTRT.stepname,
tblaaTRTvsExtTRT.workitemstepid, tblaaTRTvsExtTRT.Available,
tblaaTRTvsExtTRT.Worked, tblaaTRTvsExtTRT.StepStarted,
tblaaTRTvsExtTRT.TouchStopped, qryTrueStepStop.TrueStepStop,
tblaaTRTvsExtTRT.workitemdate, tblaaTRTvsExtTRT.status_desc,
tblaaTRTvsExtTRT.wistopdate, workdaysdiff([available],[worked]) AS
aaTRT, workdaysdiff([stepstarted],[truestepstop]) AS wholeTRT
FROM tblaaTRTvsExtTRT INNER JOIN qryTrueStepStop ON
tblaaTRTvsExtTRT.workitemstepid = qryTrueStepStop.workitemstepid;


=============================================================================
Make table sql

SELECT tblaaTRTvsExtTRT.workitemid, tblaaTRTvsExtTRT.stepname,
tblaxTRTvsExtTRT.workitemstepid, tblaaTRTvsExtTRT.Available,
tblaaTRTvsExtTRT.Worked, tblaaTRTvsExtTRT.StepStarted,
tblaaTRTvsExtTRT.TouchStopped, qryTrueStepStop.TrueStepStop,
tblaaTRTvsExtTRT.workitemdate, tblaaTRTvsExtTRT.status_desc,
tblaaTRTvsExtTRT.wistopdate, workdaysdiff([available],[worked]) AS
aaTRT, workdaysdiff([stepstarted],[truestepstop]) AS wholeTRT INTO
tblaaTRTandWholeTRT
FROM tblaaTRTvsExtTRT INNER JOIN qryTrueStepStop ON
tblaaTRTvsExtTRT.workitemstepid = qryTrueStepStop.workitemstepid;
Click to expand...


How many records are you dealing with?

I suspect that the SELECT query takes longer to run than you think; or
rather, that it takes longer to run and return all records *including the
calculated values aaTRT and wholeTRT* than you think. Access optimizes
queries for display by only calling VBA functions when the record is
accessed. You may be able to see, as you page through the query results,
that there is a slight delay before the values for those calculated fields
are displayed.

Also, if you open a SELECT query as a datasheet, it returns the first page
of records as quickly as possible, while continuing to retrieve records
behind the scenes. So the query appears to complete quickly, but for
long-running queries that is an illusion.

I could be wrong about this, and it could be that there is really such an
enormous disparity between the select query and the make-table query, but I
don't see why. You might check it by opening a recordset on the query and
looping through the records, using Debug.Print to print the calculated
fields in each record to the Immediate window. My guess is that it will
take a lot longer than 5 seconds.
 
J

John W. Vinson

Hi, I have a select query that takes about 5 seconds to run. If I
take the same query and turn it into a make table query, it takes
about 15 mins to run. I'm almost at the stage of running a SELECT
query and copying the data and pasting into the table. Is there
anything I can do to increase performance of the make table query?
Click to expand...

As Dirk says, the five seconds is probably deceptive. Try opening it as a
query datasheet and immediately hitting the >> navigation button to go to the
last record.

That said, a MakeTable query will ALWAYS be slower - often a lot slower - than
a select query. It must do everything that the select query does, plus
allocate disk space for the nee records, move the data into the new records,
create and update indexes if you have autoindexing turned on...

It's worth considering that MakeTable queries are only rarely actually either
necessary or the best way to do things. What benifit do you get from creating
a new table to contain data which you already have in your database?
 
Top