Stored procedure takes more than minute to run.

Question

Stored procedure takes more than minute to run.

savibp3

Right there with Babe

Points: 752
More actions
January 21, 2017 at 2:27 am

#320823

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.

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

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,

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

savibp3 Right there with Babe Points: 752 More actions · Answer 2

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,
Are the same parameter values passed in those instances?
Are the calls made from the same system?
Are the calling systems on the same network?
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.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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)