PERFORMANCE: make table query VS create table statment

[Tazcat]

I have a situation where I need to update an archive table several times through out the day. To do this, I need to generate some temp tables to populate aggregate data to the archive.

Basically, the user enters a bunch of new records, or alternatively, closes a bunch of existing records as a batch. At the end of the transaction, the aggregate data is updated to the archive. This obviously causes a noticeable lag from the time the user clicks DONE to the time the tables update, and the form closes.

Currently, I am doing this through stored procedures (Make Table and Update queries). Temp tables are a must, as there are too many "Operation must use updateable query" errors with Union and Summary queries to do this any other way.

SO, the question is this: will replacing the make table and update queries with CREATE TABLE and UPDATE statements improve performance? Can the time lag be 'less noticeable'?

Discussion?

Thanks,
Melanie

 

[rac2]

No.

CREATE TABLE and UPDATE are the SQL language statements you are creating when you use Acccess Query Designer to define Make Table and Update queries. You can see these by selecting SQL View when you are in Query Design mode. There is an icon on the top tool bar which has three options Design View, Datasheet View, and SQL View.

Design View is the default when you start Queries->Design or Queries->New->Design View. After the query design grid is displayed the icon to swith between the three views will appear in the tool bar. Design View and SQL View are just different ways of looking at a query; Datasheet View actually executes the query and presents the data in a grid.



"This obviously causes a noticeable lag from the time the user clicks DONE to the time the tables update, and the form closes."

Yes, lightening fast as they are, computers do take a little time to do their work. Possibly you should cut back on the caffeine and slow down to computer speed. ;-)

How much time are we talking about here, a lag of 2 seconds or 2 minutes?

 

[Tazcat]

Thanks rac2. Perhaps I should have been a bit more specific regarding what I need to know. I should have said that I am referring to how to execute this from my code, not how to structure the query itself. Therefore, from a code perspective, which is the better method, performance wise?

I will give you an example, based on the simplest of the queries I'm pulling, and will leave out the decision logic for now. As I said, this is the simplest. It uses a summary query to pull an aggregate view of the primary table, based on record age, categorized into 1 of 4 range values. The resulting 4 rows of aggregate data are populated to the archive. I have 4 other queries I've written, of varying levels of complexity that have to be pulled, but I would rather not post all of it.

Which of the following will give the better performance?

com.ActiveConnection = CurrentProject.Connection

'delete the temp table
DoCmd.DeleteObject acTable, "tbl_tmpArchv"

'create temp table
com.CommandText = "mkt_tmpArchv"
com.CommandType = adCmdStoredProc
com.Execute

'update Archive table
com.CommandText = "upd_Archv"
com.CommandType = adCmdStoredProc
com.Execute

Or this

'delete the temp table
DoCmd.DeleteObject acTable, "tbl_tmpArchv"

'create temp table
dbCurrent.Execute ("mkt_tmpArchv")

'update Archive table
dbCurrent.Execute ("upd_Archv")

Or this

com.ActiveConnection = CurrentProject.Connection

'delete the temp table
com.CommandText = "DROP tbl_tmpArchv"
com.CommandType = adCmdText
com.Execute

'create temp table
com.CommandText = "SELECT Date()-1 AS [Report Date], smry_FldRcllsAgng.StckBnd, smry_ FldRcllsAgng.[Age Category], smry_ FldRcllsAgng.[#Items], smry_ FldRcllsAgng.[Current Shares], smry_ FldRcllsAgng.[Current Value] INTO tbl_tmpArchv IN 'Y:\SF-Macros\MIS_db\WorkInProgress\Domestic Recalls_be_WIP_MB_3-23.mdb'
FROM smry_ FldRcllsAgng;"
com.CommandType = adCmdText
com.Execute 

'update Archive table
com.CommandText = "UPDATE tbl_Archv LEFT JOIN tbl_tmpArchv ON tbl_recycleArchive.TradeID = tbl_RecycledTrades.TradeID SET tbl_recycleArchive.dropDate = #" & CDate(x) & "#" & _
"WHERE (((tbl_recycleArchive.dropDate) Is Null) And ((tbl_RecycledTrades.TradeID) Is Null))"
com.CommandType = adCmdText
com.Execute

