Access 2007 First cn.Execute Statement very slow

I am currently working on an application in Access 2007 with a split FE and BE. FE is local wiht BE on a network share. To eliminate some of the issues found with using linked tables over a network, I am attempting, through VBA using ADO, to load two temp table开发者_如何转开发s with data from two linked when the application first loads using the cn.Execute "INSERT INTO TempTable1 SELECT * FROM LinkedTable1" and cn.Execute "INSERT INTO TempTable2 SELECT * FROM LinkedTable2".

LinkedTable1 has 45,552 records in it and LinkedTable2 has 45,697 records in it.

The first execute statement takes anwhere from 50-85seconds. However the second execute statement takes no more than 9 seconds. These times are consistent. In an attempt to see if there were issues with one of the tables and not the other, I have switched the order of the statements in my code and the times still come out the same (first execute is way too long and second execute is very fast). (As a side note, I have also tried DAO using the CurrentDB.Execute command with no different results.) This would make sense to me if the first statement was processing more records than the second, but although a small number, the second table has more records than the first!

Does anyone have ANY suggestions on why this is happening and/or how to get this first execute statement to speed up?

Thanks in advance! ww

What indexes do you have defined on the two temp tables, as well as primary key definitions? Updating the indexes as the data is appended could be one reason one table is slower.

My guess is that there are two sources for the difference:

1. the initial creation of the remote LDB file when you execute the first INSERT statement. This shows up as overhead in the first SQL command, when it's actually something that persists through both.

2. caching: likely the file is small enough that Jet/ACE is pulling large chunks of it across the wire (the header and metadata, plus the requested data pages) during the first operation so that there's much less data that is not already in local memory when the second command is issued.

My question is why you are having problems with linked table performance in the first place. Solve that and you then won't have to muck about with temp tables. See Tony Toews's Performance FAQ.