SQL queries take too long
I am using ADO to save data to MS Access database. It took quite a long time to save the data to a file (about 7 seconds is too long for our purposes). I looked at the number of SQL queries running and about 4200; not a whole bunch of data though.
Database connection appears to be the bottleneck. Do you know any way to reduce the amount of time it takes; either by combining multiple statements into one to reduce overhead, or some ADO / MS-Access trick?
Can you, for example, insert multiple rows into a table at once, and will it be noticeably faster?
Additional Information:
One of the reasons we have so many requests is because we insert a row and then receive another request to get its auto-incrementing id; then use that id to insert some more lines, linking them to the first
In response to several comments and responses: I leave the connection open all the time and execute it as one transaction with BeginTransaction () and CommitTransaciton ()
a source to share
Some people have pointed out to @@IDENTITY
be fast, so there is evidence (using VBA) of how two of my tables at INSERT INTO
once using the trick is VIEW
about three times faster than doing two INSERTS and grabbing the value @@IDENTITY
each time ... which is not surprising because the latter includes three operators Execute
, and the first includes only one :)
On my machine for 4200 iterations, the trick VIEW
took 45 seconds and the approach @@IDENTITY
took 127 seconds:
Sub InitInerts()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE TableA" & vbCr & "(" & vbCr & " ID IDENTITY NOT" & _
" NULL UNIQUE, " & vbCr & " a_col INTEGER NOT NULL" & vbCr & ")"
.Execute Sql
Sql = _
"CREATE TABLE TableB" & vbCr & "(" & vbCr & " ID INTEGER NOT" & _
" NULL UNIQUE" & vbCr & " REFERENCES TableA (ID)," & _
" " & vbCr & " b_col INTEGER NOT NULL" & vbCr & ")"
.Execute Sql
Sql = _
"CREATE VIEW TestAB" & vbCr & "(" & vbCr & " a_ID, a_col, " & vbCr & " " & _
" b_ID, b_col" & vbCr & ")" & vbCr & "AS " & vbCr & "SELECT A1.ID, A1.a_col," & _
" " & vbCr & " B1.ID, B1.b_col" & vbCr & " FROM TableA AS" & _
" A1" & vbCr & " INNER JOIN TableB AS B1" & vbCr & " " & _
" ON A1.ID = B1.ID"
.Execute Sql
End With
Set .ActiveConnection = Nothing
End With
End Sub
Sub TestInerts_VIEW()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim timer As CPerformanceTimer
Set timer = New CPerformanceTimer
timer.StartTimer
Dim counter As Long
For counter = 1 To 4200
.Execute "INSERT INTO TestAB (a_col, b_col) VALUES (" & _
CStr(counter) & ", " & _
CStr(counter) & ");"
Next
Debug.Print "VIEW = " & timer.GetTimeSeconds
End With
End Sub
Sub TestInerts_IDENTITY()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim timer As CPerformanceTimer
Set timer = New CPerformanceTimer
timer.StartTimer
Dim counter As Long
For counter = 1 To 4200
.Execute "INSERT INTO TableA (a_col) VALUES (" & _
CStr(counter) & ");"
Dim identity As Long
identity = .Execute("SELECT @@IDENTITY;")(0)
.Execute "INSERT INTO TableB (ID, b_col) VALUES (" & _
CStr(identity) & ", " & _
CStr(counter) & ");"
Next
Debug.Print "@@IDENTITY = " & timer.GetTimeSeconds
End With
End Sub
What this shows is that the bottleneck is now the overhead associated with executing multiple statements. What if we could only do this in one application? Well, guess what, using my contrived example, we can. First, create a unique integer sequence table which is a standard SQL trick (every database must have one, IMO):
Sub InitSequence()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim sql As String
sql = _
"CREATE TABLE [Sequence]" & vbCr & "(" & vbCr & " seq INTEGER NOT NULL" & _
" UNIQUE" & vbCr & ");"
.Execute sql
sql = _
"INSERT INTO [Sequence] (seq) VALUES (-1);"
.Execute sql
sql = _
"INSERT INTO [Sequence] (seq) SELECT Units.nbr + Tens.nbr" & _
" + Hundreds.nbr + Thousands.nbr AS seq FROM ( SELECT" & _
" nbr FROM ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
" ALL SELECT 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
" [Sequence] UNION ALL SELECT 3 FROM [Sequence] UNION" & _
" ALL SELECT 4 FROM [Sequence] UNION ALL SELECT 5 FROM" & _
" [Sequence] UNION ALL SELECT 6 FROM [Sequence] UNION" & _
" ALL SELECT 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
" [Sequence] UNION ALL SELECT 9 FROM [Sequence] ) AS" & _
" Digits ) AS Units, ( SELECT nbr * 10 AS nbr FROM" & _
" ( SELECT 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
" 1 FROM [Sequence] UNION ALL SELECT 2 FROM [Sequence]" & _
" UNION ALL SELECT 3 FROM [Sequence] UNION ALL SELECT" & _
" 4 FROM [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
" UNION ALL SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
" 7 FROM [Sequence] UNION ALL SELECT 8 FROM [Sequence]" & _
" UNION ALL SELECT 9 FROM [Sequence] ) AS Digits )" & _
" AS Tens, ( SELECT nbr * 100 AS nbr FROM ( SELECT" & _
" 0 AS nbr FROM [Sequence] UNION ALL SELECT 1 FROM" & _
" [Sequence] UNION ALL SELECT 2 FROM [Sequence] UNION"
sql = sql & _
" ALL SELECT 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
" [Sequence] UNION ALL SELECT 5 FROM [Sequence] UNION" & _
" ALL SELECT 6 FROM [Sequence] UNION ALL SELECT 7 FROM" & _
" [Sequence] UNION ALL SELECT 8 FROM [Sequence] UNION" & _
" ALL SELECT 9 FROM [Sequence] ) AS Digits ) AS Hundreds," & _
" ( SELECT nbr * 1000 AS nbr FROM ( SELECT 0 AS nbr" & _
" FROM [Sequence] UNION ALL SELECT 1 FROM [Sequence]" & _
" UNION ALL SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
" 3 FROM [Sequence] UNION ALL SELECT 4 FROM [Sequence]" & _
" UNION ALL SELECT 5 FROM [Sequence] UNION ALL SELECT" & _
" 6 FROM [Sequence] UNION ALL SELECT 7 FROM [Sequence]" & _
" UNION ALL SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
" 9 FROM [Sequence] ) AS Digits ) AS Thousands;"
.Execute sql
End With
End Sub
Then use the Sequence table to enumerate the values from 1 to 42000 and build rows in one INSERT INTO..SELECT statement:
Sub TestInerts_Sequence()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim timer As CPerformanceTimer
Set timer = New CPerformanceTimer
timer.StartTimer
.Execute "INSERT INTO TestAB (a_col, b_col) " & _
"SELECT seq, seq " & _
"FROM Sequence " & _
"WHERE seq BETWEEN 1 AND 4200;"
Debug.Print "Sequence = " & timer.GetTimeSeconds
End With
End Sub
This runs on my machine in 0.2 seconds!
a source to share
Later versions of Access support the @@ IDENTITY variable. You can use this to get the id column after insertion without making a query.
INSERT INTO mytable (field1,field2) VALUES (val1,val2);
SELECT @@IDENTITY;
See this Knowledge Base article .
a source to share
we insert a row and then add another query to get it auto-increment ID; then use that id to insert a few more lines, linking them to the first
Is it one table, two tables, or more than two tables?
If one table, you might consider a different design, for example. you can create your own random IDs, use a nested set model rather than an adjacency list model, etc. It's hard to know if you share your design; -)
If there are two tables, assuming there is a table FOREIGN KEY
between the column tables AUTOINCREMENT/IDENTITY
, you can create VIEW
that INNER JOIN
two tables and INSERT INTO
VIEW
and the AUTOINCREMENT/IDENTITY
value will be copied to the reference table. More details and a working example in this Stack Overflow answer (link below).
If there is more than one table, the trick VIEW
does not extend beyond the two tables AFAIK, so you just have to live with poor performance or change technology, for example. DAO is reported faster than ADO, SQL Server can be faster than ACE / Jet, etc. Again, feel free to share your design, there may be a “think outside the box” solution.
a source to share
In your situation, it is best to use ADO to perform the insert instead of running the SQL command.
for i = 1 to 100
rs.AddNew
rs("fieldOne") = "value1"
rs("fieldOne") = "value2"
rs.Update
id = rs("uniqueIdColumn")
'do stuff with id...
next
I know using ADO is slow, but many times faster than opening 4200 connections ...
a source to share
Some suggestion (which can even be combined):
- Why don't you get the autoincrement ID before inserting the row, so you already have the value available for your next query? This one should spare you some time.
- Closing / opening a connection also needs to be checked.
- Have you thought about manipulating recordsets (using Visual Basic code to update your data) instead of tables (with SQL statements sent to databases)?
- Another solution (if you are sure that the connection is the bottleneck) would be to create the table and then export it to an access file after the job is done.
- When you use ADO, you can even save the data as an XML file, load the recordset from that XML file, manipulate it with VBA, and export it to an Access database
a source to share
The cheapest and (in most cases every time) the best speed boost you can provide to the database is to store the non-volatile data in the cache.
I don't know if it can apply to you, but it's very simple. You can simply plug in a (free) library that does this, or keep a local collection of items that have been read. Example:
- You want to read element X.
- Is there an X item in the local collection?
- No: read item X from the database and put it in your local collection.
- Yes: just return a local copy of the X element.
Of course, this can be a little tricky when you are running a website with a lot of servers. In this case, juste use Application Blocks or Cached.
a source to share
Let me refute the following statements:
the SELECT @@ IDENTITY clause is definitely a much faster way to insert data and retrieve an Autonumber value than AddOnly opening a recordset, updating fields, saving a record, and saving an Autonumber value. SQL INSERT will always be faster than using row by row of records.
I thought that the two approaches to recordset might be slightly faster than the @@ IDENTITY approach because I suspected it involved fewer database calls. In my testing, this was much faster by 1.2 seconds, compared to 127 seconds for the @@ IDENTITY approach. Here's my code (the code for creating the .mdb posted in another answer):
Sub TestInerts_rs()
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
Dim timer As CPerformanceTimer
Set timer = New CPerformanceTimer
timer.StartTimer
Dim rs1
Set rs1 = CreateObject("ADODB.Recordset")
With rs1
.ActiveConnection = con
.CursorType = 1 ' keyset
.LockType = 3 ' optimistic
.Source = "SELECT a_col, ID FROM TableA;"
.Open
End With
Dim rs2
Set rs2 = CreateObject("ADODB.Recordset")
With rs2
.ActiveConnection = con
.CursorType = 1 ' keyset
.LockType = 3 ' optimistic
.Source = "SELECT b_col, ID FROM TableB;"
.Open
End With
Dim counter As Long
For counter = 1 To 4200
rs1.AddNew "a_col", counter
Dim identity As Long
identity = rs1.Fields("ID").value
rs2.AddNew Array(0, 1), Array(counter, identity)
Next
Debug.Print "rs = " & timer.GetTimeSeconds
End Sub
a source to share
Some thoughts that may or may not be helpful:
-
Let me repeat the SELECT @@ IDENTITY clause - by far a much faster way to insert data and get the Autonumber value than to open the AddOnly Recordset, update the fields, save the record, and store the Autonumber value. SQL INSERT will always be faster than using a rowset of records.
-
using DAO transactions, you can convert multiple such inserts into a batch that runs at once. I am not sure about this as I do not know exactly what you are doing and how many tables you are using. The point is that you run a series of SQL INSERTs in a transaction and then do a .Commit at the end, so the actual write to the actual database file will be done as a single operation, not as 4200 (or many, however) separate operations.
a source to share