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

From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 09:02:20 -0500

In addition to Dan's advice I would encourage you to watch the activity
against SQL using Profiler. You'll find that Access sometimes doesn't issue
the queries you think it is doing. 

-- 
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"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

  • Re: SQL Express vs. Jet
    ... > Wieso mit Access? ... Der SQL Server greift auf eine simple *.mdf zu:-) ... Dim dadp1 As New OleDb.OleDbDataAdapter ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • Re: How can a datatable be written to a new SQL Server table?
    ... > a SQL Server from where it can be manipulated. ... > created SQL Server table, (this has been apparently successful), ... > Dim blnRVal As Boolean = False ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Vb newb needs help on data import/export
    ... There is a data import thru the SQL Server. ... It is launched form the same SQL Server Management Studio database ... Dim cnConn As New OleDbConnection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Using QueryDefs for Calling Stored Procedures on SQL Server
    ... "Hemil" wrote in message ... > to run a stored procedure on SQL Server from Access 97 and uses QueryDefs ... > Dim Mydb As Database ...
    (microsoft.public.access.modulesdaovba)