Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :

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 is:

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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.