Or even

'delete the temp table
DoCmd.RunSQL, ("DROP tbl_tmpArchv")

'create temp table
DoCmd.RunSQL, ("SELECT Date()-1 AS [Report Date], smry_FldRcllsAgng.StckBnd, smry_ FldRcllsAgng.[Age Category], smry_ FldRcllsAgng.[#Items], smry_ FldRcllsAgng.[Current Shares], smry_ FldRcllsAgng.[Current Value] INTO tbl_tmpArchv IN 'Y:\SF-Macros\MIS_db\WorkInProgress\Domestic Recalls_be_WIP_MB_3-23.mdb'
FROM smry_ FldRcllsAgng;")

'update Archive table
DoCmd.RunSQL, ("UPDATE tbl_Archv LEFT JOIN tbl_tmpArchv ON tbl_recycleArchive.TradeID = tbl_RecycledTrades.TradeID SET tbl_recycleArchive.dropDate = #" & CDate(x) & "#" & _
"WHERE (((tbl_recycleArchive.dropDate) Is Null) And ((tbl_RecycledTrades.TradeID) Is Null))")

I am currently using the second example. The first was what was originally there, when it was just 1 archive being created. The Form Close event triggers all 5 archiving procedures to run, one after the other. I've recently clocked this at about 1 minute, +/- 15 seconds. The last two are the variations I am considering switching to, but before I do all that work, I need to know if there is any benefit to it.

Network issues asside, given that I am dealing with Traders, 15 seconds is a big deal to them, so anything I can shave will be appreciated.

Melanie

 

[rac2]

I think that the first example would be the fastest, followed very closely by the second, with the third and the fourth falling distantly behind.

And I would think that all of those differences would be fractions of seconds. The differences being mainly the result of compiling the queries. The database engine must translate the SQL statement into executable code. The first and second examples call stored procedures which have already been compiled. The executable code is stored and used when we call the procedure. In the third and fourth examples we are asking the database engine to interpret, plan, and compile the SQL statement everytime.

The difference between the first and second examples appears to be merely that you have specified that the source is a stored procedure in the first case. In the second case ADODB must figure that out. That involves communication with the database so that will take a few milleseconds.

I dont know what the difference might be between the Execute method of a connection object and the RunSQL method of a DoCmd, so the order there is just a guess. I am not a VBA programmer.

But none of that is going to add 15 seconds to the process.

Next I will look at the queries.

 

[rac2]

I am a little puzzled as to why you are using a temporary table.

And doubtful that the UPDATE query executes at all because it seems to contain a serious a syntax error. It names one table, tbl_Archv, in the UPDATE clause and a different table, tbl_recycleArchive, in the SET clause.


Why not use an INSERT statement -

INSERT tbl_recycleArchive (
         [Report Date], 
         StckBnd, 
         [Age Category], 
         [#Items], 
         [Current Shares], 
         [Current Value], 
         dropDate  )
SELECT Date()-1, 
       StckBnd, 
       [Age Category], 
       [#Items], 
       [Current Shares], 
       [Current Value],
       CDate(x) 
FROM smry_FldRcllsAgng

I am guessing that smry_ FldRcllsAgng is the table with 4 rows. And assuming that you have a way to assign a value to CDate(x). Also I used the same names for the columns in tbl_recycleArchive as are used in smry_FldRcllsAgng because I dont know what they are.

At any rate, INSERTing 4 rows will be faster than dropping and re-creating a table.

You did not say how you obtain the 4 summary rows, but GROUP BY takes more time than CREATEing or INSERTing. It can take a very long time for a large table without proper indexes.

 

[Tazcat]

Examples 3 and 4 aren't anything I am currently using, but something I was considering, if it would save time. Since they don't actually exist, I was writting them of the top of my head, and probably referenced the wrong tables, or copied something else that I was using elsewhere. In any event, at this precise moment, the actual SQL for 3 and 4 is irrelevant to the deeper question: how to write this so it performs better?

The background on the case I cited above is this; I have a Summary query that pulls aggregates and yes, uses grouping. The result is 4 rows giving record counts and value summaries within 4 age categories. The SQL is thus:

SELECT DISTINCTROW qry_FldRclls.StockBond, 
  Mid([AgeCategory],3,20) AS [Age Category], 
  Count(*) AS [#Items], 
  Sum(qry_FldRclls.CurrentShareBal) AS [Current Shares], 
  Sum(([CurrentShareBal]*[price]/IIf([sectype]="4",1,100)))
  AS [Current Value]
FROM qry_FldRclls
GROUP BY qry_FldRclls.StockBond, 
  Mid([AgeCategory],3,20);

Access being access, you can't run an Update query to a table, when the source is a query that has groupings in it. You get the dreaded "Option requires an updateable query" error. So, I had to use a temp table. That meant building a Make Table query, which is thus:

SELECT smry_FailedRecallsAging.* 
INTO tbl_tmpArchv
FROM smry_FailedRecallsAging;

However, when running a Make Table query from code, the Make Table's normal function to remove the existing table before creating itself is disabled. So, you have to delete the temp table before you create the new one. Tehcnically, in the interest of good code and not leaving garbage laying around my db, I should be doing this at the end of the subroutine and not at the beginging, but... well, there you have it.

Once I have my temp table, I use the Update query to pass changes in the data to the archive table, thus:

UPDATE tbl_Archv, tbl_tmpArchv 
SET tbl_Archv.[#Items] = tbl_tmpArchv![#Items], 
  tbl_Archv.[Current Shares] = tbl_tmpArchv![Current Shares], 
  tbl_Archv.[Current Value] = tbl_tmpArchv![Current Value]
WHERE (((tbl_Archv.[Report Date])=Date()) 
  AND ((tbl_Archv.StockBond)=[tbl_tmpArchv]![StockBond]) 
  AND ((tbl_Archv.[Age Category])=[tbl_tmpArchv]![Age Category]));

All of this is being called from a sub, (simplified here, as the sub itself calls 3 of these procedures to pull data from 5 queries to update 3 archive tables... 3 of the queries get pulled into a Union query and it's all very fightening and I really don't want to post the code for all of that!! ) OK, semi-joking aside, it is thus:

Public Sub subUpdateArchive()
'sub to create and update the Archive tables
Dim dbCurrent As Database
Dim rsLastDate As Recordset
Dim dtRptDate As Date

Set dbCurrent = CurrentDb()
Set rsLastDate = dbCurrent.OpenRecordset("max_RptDate")

    DoCmd.RunSQL ("DROP TABLE tbl_tmpArchv")

    dbCurrent.Execute ("mkt_tmpArchv")

    rsLastDate.MoveFirst
    dtRptDate = rsLastDate("LastDate")
   
    If dtRptDate = Date Then
        'update existing archives
        dbCurrent.Execute ("upd_Archv")
    Else
        'create current day's archives
        dbCurrent.Execute ("apnd_Archv")
    End If

End Sub

If you know a way to pass the summary values direct to an Update statement via code, where it wont trigger the dreaded error message of death, I'd be glad to know it. But, keep in mind this summary does have to group and perform several caculations before it can update.

 

[rac2]

At this point I dont have much to offer except some ideas for analyzing the lag time. The question in mind is which is the more time consuming, the summary query or the table destruction and reconstruction.

Does the summary query finish in a reasonable time or does it take forever?

I tend to think that is where the issue lies. But I have never designed a process that creates and drops tables explicity from a subroutine so I dont have a feel for the amount of time that takes. When I delete a table in the Access GUI, it is like instantaneous.

Also I am thinking that redesigning the "archive" might be worthwhile. When I hear "archive" I think of something that is finished, not something that will be updated.

I dont see any date range in the summary query, is it a summary for all time? Could you produce the archive once-a-day with values as-of the end of the day? Then for a current summary over all time, summarize only since the last archive and add that to the value from the last archive. Thus nothing would be updated, created, or dropped.

If qry_FldRclls is very large or is a VIEW based on a very large table, that last idea might work.