Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Unanswered: SQL Server 2005 Very very slow compared to MS Access

    Hi,

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

    records.

    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

    database.

    Following is the configuration of both machines:

    Machine-SQL
    ----------
    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")
    conn.open 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 !!

    Thanks,
    Mahesh

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK, so you have spun your wheels quite a bit.

    1) Only applies if you are doing a lot of sorting, or joining
    2) Only applies if you are attempting to reduice write contention on the physical drive.
    3) Is just a best practice, and only gives any performance boost if you are paging memory a lot.
    4) Again, only gives you a performance boost if you have a lot of update activity
    5) Only helps if other applications are fighting over the physical memory (BTW, why in $deity's name did you install 32-bit windows and SQL Server on this machine?)
    6) Again, only helps if you have other applications running here that cause contention
    7) FINALLY, we get to something that can help.


    Now, my question for you. Who in $deity's name is going to look at a web page that has over 500,000 records on it? Does the query come with a where clause? Since you are trying to push several megabytes through the network, I would suggest you look over the network settings on these machines, and see what if any difference is there. I would expect to find the SQL Server machine running at 10Mb/Half, or Auto-Negotiate.

    EDIT: Just noticed you are not even displaying the 500K records. Just the count. Change the query to
    Code:
    sql = "Select count(*) from [MyTable]"
    and make sure you have at least one index on the table. Access implicitly puts some sort of indexing in place, if I recall.

  3. #3
    Join Date
    Feb 2009
    Posts
    7
    Thanks for your reply.

    No the query is without WHERE clause. I can understand that fetching half a million records in a page is almost impractical. But my question still stands...Why SQL server too slow compared to MS Access, despite the fact that SQL server is running on much more powerful machine.

    I need to check the network settings on both the machines. I will do and let you know.

    Thanks,
    Mahesh

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can tell you it is not the server.
    Run the query directly against the database, through Query Analyzer or Management Studio to see how long it takes.
    One possibility is that the connection you are using for MSAccess starts displaying records as soon as they start being returned, while the connection you are using for SQL Server may be waiting until all 500K records are transferred before displaying any of them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and if you continue to have problems, post the DDL for tables, any indexes you have on them, and the code for they query you are executing. Eleven times out of ten, this is where the problem occurs. Not in the places you outlined above.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2008
    Location
    India
    Posts
    55
    can u provide sp_configure query output plz
    Check even if u have 32 GB of physical RAM , it matters how much SQL server is allocated , i hope SQL is currently allocated only 2GB(Max memory settings) . Please revert

  7. #7
    Join Date
    Feb 2009
    Posts
    7
    First of all, I am not displaying any records. I am doing just the following:
    set rs = Server.CreateObject("ADODB.Recordset")
    sql = "Select * from [MyTable]"
    rs.Open sql,conn,3,3

    Response.Write rs.RecordCcount


    Also IF I run a query "SELECT * FROM [MyTable]", it takes around 36 second in SQL Server 2005 to display all the records. However, it just takes 2 or 3 seconds in MS Access.

    Also note that, I am aware that both MS Access and SQL Server start start displaying records even though the query is still executing.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by blindman
    I can tell you it is not the server.
    Run the query directly against the database, through Query Analyzer or Management Studio to see how long it takes.
    One possibility is that the connection you are using for MSAccess starts displaying records as soon as they start being returned, while the connection you are using for SQL Server may be waiting until all 500K records are transferred before displaying any of them.
    I have an ADP with Access on the Front end and SQL Server 2005 on the back end as the engine and I agree with Blindman it is not SQL, run select statement on sQL in the enterprise management and you will see what he means. Its not sql
    Last edited by desireemm; 03-02-09 at 19:36.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by mahesh.chavda
    First of all, I am not displaying any records. I am doing just the following:
    set rs = Server.CreateObject("ADODB.Recordset")
    sql = "Select * from [MyTable]"
    rs.Open sql,conn,3,3

    Response.Write rs.RecordCcount
    That's not a sensible thing to do. You are forcing every row to be returned to the client. If you naively port the same code from Access to SQL Server then I expect you will see performance degrade in many cases. Jet != SQL Server. Jet is a file sharing system for the desktop, which is an entirely different architecture and of course its performance characteristics are different.

    At the very least you should replace this query with "SELECT COUNT(*) FROM [MyTable]" and while you are at it, make it a stored procedure as well.

  10. #10
    Join Date
    Feb 2009
    Posts
    7
    Quote Originally Posted by dportas
    That's not a sensible thing to do. You are forcing every row to be returned to the client. If you naively port the same code from Access to SQL Server then I expect you will see performance degrade in many cases. Jet != SQL Server. Jet is a file sharing system for the desktop, which is an entirely different architecture and of course its performance characteristics are different.

    At the very least you should replace this query with "SELECT COUNT(*) FROM [MyTable]" and while you are at it, make it a stored procedure as well.
    This looks the most satisfying answer so far to me. Especially, this statement : "Jet != SQL Server. Jet is a file sharing system for the desktop, which is an entirely different architecture and of course its performance characteristics are different."

    But why should one use a system that is 9 times slower even after adding so many resources. I know SQL server 2005 offers wide range of functionalities and more reliability and data availability than MS Access. But I am adding resources to support SQL Server's demand to accomplish those features. But why should it be too slow for simple operations. That is something indigestible.
    Last edited by mahesh.chavda; 03-03-09 at 16:30.

  11. #11
    Join Date
    Feb 2009
    Posts
    7
    Out put of sp_configure query:
    -----------------------------------

    Name Minimum Maximum Config_Value run_value
    allow updates 0 1 0 0
    clr enabled 0 1 0 0
    cross db ownership chaining 0 1 0 0
    default language 0 9999 0 0
    max text repl size (B) 0 2147483647 65536 65536
    nested triggers 0 1 1 1
    remote access 0 1 1 1
    remote admin connections 0 1 0 0
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 600 600
    server trigger recursion 0 1 1 1
    show advanced options 0 1 0 0
    user options 0 32767 0 0

  12. #12
    Join Date
    Feb 2012
    Posts
    1

    Slow SQL Server Connection from MS SQL Server

    Still if you are facing the issue, please see blow resolution:
    Root Cause
    -------------------------------------------------------------------
    The issue which we were seeing on Win7 VDIs could be due to the Network hardware device connected with the machine. If TCP/IP scaling is not supported by the network device then the performance will be slow.

    Solution
    -------------------------------------------------------------------
    Disable auto tuning level of the TCP. Please follow below steps:
    1) Open command Prompt with admin right (Run as Admin)
    2) Type “netsh interface tcp set global autotuninglevel=disabled”
    3) After running above command restart the machine.

    For other information on this command, visit link “http://support.microsoft.com/kb/935400”

Posting Permissions

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