SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Function takes more than 2 hours to return a table..


SQL Function takes more than 2 hours to return a table..

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88500 Visits: 41130
sanjaytiwari31 (2/4/2013)
Can anybody tell me how to optimize this function or where is the problem.


Yes I can but you're not going to like it. The code cannot be optimized no matter how much you play with it because of the nested cursors. It would be a far better thing sack the code entirely and rewrite it from the ground up without the cursors.

Because of the length of the code, I've not done a serious deep dive on it but I agree with what has already been said... it looks like it could be resolved by a single relatively simple query across 4 tables. If no one in your shop can handle the conversion, your shop should get some hired help. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
k.srikalyan
k.srikalyan
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 89
Try to create a primary key/unique key on your table variable, and it is always better to go as a stored proc using temp tables and while loop instead of cursors and try building a clustered index on the temp table which will definitely improve your performance.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41167 Visits: 32666
k.srikalyan (2/6/2013)
Try to create a primary key/unique key on your table variable, and it is always better to go as a stored proc using temp tables and while loop instead of cursors and try building a clustered index on the temp table which will definitely improve your performance.


A while loop is a cursor. That's not a replacement for one.

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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
bobie
bobie
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
Not really looking in detail but
I think it should be a store procedure.
And I quite sure we can eliminated the cursor using temp table and update and set variables in a single query.



Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40424 Visits: 38567
k.srikalyan (2/6/2013)
Try to create a primary key/unique key on your table variable, and it is always better to go as a stored proc using temp tables and while loop instead of cursors and try building a clustered index on the temp table which will definitely improve your performance.


While loops are not all that better than cursors, it is still RBAR.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88500 Visits: 41130
k.srikalyan (2/6/2013)
Try to create a primary key/unique key on your table variable, and it is always better to go as a stored proc using temp tables and while loop instead of cursors and try building a clustered index on the temp table which will definitely improve your performance.


bobie (2/6/2013)
Not really looking in detail but
I think it should be a store procedure.
And I quite sure we can eliminated the cursor using temp table and update and set variables in a single query.


Absolutely not. Temp table/While loop replacements for cursors is like smoking a pipe to quit cigars. They're essential identical and a well written firehose cursor (read only, forward only) is frequently faster than the Temp Table/While loop combination. To wit, replacing cursors with While Loops does little to help the situation and will sometimes cause even worse performance because most people aren't good at writing either. ;-)

Please don't make such recommendations in the future because it's a myth and a general bad recommedation to replace cursors with Temp Tables/While loops.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
bobie
bobie
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 24
What I mean is not a combination temp table and while loop but temp table with an bulk update statement with help of variable, is it the same as while loop?
Because if I check the statistics io and the time needed to execute it is much2 lower io and faster.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88500 Visits: 41130
ChrisM@Work (2/5/2013)
Steven Willis (2/5/2013)
Just a generic observation...

If this function is taking two hours to run--even with all of the cursors and subcursors--then you are probably accessing a LOT of data. I don't think a function is even appropriate. Is this being called by another procedure or query? If so the issues raised are multiplied by how many times the function itself is being called.

You should write a procedure instead. ..


Not necessarily. A properly written iTVF inlines like a view. This function is just a 4-table query with a few sums in it. I can't see any reason why it shouldn't be written as an iTVF regardless of how many rows the base tables contain.


+100!

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88500 Visits: 41130
bobie (2/7/2013)
What I mean is not a combination temp table and while loop but temp table with an bulk update statement with help of variable, is it the same as while loop?
Because if I check the statistics io and the time needed to execute it is much2 lower io and faster.


Ah! Understood. Yes. Doing a "Divid'n'conquer" using a Temp Table to hold interim results during "bulk updates" is much better than using cursors or while loops. Of course, you can't use Temp Tables in functions and Table Variables usually aren't the thing to use when a lot of rows are present.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search