Thank you for your earlier advice on whether or not I should use a make table query. I have another question related to the same project.
As before, I have my backend on Microsoft 365 with Azure SQL and my frontend on my computer using Access 2013.
I have created a procedure to create invoices based on enrollments, materials purchased, relevant discounts, and other criteria. To make the calculations and modify the data, I make temporary tables on the Access 2013 frontend, modify it there, and then append the new rows to the Microsoft 365 backend. The entire procedure takes about 2:50 (2 minutes and 50 seconds), but 2:35 of that is taken up by the two append queries to the Microsoft 365 backend.
The first query takes 28 seconds to append 195 records. The VBA procedure I use is:
Private Sub Invoices_PopulateStudentInvoices(InvoiceTableName As String)
Dim TableSQL As String
'Populate StudentInvoices table with information from temporary invoices table
TableSQL = "INSERT INTO Access_tStudentInvoices" _
& " SELECT " & InvoiceTableName & ".*" _
& " FROM " & InvoiceTableName
CurrentDb.Execute TableSQL, dbFailOnError + dbSeeChanges
End Sub
Using debug.print TableSQL, the executed query looks like:
INSERT INTO Access_tStudentInvoices SELECT tmptbl_StudentInvoices.* FROM tmptbl_StudentInvoices
The backend Access_tStudentInvoices table has one index, the autonumber prmary key, and one lookup field.
The second query takes 2 minutes 7 seconds to append 634 records. The VBA procedure I use is:
Private Sub Invoices_PopulateInvoiceItems(InvoiceItemsTableName)
Dim TableSQL As String
'Populate Azure SQL InvoiceItems table with information from temporary InvoiceItems table
TableSQL = "INSERT INTO Access_tInvoiceItems (StudentInvoiceID, StudentID, AddressesID, ItemTypeID, Description, ItemID, Cost, BillDate)" _
& " SELECT StudentInvoiceID, StudentID, AddressesID, ItemTypeID, Description, ItemID, Cost, BillDate" _
& " FROM " & InvoiceItemsTableName _
& " WHERE InvoiceItemsID Is Null"
CurrentDb.Execute TableSQL, dbFailOnError + dbSeeChanges
End Sub
Using debug.print TableSQL, the executed query look like:
INSERT INTO Access_tInvoiceItems (StudentInvoiceID, StudentID, AddressesID, ItemTypeID, Description, ItemID, Cost, BillDate) SELECT StudentInvoiceID, StudentID, AddressesID, ItemTypeID, Description, ItemID, Cost, BillDate FROM tmptbl_InvoiceItems WHERE InvoiceItemsID Is Null
The backend Access_tInvoiceItems table has one index, the autonumber primary key, and four lookup fields.
Are there any strategies that might help speed up these append queries (and an update query I will be using later) that have relatively large numbers of records? Or is this simply an unavoidable downside to working with Microsoft 365? (The upsides in my situation are enough to stick with it, but more speed would be nice.)
I appreciate your help and advice.
Best wishes,
Ryan Hagglund
MY English School
Yamagata, Japan
Posted by: "Ryan Hagglund" <rhagglund@myeigo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar