SQL Server Performance

Stroed proc:Slow on SSMS and fast from application

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Hillside, Sep 21, 2009.

  1. Hillside New Member

    Stored procedure takes 50 mints if we run from SSMS(SQL Server 2005 Developer edition) and same store proc takes 2 mints from Java applcation where we use ODBC connection.
    Store proc does select statment on huge table(90 millions rows) with couple joins.
    Any idea ?
  2. moh_hassan20 New Member

    Is SSMS running on the same machine of java application , or on Server
    try both methods
    feed back the results with using
    set statistics io on
    set statistics time on
  3. satya Moderator

    What is the service pack level on SQL Server?
    Is that SSMS located or accessed from remote location?
  4. Hillside New Member

    The database is on different server which has SQL Server 2005 developer edition with SP2
    The java application and SSMS we running from Client PC which has same version as said above with SP2.
  5. dschaeff New Member

    If you're returning many rows network speed is a factor. When run from SSMS the query doesn't finish until all that data gets to the box you're running SSMS on, resulting in a high duration. Try inserting the results of the proc into a temp table; see how long that takes.
    It's also entirely possible that there's different query plans involved even if the SQL is identical because of different connection properties. Check the plan it uses when executed from SSMS. With that large number of rows, let's hope it's not using nested loops.

Share This Page