-
Performance Access vs SQL-2000
I am evaluating SQL server and therefore I made a benchmark comparing the search speed betwenn Access (which we are using up to now) and SQL-Server.
I created 2 identical tables on the same Server, once as Access database file, once in SQL-Server with one Counter as Primary Key and Index and one string*255
The tables have 2.700.000 entries each.
Then I benchmarked 5000 arbitrary searches on both tables
The results of this benchmark are so strange and surprising, so I would like to find out, if there is a major error in my application!!!
Searching the MS-Access table takes about 3 seconds
!!!!!!!!Searching the table with SQL server takes 60-70 seconds!!!!!!!!!!
The primary index in the SQL-table is existing and working, I checked with the SQL profiler and the index optimizer wizard.
Am I doing anything absolutely wrong?????
I would be happy for any hints.
I am using this code:
Alternative 1 with MS-Access and DAO:
Dim Test_DB As DAO.Recordset, DB As Database
Dim I as Long, J as LongSet DB = DBEngine.Workspaces(0).OpenDatabase("G:\CADBDat.md b")
Set Test_DB = DB.OpenRecordset("Test", DB_OPEN_TABLE)
Test_DB.Index = "PrimaryKey"
For J = 1 To 5000
I = CLng(Rnd() * 2791680)
Test_DB.Seek "=", I
Next J
Alternative 2 with SQL-Server and ADO:
Dim SQLConn As New ADODB.Connection
SQLConn.ConnectionTimeout = 25
SQLConn.Provider = "sqloledb"
SQLConn.Properties("Data Source").Value = "W2SERVER"
SQLConn.Properties("Initial Catalog").Value = "CADBase" SQLConn.Properties("Integrated Security").Value = "SSPI"
SQLConn.Open
Dim Test_SQL As New ADODB.Recordset
For J = 1 To 5000
I = CLng(Rnd() * 2791680)
Test_SQL.Open "SELECT * FROM Test WHERE ID=" & CStr(I) & ";", SQLConn, adOpenStatic, adLockOptimistic
Next J
-
So many factors affects your benchmark test. Check some of the factors like
1. Is it clustered index or nonclustered?
2. Did you update the statistics for that table?
3. Create a procedure like below to access the table
Create procedure usp_getval @val1 int
as
SELECT * FROM Test WHERE ID=@val1
4. SQL Server can handle volumes of connections. So did you try accessing the table in multi-threaded mode?
5. Did you see the execution plan for this statement? Does SQL Server uses the right index?
6. Proximity: How close is you MS-Access database to your client app? Ho close is your sql server box.
7. Why you have to convert the value to a string before executing the SQL Statement - CStr(I)
That takes some time at the client side
-
Hi MAK,
thanks for the hints.
1. The index I am using in SQL is clustered (Primary Key)
2. How can I update the statistics? The table has just been created before the benchmark
3. Being a Newbie in SQL-programming: Could You give me some more info how to create and use the stored procedure?
4. How can I access the table in multithreaded mode?
5. I checked the operations with profiler and Index Optimizer, everything seems OK. It is using the index for sure, because if I try to seek for another (unindexed) field, everything works a lot slower.
6. The MS-Access database is located on the same server, as SQL-Server, all in a small network
7. I thought, I have to convert the Integer to a string in order to get the SQL-command-string. But You are right: the string conversion takes approx. 15 seconds for the 5000 searches - so there are only 45-55 more seconds missing as a difference.
In the meantime I learned, that the dynamic SQL statement needs compilation time every time, this may be the main difference between the two alternatives. Therefore I would like to test with a stored procedure - if You could give me some help on this?
Thanks,
Marco
-
1. It's not clustered on nonclustered index issue, you should created indexes on proper columns.
2. Check 'How to create statistics (Query Analyzer)' in books online.
3. Check 'CREATE PROCEDURE' in books online.
4. Make multiple connections to sql and query same table.
-
The problem is that your tests are not equivalent!!
For the Access test, you are opening the table ONE TIME and merely seeking specific records within that SINGLE recordset.
For the SQL Server test, you are running 5000 queries and opening 5000 recordsets!!! Which, by the way, you're not closing, so you end up with 5000 open recordsets once the loop is finished!!!
See the difference?
-
Sorry, no. I forgot to paste the Test_SQL.close statement in the sample code. Without this statement I would get an error, since I am not allowed to open a recordset which is already opened.
The task is to find 5000 arbitrary records in a big database. In SQL-server the only way to do this is running 5000 queries - or is there any other alternative???
Changing the static SQL statements to a stored procedure did not really make a big difference.
I changed the benchmark to make it a bit more realistic:
I did a query returning 1000 datasets. Then I scanned through the 1000 Records evaluating some field. This procedure I looped 200 times with arbitrary query criteria.
I tested alternatively with DAO and ADO (using Jet) on the same access database on a network server and also on SQL server (same as the file server)
DAO result: 14-15 seconds (DB_OPEN_DYNASET)
DAO result: 20seconds (DB_OPEN_SNAPSHOT)
ADO result: 12-13 seconds (Server cursor)
ADO result 18 seconds (client cursor)
SQL result: 10 seconds (client cursor, which in this case means Read only)
SQL result 80 seconds (Server cursor adLockOptimistic)
SQL result 70 seconds (Server Cursor, adLockReadonly)
So this kind of benchmark says:
DAO and ADO are more or less equal in the performance on a Access database
On SQL with the local cursor read only queries may be obtained very fast with the local cursor
SQL with a read/write option take about 5-8x longer than anything else, even with little SQL-translation overhead
Additionally I found out, that using DB_OPEN_DYNASET with DAO is considerably faster, than DB_OPEN_SNAPSHOT - just the opposite, which may be expected...
For performant database applications this means quite a lot of optimizing work in the code changing to SQL server - and it means, that searching and editing single records will be considerably slower on the SQL server. Surprising for me, anyway
-
Originally posted by Marco Lorenz
The task is to find 5000 arbitrary records in a big database. In SQL-server the only way to do this is running 5000 queries - or is there any other alternative???
Either method/query that you are using will work in either Access or SQL Server.
Until you run identical tests on both databases, your results don't mean a whole lot.
-
@Rawhide: the last tests i DID with identical procedures on both databases. Especially the results between ADO on the MS-Access database and SQL on the SQL server are absolutely identical, since for both tests the ADO library was used and therefore the statements may be the same. For a test with DAO ofcourse I changed the code, but not the type of query. So the results ARE comparable.
-
Can you post the queries you used?
-
Sure.
DAO test on MS-Access database:
' 14-15 sec for DYNASET
' 20 sec for SNAPSHOT
-------------------------------
Set DB = DBEngine.Workspaces(0).OpenDatabase("G:\CADBDat.md b")
For J = 1 To 200
I = CLng(Rnd() * (2791680 - 1000))
Dim Krit As String
Krit = "SELECT * FROM Test WHERE Nummer>=" & CStr(I) & " AND Nummer <" & CStr(I + 1000) & ";"
Set Test_DB = DB.OpenRecordset(Krit, DB_OPEN_SNAPSHOT)
If Test_DB.EOF Then
MsgBox "record not found"
End If
Do Until Test_DB.EOF
Diff = Test_DB!Nummer
Test_DB.MoveNext
Loop
If Test_DB.RecordCount <> 1000 Then
MsgBox "wrong number of records"
End If
Test_DB.Close
Next J
ADO test on MS-Access database:
' 12-13 sec (adUseServer)
' 18 sec (adUseClient)
-------------------------------
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='" & "G:\CADBDat.mdb" & " ';"
Cnxn.Open strCnxn
Dim Test_ADO As New ADODB.Recordset
Test_ADO.CursorLocation = adUseClient ' adUseServer
For J = 1 To 200
I = CLng(Rnd() * (2791680 - 1000))
Dim Krit As String
Krit = "SELECT * FROM Test WHERE Nummer>=" & CStr(I) & " AND Nummer <" & CStr(I + 1000) & ";"
Test_ADO.Open Krit, Cnxn, adOpenStatic, adLockOptimistic
If Test_ADO.EOF Then
MsgBox "record not found"
End If
Do Until Test_ADO.EOF
Diff = Test_ADO!Nummer
Test_ADO.MoveNext
Loop
If Test_ADO.RecordCount <> 1000 Then
MsgBox "wrong number of records"
End If
Test_ADO.Close
Next J
ADO test on SQL server:
' 10 sec (client cursor->Read only)
' 80 seconds (Server cursor adLockOptimistic)
' 70 seconds (Server Cursor, adLockReadonly)
-------------------------------
Dim SQLConn As New ADODB.Connection
Dim Test_SQL As New ADODB.Recordset
SQLConn.ConnectionTimeout = 25 SQLConn.Provider "sqloledb"
SQLConn.Properties("Data SSource").Value = "W2SERVER"
SQLConn.Properties("Initial Catalog").Value= "CADBase" SQLConn.Properties("Integrated Security").Value = "SSPI" SQLConn.Open
Test_SQL.CursorLocation = adUseServer ' adUseClient
For J = 1 To 200
I = CLng(Rnd() * (2791680 - 1000))
Dim Krit As String
Krit = "SELECT * FROM Test WHERE Nummer>=" & CStr(I) & " AND Nummer <" & CStr(I + 1000) & ";"
Test_SQL.Open Krit, Cnxn, adOpenStatic, adLockOptimistic ' adLockReadOnly
If Test_SQL.EOF Then
MsgBox "record not found"
End If
Do Until Test_SQL.EOF
Diff = Test_SQL!Nummer
Test_SQL.MoveNext
Loop
If Test_SQL.RecordCount <> 1000 Then
MsgBox "wrong number of records"
End If
Test_SQL.Close
Next J
-
Hi,
use Teratrax performance monitor to find out what happens when you run the sql 2000 code. look for various hardware events and also monitor connections before and after the code runs.
Teratrax Performance Monitor for SQL Server
-
It shouldn't really be a surprise. Access is usually faster when you are testing on same server environments. If you are talking about a single user on the same workstation/server, Access almost always spanks SQL. SQL gets faster when you have lots of concurrent connections, multiple processors, and remote processing. Making those stored procedures will also help a lot. When you pass the raw string into SQL like that, it first has to make a temp execution plan and (to over simplify) compile your SQL statement. That time adds up fast. Add to that the latency of talking over the network interface, when access goes right to the disk (which the file is most likely completely cached in ram anyway) and Access is going to stomp SQL every time.
-
Hello, JCrowley,
clear, if I worked on my local machine.
But the comparison was with databases both on the same network server. So I thought, the result was surprising, because Access just uses the file system so search in a Access.mdb, whereas SQL server should hold the tables and just return the SQL results to the client.
Anyway, it looks like Access is better than I thought...
Marco
-
It seems to me, MSACCESS is the best LOCAL database.
I've compared MSAccess97, MSSqlServer2k, Oracle 9i, MySqlServer. 10 tables 10 million records each , non-indexed fields, indexed fields, LIKE search... string, integer and real fields. Also, i've tested open/close and rs.movefirst rs.movelast DAO and ADO recordsets.
The compozite result was:
Local tests
1.Oracle
2.MsAccess
3.MySql
4.MSSqlServer
Multi-User (10-50):
1.Oracle
2.MySql
3.MSSqlServer
4.MSAccess
Multi-user (100-300):
1.Oracle
2.MSSqlServer
3.MySql
4.MSAccess
You get, what you pay for =)
Only one remark - Oracle was configured by professional certified Oracle Administrator. When i configured the Oracle by my own, ACCESS in local tests grows in 1-st place =).
-
Hello, VERESK,
Thanks for the information.
One more question: How were the results or Your comparison between ADO and DAO inside MS-Access? I notices differences so big, that I am doubting my own results. DAO was a lot faster than ADO
Marco
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|