Level7Sensei
asked on
MS Access INSERT into linked SQL table causing time-out
Running MS Access 2013, 64 bit on Windows 7. Linking to MS SQL 2012.
I'm getting various errors such as "Inserting on a linked table failed" and "ODBC timed out" when running a series of Append Queries in MS Access to add records into a linked SQL table.
I am running 3 queries over and over building up a table until it is complete. When I run these queries manually, they work fine. When I run them using VBA in a series manner, I get these timeout issues.
Note that before these 3 queries, I run 3 other "base" queries that work fine using this method.
I run every query using the following Sub:
Public Sub ExecuteQuery(QueryName as String)
Dim dbs as DAO.Database
Set dbs = CurrentDB
dbs.Execute QueryName
Set dbs = Nothing
End Sub
I'm getting various errors such as "Inserting on a linked table failed" and "ODBC timed out" when running a series of Append Queries in MS Access to add records into a linked SQL table.
I am running 3 queries over and over building up a table until it is complete. When I run these queries manually, they work fine. When I run them using VBA in a series manner, I get these timeout issues.
Note that before these 3 queries, I run 3 other "base" queries that work fine using this method.
I run every query using the following Sub:
Public Sub ExecuteQuery(QueryName as String)
Dim dbs as DAO.Database
Set dbs = CurrentDB
dbs.Execute QueryName
Set dbs = Nothing
End Sub
I've seen this many times and usually end up converting the queries to pass-through queries. Often speed is changed from minutes to seconds.
Walter is right - this seems to be a limitation of the ODBC driver that falls back to send an insert/update command for each and every record.
To edit the queries I use SQL Server Management Studio.
/gustav
Walter is right - this seems to be a limitation of the ODBC driver that falls back to send an insert/update command for each and every record.
To edit the queries I use SQL Server Management Studio.
/gustav
ASKER
Right now we are only talking about 5-10 records per write. Each record has 6 fields. Although this is a small amount of data the queries are very complex and resource heavy. Don't know enough yet to create SQL insert/append queries. Comfortable with select only. Recommendation?
Yes. use SSMS (SQL Server Management Studio). It has - a little like Access - a GUI designer for queries where you can rebuild your queries and look for possible speed improvements.
You can also copy and paste your SQL into a query and make the necessary adjustments.
/gustav
You can also copy and paste your SQL into a query and make the necessary adjustments.
/gustav
Is the Append query joining to a table that is in SQL Server? Queries that join Jet/ACE tables to server-side tables always request that the entire server-side table be downloaded to memory on the local PC in order to do the join locally. While these joins are very useful, they can be very resource intensive if the server-side table has more than a few thousand rows.
If you look at the queries that are actually being sent to the server, you will see that Access selects the data and populates variables and then sends an insert with variables to the server for each row. So, appending 100 rows will cause 200 queries to be run. 100 to select the local data and 100 to insert it to the server table. At some point, it becomes more efficient to copy the source table to the server temporarily and do the append on the server and then delete the temp table.
If you look at the queries that are actually being sent to the server, you will see that Access selects the data and populates variables and then sends an insert with variables to the server for each row. So, appending 100 rows will cause 200 queries to be run. 100 to select the local data and 100 to insert it to the server table. At some point, it becomes more efficient to copy the source table to the server temporarily and do the append on the server and then delete the temp table.
ASKER
all the tables are on the Server. I attempted to convert my Access query into an SQL pass through compatible statement, but with the multiple joins and multi-level dlookups, I have not been successful. The workaround that I will use (for now) is to use a local table for building then append all the data at once to the server. Works fine, but was trying to avoid local tables and queries. Once my Kung-fu for SQL Server has improved, I will redo this later the proper way. Thanks for trying!
Perhaps posting the append query will get a solution.
Yes, posting the query might help us helping you.
ASKER
There are 3 queries. One below. If this can be fixed, I should be able to apply the syntax for the other two. Thanks.
INSERT INTO tblRCL_Expected ( RCFID, TAOID, SITID, RCEID, DateMin, DateMax )
SELECT dbo_tblRCL_Schedule.RCFID, dbo_tblRCL_Schedule.TAOID, dbo_tblSite_Forms.SITID, dbo_tblRCL_Schedule.RCEID, CDate(DMin("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'"))+1 & "'")) & "'")) AS rDateMin, CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")) AS rDateMax
FROM (qryRCL_Expected_Max INNER JOIN (dbo_tblRCL_Schedule INNER JOIN dbo_tblSite_Forms ON dbo_tblRCL_Schedule.RCFID = dbo_tblSite_Forms.RCFID) ON (qryRCL_Expected_Max.RCFID = dbo_tblRCL_Schedule.RCFID) AND (qryRCL_Expected_Max.TAOID = dbo_tblRCL_Schedule.TAOID) AND (qryRCL_Expected_Max.SITID = dbo_tblSite_Forms.SITID) AND (qryRCL_Expected_Max.RCEID = dbo_tblRCL_Schedule.RCEID)) INNER JOIN dbo_tblTaskOrders ON dbo_tblRCL_Schedule.TAOID = dbo_tblTaskOrders.TAOID
WHERE (((dbo_tblRCL_Schedule.TAOID) Like IIf(IsNull(fnRCL_RPT_TAOID()),"*",fnRCL_RPT_TAOID())) AND ((dbo_tblSite_Forms.SITID) Like IIf(IsNull(fnRCL_RPT_SITID()),"*",fnRCL_RPT_SITID())) AND ((dbo_tblRCL_Schedule.RCEID) Like IIf(IsNull(fnRCL_RPT_RCEID()),"*",fnRCL_RPT_RCEID())) AND ((CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")))<=fnRCL_RPT_MaxDate() And (CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")))<=(IIf(IsNull([SCH_DateStop]),fnRCL_RPT_MaxDate(),CDate([SCH_DateStop])))) AND ((dbo_tblRCL_Schedule.SCH_FreqType)="Monthly") AND ((dbo_tblTaskOrders.PROID) Like IIf(IsNull(fnRCL_RPT_PROID()),"*",fnRCL_RPT_PROID())));SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey - If I didn't need help I would not have posted. Useful comments are appreciated, sarcastic criticism is not.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Relax. Instead of yelling you should listen and learn from Pat and Walter.
/gustav
/gustav
Level, I wasn't being sarcastic. That isn't my style. I realize you are here for help and this query needs a lot of help. I'm sorry you read my comment as a personal attack. It wasn't.
There are lots of examples that show the use of domain functions and they are very useful. However, except in a place like this you probably won't see any advice regarding where the use of domain functions is appropriate and where it is not. Since domain functions are queries themselves, using them in a query will frequently cause the main query to execute a query for each domain function for each row of the main query. So, the place to begin is to eliminate the domain functions from the query. Sometimes they can be replaced by a join to a totals query. The totals query will be much more efficient at aggregating data than individual domain functions. Sometimes the functions can be eliminated by running them once and saving the value to a form field and referencing the form field. Although the query analyzer is pretty slick sometimes you need to give it a little help and this technique ensures that the query engine doesn't try to run the domain function more than once when it isn't necessary.
In my first Access app, I discovered the pain of domain functions in a code loop in a big way. My first attempt at a particular process ended up running 90 minutes to update 109,000 records because for every record I was running three DLookup() functions. Once I realized what they were doing, I changed to using left joins and the update loop ran in 3.5 minutes. Then I reviewed it again and realized that the joins had reduced the complexity of the query and I no longer needed a code loop so I changed to using an update query and that cut the run time to less than a minute.
Three things to take away from that.
1. Domain functions should never be used in a query or code loop.
2. All things being equal, update queries are faster than code loops.
3. When your recordset is small enough as my test data was, almost nothing you do matters so I didn't actually discover how awful the procedure was until I did one final test with production data.
There are lots of examples that show the use of domain functions and they are very useful. However, except in a place like this you probably won't see any advice regarding where the use of domain functions is appropriate and where it is not. Since domain functions are queries themselves, using them in a query will frequently cause the main query to execute a query for each domain function for each row of the main query. So, the place to begin is to eliminate the domain functions from the query. Sometimes they can be replaced by a join to a totals query. The totals query will be much more efficient at aggregating data than individual domain functions. Sometimes the functions can be eliminated by running them once and saving the value to a form field and referencing the form field. Although the query analyzer is pretty slick sometimes you need to give it a little help and this technique ensures that the query engine doesn't try to run the domain function more than once when it isn't necessary.
In my first Access app, I discovered the pain of domain functions in a code loop in a big way. My first attempt at a particular process ended up running 90 minutes to update 109,000 records because for every record I was running three DLookup() functions. Once I realized what they were doing, I changed to using left joins and the update loop ran in 3.5 minutes. Then I reviewed it again and realized that the joins had reduced the complexity of the query and I no longer needed a code loop so I changed to using an update query and that cut the run time to less than a minute.
Three things to take away from that.
1. Domain functions should never be used in a query or code loop.
2. All things being equal, update queries are faster than code loops.
3. When your recordset is small enough as my test data was, almost nothing you do matters so I didn't actually discover how awful the procedure was until I did one final test with production data.
Have you tried to determine how many rows are inserted before the timeout error starts? I think that would be a good thing to discover.
With that information, you may want to change your logic to give some delay between blocks of inserts.
Also, other thing that may be causing overhead and lead to this error is implicit transactions. Maybe you should build your macro in a way that you can commit after X lines being inserted, instead of probably commit each line as it seems.