Stored procedure takes more than minute to run.

  • I have stored procedure which takes more than a min to run in sql browser.when i call from java UI it times out.It has group by and order by clause.Should the group by have same number of columns as order by?? to fix.

  • savibp3 - Saturday, January 21, 2017 2:27 AM

    I have stored procedure which takes more than a min to run in sql browser.when i call from java UI it times out.It has group by and order by clause.Should the group by have same number of columns as order by?? to fix.

    You will have to share much more detailed information on the procedure, the data set, indices, preferably the actual execution plan(s) etc. 
    😎

    Further,

    1. Are the same parameter values passed in those instances?
    2. Are the calls made from the same system?
    3. Are the calling systems on the same network?
    4. Are the result sets identical?

  • Eirikur Eiriksson - Saturday, January 21, 2017 2:47 AM

    savibp3 - Saturday, January 21, 2017 2:27 AM

    I have stored procedure which takes more than a min to run in sql browser.when i call from java UI it times out.It has group by and order by clause.Should the group by have same number of columns as order by?? to fix.

    You will have to share much more detailed information on the procedure, the data set, indices, preferably the actual execution plan(s) etc. 
    😎

    Further,

    1. Are the same parameter values passed in those instances?
    2. Are the calls made from the same system?
    3. Are the calling systems on the same network?
    4. Are the result sets identical?

    I have temp table created in the procedure and  variables declared.Data is passed to variables from output of other procedure.These variable are used in case statements within select statement.Select statement has lost of joins and group clause which in turn has multiple case statements and order by clause.the select statement is used to insert data into temp table. and later we are updating the temp table.
    summary of data is the result set. 

  • The only way to know the answer to your question is to take a look at the execution plan in order to understand the choices being made by the optimizer. If you post the plan here, we can help out. Otherwise, all I can do is suggest common code smells to avoid. Don't have functions on columns used in WHERE, ON or HAVING clauses. Be sure your statistics are up to date. Stuff like that.

    "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

  • If you want help on performance, please read and heed the article at the second link under "Helpful Links" in my signature line below.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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