Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Slow to connect on live pages.

    Hi,

    Ive been running a ms sql 2000 database on a 1gz, 512mb ram machine for several months with no real problem.

    However lately its been taking upwards of 2 seconds just to connect...

    Im on windows 2003 with iis 6 and connecting like so:

    SET MyConn = Server.CreateObject("ADODB.Connection")
    MyConn.Open "Provider=SQLOLEDB.1;UID=x;Password=x;Initial Catalog=x;Data Source=127.0.0.1"

    Most queries are opening in a matter of milliseconds, how ever the occasional one will take upto a second to complete. The queries are very simple update and select statements. (UPDATE Stats SET Page_Views = Page_Views + 1, Page_Views_Daily = Page_Views_Daily + 1) etc...

    Any ideas why is taking so long?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you run UPDATE STATISTICS against your database tables? What other maintenance is being done?

    Many times new databases slow down after use because as their tables fill up the original query execution plans are no longer the most efficient execution plans.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    I have run update statistics a few hours ago, but it doesn't appear to have made any difference. (No maintance has been done on the server since it was installed)

    On a page with no queries just a connection, its still taking about 1.5 seconds to execute.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry if these questions are a little basic, but how much has this database grown in the past few months? Was it empty when you started?

    Do you have indexes on your tables?

    Are you backing up the database and truncating the transaction log?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Doesn't sound a like a server (IIS) problem, sounds more like a coding deficiency or a slow connection between the access database server and the web application server assuming they're on different servers.

    http://www.winnetmag.com/Article/Art...ows_26260.html -optimizing slow connections.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Mar 2004
    Posts
    3
    I dont know what indexes are so im assuming I dont have them... I'll have a look into it.

    The database and iis are located on the same machine.

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    During execution of slow queries can take help of PROFILER and submit to Index tuning wizard for index recommendations.
    Keeping IIS & SQL On same machine might have performance issues.

    Also collect Hardware & SQL counters using PERFMON and I suspect this might encountering Paging problems.

    The best solution to your paging problem is to either add more RAM to your server, or even better yet, use two servers, each with an appropriate amount of physical RAM.

    If your database should get bigger, or your website busier, you may have to adjust the maximum amount of RAM you have assigned SQL Server again because of changed circumstances. This is one of the disadvantages of not using SQL Server dynamic memory allocation.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you do not have indexes defined on the columns involved in your joins, then that should be the first thing you do to try to increase execution speed. Indexes would not make a difference on small tables, but as the number of records grows they become critical to query efficient.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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