none
Stored Procedure called from the application is slow (Java)

    Question

  • We have a stored procedure being called from our java web application.  It is very fast when run from the MS but takes 10x + slower when called from the app.  Also noticed that the IO is ridiculously high when called from the app.  Anyone here familiar with both sql and java enough to comment?
    ~ syi916 ~
    Thursday, November 12, 2009 10:26 PM

Answers

  • This is a typical case of parameter sniffing. Your application most likely runs with different SET options (set by the client API) and uses a different execution plan than the one created in SSMS. What happens is when your procedure is invoke the first time via your application is creates execution plan based on the parameters passed. However, this execution plan may not be good for another set of parameter, which can result in poor performance when executed with the other set of parameters. See the following for more details and different solutions:
    http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

    Here is more on the internals of plan caching and query plan reuse:
    http://technet.microsoft.com/en-us/library/cc966425.aspx
    Plamen Ratchev
    Friday, November 13, 2009 4:34 AM
  • Pretty shocking.... Can you perform the following actions to stabilize the sproc?

    Check for index fragmentation, REBUILD indexes if needed.

    Do the following change to the sproc: remap all input parms (eliminate parameter sniffing). Example:

    CREATE PROC sprocAlpha @pLastName varchar(32)
    AS
    DECLARE @LastName varchar(32)
    SET @LastName=@pLastName
    ......
    Retest.

    Let us know the results.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, November 13, 2009 3:57 AM

All replies

  • From the application
    RPC

     

     

    exec SomeProcedure N'9eb68ae5-56c6-4954-824b-d75bbc9cddc0',NULL,NULL,NULL,NULL,NULL,NULL
    cpu:20093 reads:480185 writes:0 duration:20655 start:2009-11-12 17:15:10.377 end:2009-11-12 17:15:31.047

    From SSMS
    SQL

    exec SomeProcedure  N'9eb68ae5-56c6-4954-824b-d75bbc9cddc0',NULL,NULL,NULL,NULL,NULL,NULL
    cpu:234 reads:8523 writes:0 duration:578 start:2009-11-12 17:15:43.207 end:2009-11-12 17:15:43.797


    They both only return 511 records.
    Thursday, November 12, 2009 11:24 PM
  • Judging from the fact that the IO count for RPC is 50x more than the IO count for batch... I made an assuption that it was using a bad execution plan.  Added WITH RECOMPILE and it seems to fix the problem.  Can someone explain why this is happening?


    ~ syi916 ~
    Thursday, November 12, 2009 11:39 PM
  • Pretty shocking.... Can you perform the following actions to stabilize the sproc?

    Check for index fragmentation, REBUILD indexes if needed.

    Do the following change to the sproc: remap all input parms (eliminate parameter sniffing). Example:

    CREATE PROC sprocAlpha @pLastName varchar(32)
    AS
    DECLARE @LastName varchar(32)
    SET @LastName=@pLastName
    ......
    Retest.

    Let us know the results.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, November 13, 2009 3:57 AM
  • This is a typical case of parameter sniffing. Your application most likely runs with different SET options (set by the client API) and uses a different execution plan than the one created in SSMS. What happens is when your procedure is invoke the first time via your application is creates execution plan based on the parameters passed. However, this execution plan may not be good for another set of parameter, which can result in poor performance when executed with the other set of parameters. See the following for more details and different solutions:
    http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

    Here is more on the internals of plan caching and query plan reuse:
    http://technet.microsoft.com/en-us/library/cc966425.aspx
    Plamen Ratchev
    Friday, November 13, 2009 4:34 AM
  • This is a typical case of parameter sniffing. Your application most likely runs with different SET options (set by the client API) and uses a different execution plan than the one created in SSMS. What happens is when your procedure is invoke the first time via your application is creates execution plan based on the parameters passed. However, this execution plan may not be good for another set of parameter, which can result in poor performance when executed with the other set of parameters. See the following for more details and different solutions:
    http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

    Here is more on the internals of plan caching and query plan reuse:
    http://technet.microsoft.com/en-us/library/cc966425.aspx
    Plamen Ratchev

    Any particular SET options in mind? I originally didn't consider parameter sniffing since the parameters being passed are the same in both places.  I have this problem in one other system and would love to avoid localizing all my parameters.
    ~ syi916 ~
    Friday, November 13, 2009 3:23 PM
  • If you read the article that I posted (http://technet.microsoft.com/en-us/library/cc966425.aspx) it has a section labeled "Factors that affect plan-reuse" and there is has a table that list all SET options affecting plan-reuse (like ANSI_NULLS, ANSI_PADDING, ARITHABORT, etc.). Many client APIs change those settings and the execution plan will be specific for the API calls. On the other side SSMS has different settings and a different plan is used, which results in the difference you noted initially. The parameter may be the same now, but maybe the plan from your API was created using different set of parameters, and that plan is not efficient for the current set. Read the other article I posted, localizing parameters is not the best option as then plan is generated based on statistics. You can use the OPTION RECOMPILE if on SQL Server 2005+ to recompile only a particular SQL statement.
    Plamen Ratchev
    Friday, November 13, 2009 3:35 PM
  • Hi,
    We are experiencing a very similar issue.  We have a stored proc that takes no arguments, that when run under SSMS takes 12:49 to complete, and then under a thin Java wrapper (using the JTDS sql server driver) takes 16:24 to complete. (The stored proc in question is a scaled-down version of a production task that I am investigating which is chewing up days when it only takes around 4 hours to complete in SSMS)

    I have run sql profiler, and found the following:
    Java app:
    CPU: 466,514   Reads: 142,478,387  Writes: 284,078   Duration: 983,796

    SSMS:
    CPU: 466,973   Reads: 142,440,401   Writes: 280,244   Duration: 769,851

    (Both with DBCC DROPCLEANBUFFERS run prior to profiling, and both produce the correct number of rows)

    I also tried running the Java app with the stored proc specifying WITH RECOMPILE, but there was little difference.  

    As the I/O looks similar and there are no params to the stored proc, it doesn't look to me like the same query plan problem that DogTog's problem seemed to be.  It basically looks like it does the same amount of work, but it just takes longer under the Java app. 

    As the amount of work looks similar (i.e. I/O) from the profile output, could they be using different plans?....Is there a way to tell?

    Does anyone have any ideas?....Or any further diagnostics I could try?

    Thanks in advance,

    -James

    Environment: 
    Server: SQL Server 2005/Windows 2003 Server
    Client :Java 1.6/ JTDS 1.2.1 SQL Server driver/ Windows XP Pro

    • Edited by James Bee Tuesday, November 24, 2009 1:01 PM added JTDS version
    Tuesday, November 24, 2009 1:00 PM
  • It turns out the different clients are given different query plans (!) even though this is a stored proc with no arguments.  I shall start a new thread if needbe on this topic and re-post here with a link to it.
    Wednesday, November 25, 2009 10:58 AM

  • Does anyone have any ideas?....Or any further diagnostics I could try?

    Thanks in advance,

    -James

    Environment: 
    Server: SQL Server 2005/Windows 2003 Server
    Client :Java 1.6/ JTDS 1.2.1 SQL Server driver/ Windows XP Pro

    What is @@Version?

    Can you fire up SQL Server Profiler again and execute from SSMS and Java.

    Post the exact captured scripts for both cases.



    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Tuesday, December 01, 2009 6:25 AM