SELECT versus INSERT when adding new records.

Microsoft has done testing of SELECT versus INSERT, DELETE and UPDATE statements in SQL Server to determine the relative performance of each type of statement when using tables with clustered indexes versus heaps. For the purposes of an Access forum, since we tend to use tables with clustered indexes almost exclusively – that would be the Primary Key autonumber field – I will limit this discussion to those results only.

SELECT statements differ from INSERT, DELETE and UPDATE statements in that a SELECT statement is used to work on large recordsets, typically, whereas INSERT, DELETE and UPDATE statements are better used a single record at a time. At least at the transaction level, SELECT can work on large chunks of data, while the other three work a row at a time. An Exception would be “DELETE FROM Table1” and who wants to see that?!

Microsoft carried out the test using 1,000,000 records. In their tests, when inserting 1,000,000 records, there was a performance gain of roughly 11% (from what I recall) using the SELECT statement and batch updates versus the INSERT statement.

The reason for the performance gain on tables with clustered indexes should be obvious. With an INSERT statement, when a row is inserted, the actions run roughly thus:

Insert row 1
Re-index to accommodate single row
Insert row 2
Re-index to accommodate single row
…
Insert row n
Re-index to accommodate single row

With a batch update on a SELECT statement on the other hand, the entire batch, or chunks of it, can be committed thusly:

Update Batch
Re-Index to accommodate entire batch a row at a time. (Batches may be limited by network bandwidth so there may be another batch to the operation).

The more indexes that a table has (1 clustered, 249 unclustered in SQL Server 2005, 1 clustered, 31 unclustered in Access 2007), the greater this bottleneck can become. I would say most of us develop with 1 clustered (PK) an 4 or 5 unclustered (FK) per table at the most, but the issue is there (You are indexing your FK's, aren't you? :))

The way this would play out in code, then, is that if we were doing a batch update, I submit, the better model would be to loop through a recordset using a SELECT statement, depending on the table size. So that would look like (this adds rows to a table with one field called Count):

Dim sSQL As String
Dim rs As ADODB.Recordset
Dim iCount As Integer

iCount = 0

sSQL = "SELECT * FROM Table1"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until iCount = 20
    rs.AddNew
    rs("Count") = iCount
    iCount = iCount + 1
Loop

rs.UpdateBatch

This would effect the savings noted by Microsoft in its testing. UpdateBatch is available in ADO, but not DAO, I am pretty sure (Comments?)

CAVEAT: Using Recordset.Update within the Do Loop above in either ADO or DAO is equivalent for purposes of this analysis to using an INSERT statement since only one line at a time is committed, and re-indexing must occur for each Update statement. In that case, using SELECT instead of INSERT adds the overhead of opening a recordset consisting of all the rows in the underlying table into memory to effect the inserts.

In the case where only a single record would be added to a table, and then re-indexed, the greater performance gain would come from the INSERT statement since it does exactly what the SELECT, Open Recordset, Do, AddNew, …, Update, Loop does, without the added overhead of the complete recordset being opened in memory. This may not be significant when opening a local Access table, parts of which are already in memory, but would be more significant when opening a SQL Server table, which could contain hundreds of thousands of records. Much of this data would also have to make its way across the wire, and would have been pulled without the benefit of a server-side view that had been optimized by the SQL Server (although a view may not be updateable so a select table statement would be necessary.)

INSERT syntax is a little trickier to maintain since it takes the ungainly form:

sSQL = “INSERT INTO Table1([Count]) VALUES (“ & iCount & “)”

This can get pretty ugly the more fields you add, but will only require opening a connection and recordset object, and then the work is done.

In point of fact, in a local Access database, a user should see little performance gain with one method over another. So the developer is free to choose which ever method best suits their preferences. Good news for everyone.

In a larger enterprise level application, however, the extra bit of keeping the coding in order becomes a more significant benefit to the user as the size and number of users of the application increase. In this case, an INSERT statement for a single record is preferred, and a SELECT statement with a batch update for multiple records would be the way to go.

I hope this has helped to clear the air a bit on this issue, and thanks to martinrhague for piquing my interest again on this topic.


mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

I did not mention that when developing an Access/Access solution, versus an Access/SQL solution, DAO is preferred. We have had that discussion here before. DAO is optimized for Jet and cannot be used with SQL Server. So in most of our work, DAO is going to be the choice.

mmcdonal

Thanks for the info mmcdonal. Interesting stuff.