Append Query Takes A Very Long Time To Load, But Not Always

[VariableZ]

Windows 7, 64bit
Office 2010 Professional 32bit version 14.0.6112.5000

I have a button that I click that runs a delete query, then an append query, and then opens a report based on some parameters and the data in the table that the query appends to.
When I first open the database, the whole procedure takes a few seconds, which is acceptable. If I close the report and click the button again, it still takes a few seconds. But if I sort by any field in the report before closing the report, the next time I click the button the append query takes over a minute run. The code for the button is:

Private Sub btnOpenrptLotData_Click()

DoCmd.OpenQuery "qryLotDataConglomerateDELETE"

DoCmd.OpenQuery "qryLotDataConglomerateAPPEND"

DoCmd.OpenReport "rptLotData", acViewReport, , "([qryLotDataConglomerateWithTotals].[FormattedDate] Between #" & VBA.Date & "# And #" & DateAdd("m", -1, VBA.Date) & "#) And ([FirstOfModel] Like ""*" & [VINCodeReportSortByUnitField] & """" & " Or [FirstOfModel] Like """ & [VINCodeReportSortByUnitField] & " *"")"

End Sub

But I don't think the problem resides there, because once the slow-down occurs, I can run the append query directly and it STILL takes over a minute to run. The SQL for the append query is:

INSERT INTO tblLotDataConglomerate ( RecordID, SeqNum, LotNum, VINCode, Model, FormattedDate, FormattedTime, UniqueID, SkippedVIN, RightVINCode7, FirstVINOfLot, LastVINOfLot, Quantity )

SELECT qryLotDataWithUID.RecordID, qryLotDataWithUID.SeqNum, qryLotDataWithUID.LotNum, qryLotDataWithUID.VINCode, qryLotDataWithUID.Model, qryLotDataWithUID.FormattedDate, qryLotDataWithUID.FormattedTime, qryLotDataWithUID.UniqueID, tblSkippedVINSheets.SkippedVIN, Right([VINCode],7) AS RightVINCode7, tblReceivedVINSheets.FirstVINOfLot, tblReceivedVINSheets.LastVINOfLot, tblReceivedVINSheets.Quantity

FROM (qryLotDataWithUID LEFT JOIN tblSkippedVINSheets ON qryLotDataWithUID.VINCode = tblSkippedVINSheets.SkippedVIN) LEFT JOIN tblReceivedVINSheets ON qryLotDataWithUID.UniqueID = tblReceivedVINSheets.UniqueLotID

ORDER BY qryLotDataWithUID.FormattedDate DESC , qryLotDataWithUID.FormattedTime DESC;

The query qryLotDataWithUID (which the append query is mostly based on) takes a little over a second to run both when the append query is running quickly and when it's running slowly.





Once I've Compact/Repair-ed, the problem goes away. Is there anything in the code that could be causing this problem, or does anyone have any ideas for changes or workarounds that might help fix it?

[June7]

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[AccessMSSQL]

How many records are in the base table that you are appending from? Do you have indexes set up on the fields you sort by?

[VariableZ]

I was hoping not to have to provide the project for analysis due to the data contained. Would it be useful without any data?

[VariableZ]

32,800 records are in the main base table. The first query removes about 400. tblReceivedVINSheets has 129 records and tblSkippedVINSheets has 1 record. The attached DB in the next post should answer the question about indexes. I've changed a couple of things since my first post (removed the ORDER BY line in the append query, since it wasn't needed, and removed RightVINCode7 since I was calculating it elsewhere and meant to get rid of it previously), which didn't help the time issue, but did, strangely, affect the way the 'Run Query' bar behaves in the bottom right corner of the screen while the query is running. Before it would quickly get about a third of the way across and then mosey the rest of the way. Now it zips to the end and then stays there for a long time.

[AccessMSSQL]

I think the problem might be with the report Properties. The Order By and Order By On Load properties of the report (in the Data Tab) need to be reset. Set Order By On Load = "No" and clear the Order By property if there is any text in it.

[VariableZ]

Order By is already blank and Order By On Load is already set to "No". Even if they weren't I don't see how that would affect the query run time. The query runs for a very long time even when the report isn't loaded.

In case this helps pinpoint the issue, I've just recreated the DB in Access 2002-2003 file format (*.mdb) and now when I click the button on a fresh load it takes about 4 seconds to run and after doing a sort on the report and closing the report it takes 8-9 seconds. While this is now in the realm of usable behavior, I'm afraid that at some point MS is going to stop supporting *.mdb, and of course I'd really like to know what's going on anyway so that I can avoid this problem in the future.

I'll go ahead and post a stripped version of the original. If that doesn't help, let me know what you might need.

[AccessMSSQL]

I just took a took a look at it. I'm assuming dbo_LotData is a SQL table? At first glance it looks like you need to set RecordID as a primary key in LotData and add some indexes to SeqNo, LotNo, VINCode, etc. Indexes could be set up on fields you have criteria in your base query. You may want to remove the Like clauses in your query also. I don't know your data but I don't think you need Not Like "" on an Integer field (seqNo). Try taking out the Like clauses and use <>"" instead? These are my only suggestions at this point from a query/table standpoint.

If this doesn't work I would suggest moving the code to VBA and running the delete and append statements from code.

Not sure what else to suggest here if it's working great in .MDB but not .ACCDB without more info.

[VariableZ]

dbo_LotData's RecordID field is actually set as a primary key, but I lost that when I changed it to a regular table - it's actually a linked table in the real db. Since it's a linked table, I can't set up indexes on the data (Unless I can. Can I?) I tried changing the LIKE clauses to <>"", but that resulted in data type mismatch errors, so I got rid of them entirely, just leaving Is Not Null. Unfortunately, things did not improve.

I wouldn't say that things are working 'great' in *.mdb. Funtional, yes. But it still takes twice as long for the query to run after the report has been sorted, just not 30 times as long. I'm concerned that as more data is added, the same problem is going to catch up to me even in .mdb format.

Upon further experimentation, I've discovered that if I create a simple report that pulls information directly from the dbo_LotData table (ie, doesn't call any queries; no grouping, etc) and then, after freshly opening the database, open the report, sort it, and close it, the same append query slowdown problem occurs. At this point I am utterly baffled. Is there any way to send this type of thing to MS for bug testing?

Assuming that this will be an ongoing problem, how would I go about creating an append query in VBA? If writing the actual code is time prohibitive, then sending me a link to go in the right direction would be most helpful. Thanks for everyone's time on this.

[June7]

Here is one site for tutorials on writing SQL statements http://www.w3schools.com/sql/sql_intro.asp

Executing an action SQL in VBA can be done with:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tablename"
DoCmd.SetSWarnings True

or

CurrentDb.Execute "DELETE FROM tablename"

[AccessMSSQL]

What version of SQL Server are you running on and how many records are in dbo_LotData? I will try to reproduce behavior. Also, can you provide how many records are in the other tables?