Post reply

Query slower via Management Studio?

  • April 14, 2009 at 1:34 am

    #384353

    A developer here has a query that when they run it from PHP takes 20s.

    When I run it from Management Studio it takes 1m 7s.

    Any ideas where I start?

    I read that Management Studio might not be utilising all processors.

    I'm not interested in pasting/debugging the query (i know we have the data model from hell and am working to convince people of that). I merely need some ideas to ascertain why the query takes longer from Management Studio.

    r

  • April 14, 2009 at 4:45 am

    #977111

    Query Time = Time that server needs run the query + Time that client needs to read the result.

    Management studio need long time to read and display result.

  • April 14, 2009 at 5:07 am

    #977123

    is the query using a linked server in SSMS to go to a MySQL Database used by PHP database?

    Is PHP Connecting to a SQL2005 database? I know PHP with MySQL is common.

    i would not be surprised if that was the issue, since the linked server is just stored connection info, that is used on demand;I know my Oracle Linked server is slower than using SQLPlus from the same machine.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 14, 2009 at 7:38 am

    #977201

    If you're running a query through a linked server, most of the time, all the data is moved across the wire first, then filters & joins are done to extract the appropriate data set on the SQL Server machine. That's one of the reasons linked servers run so slow. We use OPENQUERY with our linked servers so that the work of the query is done on the Oracle system and only the result set is returned to SQL Server. When we need to use that in joins, etc., we load it into a temp table first.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 14, 2009 at 7:40 am

    #977203

    valuable info Grant; i did not know that about linked servers vs openquery... Thanks

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 14, 2009 at 7:51 am

    #977219

    Check if you have the same set options in the SSMS window as the application has. Some of the set options can produce a different query plan.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

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