Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: MSDE running much slower than Access dB??

    I have a large VB 6.0 application running with Jet 4.0 Access DB. I am considering moving the DB to MSDE. For testing, I installed MSDE on another computer in a peer to peer network running XP sp1, loaded up the DB from Access to MSDE2000A -- all without problem. (I should add the obvious fact I am new to using SQL server.)

    What I find is that with a relatively small test DB, running just a single instance of the application, query response from MSDE is taking several seconds (4-5 second lag) longer than response from the Access DB, which runs extremely fast. This is with the Access DB installed on the same network drive, running the same application and the same queries syntax -- only changing configuration of connection for each (SQL vs Jet 4).

    Any clues as to what may be going on? The lag time is unacceptable. I am using SQL password instead of NT security. It seems the process is perhaps lagging in the process of authorization.

    Thanks for any suggestions or ideas on this.

  2. #2
    Join Date
    Feb 2009

    I am having a problem with SQL server 2005, in that it is very slow compared to MS Access Database when fetching


    We migrated our ASP application to point use SQL Server 2005 as a back end. Earlier the application was using MS

    Access database. To our surprise a simple operation of opening a table and fetching records through recordset is

    very very slow in SQL server 2005 when compared it with MS Access database.

    We have two machines. 1) Machine-SQL having SQL server 2005 installed 2) Machine-MS Access having MS Access


    Following is the configuration of both machines:

    16 processors
    32 GB RAM
    3 Tera byte hard disk (approx)
    X64 processor
    OS: 32 bit Windows Server 2003
    32 bit SQL Server 2005

    Machine-MS Access
    4 processor
    4 GB RAM
    x86 processor
    OS: Windows server 2003
    800 GB Hard disk (Approx)

    Looking at the configuration, you can say that the machine, on which SQL server 2005 is running, is very very high

    configured and much more powerful compared to the machine on which MS access db resides.

    I am just testing the following thing on both the machines:

    ASP page code snippet:

    'Connection string to connect to MS Access database
    ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
    "DBQ=MyDatabase.mdb;UID=userId; PWD=password"

    'Connection string to connect to SQL Server 2005 database
    ConnectionString = "Driver={SQL Server}; Server=<Server Name>; Database=MyDatabase; Uid=UserID; Pwd=Password;"

    Set conn = Server.CreateObject("ADODB.Connection") ConnectionString

    set rs = Server.CreateObject("ADODB.Recordset")
    sql = "Select * from [MyTable]"
    rs.Open sql,conn,3,3

    Response.Write rs.RecordCcount

    The table in the above Database (MyTable) contains half a million records.

    Now I try to browse to ASP page having the above code first by connecting to MS Access database and second time

    connecting to SQL Server 2005 db.

    With the above machines, ASP Page that uses MS Access database takes roughly around 12 to 13 seconds to get rendered

    completely on the browser, while the page using SQL Server 2005 takes roughly 116 seconds.

    I tried a lot of things on SQL Server 2005 database like:
    1) Increased the initial size of tempdb database.
    2) Separating .mdf and .ldf files on different logical partitions
    3) Installing SQL Server 2005 on non-system drive
    4) Used Transaction isolation level as Read Uncommitted
    5) In SQL Server 2005 settings set Min memory to 2GB
    6) Boosted SQL Server priority in settings, though nothing is running on that machine except SQL Server 2005. This

    is a fresh new machine.
    7) Converted all my NVARCHAR data types to VARCHAR
    8) tried using "SQL Native Client" as ÖDBC driver instead of "SQl Server"

    My question is why such a simple query is almost 9 times slower on very very powerfull machine in SQL SErver 2005

    compared to MS Access database which is residing on comparatively low-configured machine???????

    Your help is appreciated !!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts