dcsimg
Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Performance Access vs SQL-2000

  1. #1
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9

    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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    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?

  6. #6
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    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

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  8. #8
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    @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.

  9. #9
    Join Date
    Feb 2003
    Posts
    1,048
    Can you post the queries you used?

  10. #10
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    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

  11. #11
    Join Date
    Jan 2005
    Posts
    2
    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

  12. #12
    Join Date
    Feb 2005
    Location
    Rochester NY
    Posts
    1
    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.

  13. #13
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    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

  14. #14
    Join Date
    Feb 2005
    Posts
    14
    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 =).

  15. #15
    Join Date
    Jan 2005
    Location
    Germany
    Posts
    9
    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
  •