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

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 05:59:01 -0800

I can not tell you anything about ms access, but about sql server, the
statement is being parse and compile every time you send it to sql server in
order to get a good execution plan. You can avoid this creating a command
object in ADO and setting the property PREPARED to TRUE (ADO will send a
compile version), also setting the LockType to ReadOnly because you are not
planning to do any change during the test. You can also create a stored
procedure in sql server with an input parameter and execute it from ADO using
the command object.

AMB

"Marco Lorenz" wrote:

> 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

  • RE: How to populate VBA Variables from ADODB.Connection Results?
    ... y As Integer 'Incremental counters to populate ... 'Find SQL Server data for the active user. ... "GetUserNameA" (ByVal lpBuffer As String, ... Dim UserName As String ...
    (microsoft.public.excel.programming)
  • Re: Programmatically changing a SQL view in a ADP
    ... Public Sub TestCreateView() ... Dim cn As ADODB.Connection ... Dim rst As ADODB.Recordset ... to alter the 'Select' statement of a SQL Server view from within a ADP ...
    (microsoft.public.access.modulesdaovba)
  • RE: Error in SQL Enterprise Manager after migrating a user & their WS
    ... it is a SQL Server related issue. ... server, the restoration of these Registry Keys is not something we support, ... Dim oServerGroups ...
    (microsoft.public.windows.server.migration)
  • Passing values to an SQL Server backend database using a form
    ... I am trying to pass values from a form to an SQL Server 2000 DB using the ... OpenRecordSet method and then the .AddNew function. ... Dim recA As DAO.Recordset ... updated", vbInformation, "Information Conflict" ...
    (microsoft.public.access.modulesdaovba)
  • Re: dsn-less connection
    ... Doug Steele, Microsoft Access MVP ... SQL Server login and was able to remove the specific dsn that's in the ... be possible to have it default to not Use Trusted Connection so it would ... Dim dbCurrent As Database ...
    (microsoft.public.access.security)
Loading