Post reply

Queries very slow

  • May 9, 2005 at 10:01 am

    #88391

    I have just upsized an Access database to SQL Server.
     
    I have 2 identical sites on 2 different servers (1 live site, 1 test site) and both are currently using the same SQL database. Both sites are running fine with no errors, but the problem is the live site is unreasonably slow while the test site is lightning fast. This is proving a real problem and I'm in danger of losing some business so if anyone can provide any help asap I'd be very grateful.
    All the pages which access the database on the live site are slow, and have been for a few days (even during hours of minimal activity). It's true the demo site won't have anything near the activity of the live site, but I wouldn't expect such a difference in speed when accessing the same database using the same code.

    At the moment the database is very small (only about 3Mb). There are around 15 tables and all the queries are run either dynamically in the code (ASP) or using Views (I'm currently writing Stored Procedures to replace come of these). I'm not currently sure of precise stats for server load but will find out if need be.

    The reason I upsized from Access was due to the concurrent user limitations, but as it stands Access was a lot faster.

    The demo server is actually hosted by the same host who provide the SQL database so there is presumably on the same network. The live site is hosted by a different host so is obviously not on the same network. Do you think this would be the problem?

    AUTOCLOSE is not selected, neither is AUTOSHRINK.

    Thanks for any help.

  • May 9, 2005 at 10:04 am

    #557643

    Is there any indexes on the live db?

  • May 9, 2005 at 10:12 am

    #557645

    Yes, there are indexes in most of the tables (id columns) - these were upsized from the Access database which were originally Autonumber columns.

  • May 9, 2005 at 11:15 am

    #557659

    Can you check if the execution plans are the same on each server?

    Could you also run the profiler to see which querie(s) take the most time to run?

  • May 9, 2005 at 2:04 pm

    #557698

    What are the specs of the various machines?

    What else is running on the machines? If the host is running WebTrends you may see a huge performance hit. WebTrends themselves say that their software should not be run simultaneously on a shared box.

    What is the connectivity between your web server and your SQL server?

    LinkedIn Profile

  • May 9, 2005 at 3:28 pm

    #557705

    I'm not 100% sure of the exact specs of the servers although they are all operating in a commercial hosting environment running Win2003. The SQL Server is on a dedicated server and is hosted by the same company as the server running the demo site and as such is on the same network. The live site is currently hosted by a different company and so is not on the same network as the SQL Server - could this be the reason for the performance hit? I didn't think it would make a great deal of difference as the connection string on both sites is obviously pointing at the same IP address.
     
    Thanks for your help.

  • May 9, 2005 at 3:54 pm

    #557707

    So if I understand you correctly you have two locations, for the sake of argument we'll call them demo and live.

    At demo you have

    * A web server

    * A SQL server

    At live you have a web server accessing the sql server at demo.

    If I am understanding you correctly this is probably why it is slow. I would have put your SQL server in the same network as the live web server and let the demo site suffer the slow queries.

    I am not an infrastructure expert but we tend to have a dedicated live SQL server and a dedicated live web server or servers. These are not only on the same network but on the same network segment and that segment has only those particular servers on it. We may even go to the expense of having an additional network card to allow those servers to talk directly to each other.

    On one of our sites there are 3 web server and 2 sql servers.

    Webserver 1 is for content authoring

    Database 1 services webserver 1

    Database 2 services webservers 2 & 3 which in turn serve the public.

    This means that if the content authors get the urge to publish War and Peace and the entire works of Doestovsky in 50 languages it won't affect the live site.

    Also, if the general public decide to read and search these worthy tomes then their activity won't impact on publishing.

    If your budget is very tight then I would consider running your demo site on MSDE as its limitations are adequate for demo purposes.

    LinkedIn Profile

  • May 9, 2005 at 4:39 pm

    #557713

    Thanks again for your reply - I think you've answered my question.
     
    The reason the live site is accessing the SQL Server at another location is because the site was originally set up on a cheap host running Access. Now the site is growing we are moving over to a new, more reliable host and have upsized the database to one of their SQL Servers. We are currently waiting for the domain to transfer hence the test site, but in retrospect I think I was a little hasty moving the database before the transfer was complete - I wasn't aware of this problem.
     
    Many thanks again - I was beginning to think it was my code so it's a relief in many ways.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply