Re: Query performance MS-Access vs. SQL-server 2000

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 07:38:17 -0600

Try closing the recordset at the end of each iteration like the example
below. I ran your ADO test using VBScript and it ran in a couple of
seconds, which is the performance I would expect.

For J = 1 To 5000
    I = CLng(Rnd() * 2791680)
    Test_SQL.Open "SELECT * FROM Test WHERE ID=" & CStr(I) & ";", _
        SQLConn, adOpenStatic, adLockOptimistic
    Test_SQL.Close
Next J 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Marco Lorenz" <Marco Lorenz@discussions.microsoft.com> wrote in message 
news:0AD1E269-1B89-48A5-8EF4-FDEFD7BF58D7@microsoft.com...
>I am evaluating SQL server and therefore I made a benchmark comparing the
> search speed between 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.mdb")
> 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
>

Relevant Pages