1

I am performing a large number of insert operations using VBA - Access. the VBA code is in a file "control.accdb" performing operations on "data.accdb" Few tables in the process are created from the VBA , few remain as such.

After finishing Phase 1 of the process , I do a Compact And Repair either manually or from DBEngine.Compact VBA code. The second phase (same code - next iteration) becomes very very slow.

Here is the Insert Code :

SQL = "INSERT INTO ExpectedResult " _
  & "( DLID, NumRows, Total, SubjectBlock, NextSubjectBlockSeq ) " _
  & "SELECT INVDL.DLID, 1 AS Expr1, INVDL.Amount, " _
  & "INVDL.SubjectBlock,INVDL.SubjectBlockSeq+1 AS Expr2 " _
  & "FROM INVDL where SubjectBlock > 0;"
cdb.Execute (SQL)
TargetNumRows = 2
Do While TargetNumRows < MaxSubjectBlockSeq + 1
    Set qdf = cdb.QueryDefs("pq_appendToExpectedResult")
    qdf!TargetNumRows = TargetNumRows  '' parameter value
    qdf.Execute
    TargetNumRows = TargetNumRows + 1
    Set qdf = Nothing
Loop

where pq_appendToExpectedResult :

PARAMETERS TargetNumRows Long;
INSERT INTO ExpectedResult _
  ( DLID, NumRows, Total, SubjectBlock, NextSubjectBlockSeq ) _
SELECT INVDL.DLID+1-[TargetNumRows], [TargetNumRows] AS Expr1, _
  [ExpectedResult].[Total]+[INVDL].[Amount] AS NewTotal, _
  INVDL.SubjectBlock, [INVDL].[SubjectBlockSeq]+1 AS Expr2 _
FROM INVDL INNER JOIN ExpectedResult _
   ON (INVDL.SubjectBlock = ExpectedResult.SubjectBlock)  _
   AND (INVDL.SubjectBlockSeq = ExpectedResult.NextSubjectBlockSeq) _
WHERE (((INVDL.SubjectBlockSeq)>=[TargetNumRows])  _
   AND (ExpectedResult.NumRows=[TargetNumRows]-1));

To the best of my knowledge , all indexes in INVDL are untouched. I drop the table ExpectedResult and recreate it with indexes after each Phase (iteration). Other tables - dont think will make any difference.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
arcotenterprises
  • 131
  • 1
  • 4
  • 14
  • Seems as if Access is doing some sort of "caching" within the accdb to speed things up, which you are clearing during the C&R. You would need to optimize your insert... Can we see your code for that? – Robert Harvey Apr 22 '13 at 16:42
  • http://stackoverflow.com/questions/15584949/sql-vbscript-intelligent-algorithm-to-find-sum-combinations-quickly , this is the question and code is provided by GordThompson – arcotenterprises Apr 22 '13 at 16:49
  • Paste only the relevant code into this question. – Robert Harvey Apr 22 '13 at 16:52
  • @RobertHarvey http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c , will making it as a recordset append help , – arcotenterprises Apr 23 '13 at 22:50
  • 1
    Don't know about that, but using DAO definitely seems to improve things. That's not really surprising, given that DAO is native to Access. – Robert Harvey Apr 23 '13 at 22:51
  • I worked on your code block to eliminate the horiz. scroll bar. My goal was to add new-lines while preserving workable code. Hopefully I got all the line-continuations correct. – Smandoli Aug 04 '13 at 15:06
  • Have you tried turning off row locking? That can be HUGE source of bloat. (because Access fakes row locking by expanding + padding a record to 2k in size). I seen some data files that are only about 5 megs size (before and after) the code as compared to well over a 100 megs of expansion for the EXACT same code. So the row locking is something I would try turning off. – Albert D. Kallal Aug 05 '13 at 01:55

0 Answers0