Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««3435363738

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction Expand / Collapse
Author
Message
Posted Saturday, June 26, 2010 10:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 19, 2015 11:18 AM
Points: 1,277, Visits: 1,150
ehaustin78737 (6/25/2010)
I agree with Brad Neufeld this article bites. Another condescending rant.


Everyone appreciates such specific criticism. It helps the author fine-tune his style, lists plenty of well-defined arguments that provoke thoughtful consideration, and rises far above the level of the "background noise" generated by (for instance) a YouTube "I Hate!" video.

I thank you on behalf of the author.

Mike C.
Post #943469
Posted Saturday, June 26, 2010 11:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 19, 2015 11:18 AM
Points: 1,277, Visits: 1,150
Nick Walton (6/25/2010)
Thomas-282729 (6/25/2010)

DBA: A. Why is it my problem that someone else did not do a better job of planning ahead? B. why are you pulling one record at a time instead of pulling a batch of records? C. Why not write a small app that batches and processes the data?


I agree, I've been there, so I can give you the responses.

a. Because you're holding the project up. It would be on-schedule if you didn't just let the developers write the code. there's no Right and Wrong answer. Just write an SP to do it, and the project can get delivered.


"...there's no Right and Wrong answer." --> I disagree with the "everyone gets a trophy" mentality.

I think a more complete assessment is that the "Right" SP that seems to run well today when your table has 1,000,000 rows is going to run terribly a year from now when it grows to 10,000,000 rows. It's going to push out your processing time by delaying other resource-intensive processes and cause you to miss your SLAs. The customer will complain and you'll go and try to optimize that cursor in the SP to shave off a couple of seconds.

The more accurate (...and just as satisfying) answer might be that there's no need to do it "Right" the first time because we can fix it later when it causes problems. Besides, the ongoing project maintenance costs come from a different budget.


b. We're not. We're batch processing the records, however each row requires handling in a different, and complex way depending on the data which 'hangs off' it. There are up to 300 different 'events' which can fire depending on the processing logic.


When you say 'events' which can fire depending on processing logic, SQL is not the development tool of choice that comes to mind. Rather I think of procedural languages that natively support user-defined event handling. It would be interesting to see the performance affects of a custom-built user-defined event-handling framework built completely in T-SQL.


c. that would require development time which we don't have. It wasn't specified *whinge whinge*


More than that this type of project generally requires planning time which many companies aren't willing to invest in. I've read studies by IBM and others that peg the cost of fixing broken solutions at 100's to 1000's of times the cost of doing it "Right" the first time, even calculating in the cost of proper planning and design. But many managers don't care since the code fixes are often hidden away in a different budget after delivery.


In defence of point c, The app option is a red herring anyway, cos it would be external to the DB, and would still have to pore through each row, do the very specific logic to each row, requiring the pulling-in of yet more data. The pull/process/push time would be rather extensive. Even with 1000 simultaneous connections (assuming the DB didn't fall over due to locking on the tables), it'd still take more than 10 times longer to complete that way, and would be yet another service to have to monitor, document, maintain, etc.


Some tasks, such as row-by-row processing, are often better done in other tools. SSIS and other ETL tools are prime examples of tools that are optimized to act on individual rows at a time.
Post #943472
Posted Saturday, June 26, 2010 12:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 37,391, Visits: 34,265
jeffwilson2 (6/25/2010)
Hi my name is Jeff.
I am new to this forum. About a year ago I started at a company and last year they upgraded to SQL 2005 and nearly every procedure uses a cursor, in some of the most bizarre circumstances. I have been trying to rewrite them as I have had to modifiy them, but some of them are so embedded in other processes or so long and confusing with cursors inside other cursors that I hesitate to do so.

But I have come across a scenario that i am at a loss on how to avoid a loop. And maybe this is an exception to the rule about using loops.
Any help or advice would be appreciated.

The purpose of the procedure is to back into the SDI tax rate when the total SDI Tax has exceeded it's limit, so the new net Tax value can be recalculated. It usally has to loop through the process 5-10 times to return.
See everytime the SDI tax rate changes the Total net tax changes. But SDI tax i based on the Net Total Tax.
So everytime you adjust the tax rate so that the SDI TAX is at the limit, then the NetTotal Tax changes and the SDI rate is not accurate, But every time you make the adjustment you get closer to the SDI tax.

I realize that this is circular logic, but it does retrun the accurate data because the SDI rate keeps getting closer and closer to the correct rate needed to set the SDI tax at its limit.


It's a "memory-only" loop and isn't likely going to be a performance problem (except that such loops can't be used in an iTVF). What will be a performance problem is the fact that it's a mlTVF (multi line table valued function) which can be nearly as slothful as a scalar function. My question would be how many times do you use the function in a given proc (ie... against how many rows)?

My other question would be... what happens if it "hits" the correct number on the 5th iteration but we make it run for 10 iterations? Will it still come in on the correct answer (not taking the time to analyze the algorithm just yet)?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #943483
Posted Saturday, June 26, 2010 3:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 19, 2015 11:18 AM
Points: 1,277, Visits: 1,150
jeffwilson2 (6/25/2010)
Hi my name is Jeff.
I am new to this forum. About a year ago I started at a company and last year they upgraded to SQL 2005 and nearly every procedure uses a cursor, in some of the most bizarre circumstances. I have been trying to rewrite them as I have had to modifiy them, but some of them are so embedded in other processes or so long and confusing with cursors inside other cursors that I hesitate to do so.

But I have come across a scenario that i am at a loss on how to avoid a loop. And maybe this is an exception to the rule about using loops.
Any help or advice would be appreciated.

The purpose of the procedure is to back into the SDI tax rate when the total SDI Tax has exceeded it's limit, so the new net Tax value can be recalculated. It usally has to loop through the process 5-10 times to return.
See everytime the SDI tax rate changes the Total net tax changes. But SDI tax i based on the Net Total Tax.
So everytime you adjust the tax rate so that the SDI TAX is at the limit, then the NetTotal Tax changes and the SDI rate is not accurate, But every time you make the adjustment you get closer to the SDI tax.

I realize that this is circular logic, but it does retrun the accurate data because the SDI rate keeps getting closer and closer to the correct rate needed to set the SDI tax at its limit.

Here is a function that I use a loop for.
CREATE  FUNCTION dbo.fn_TaxLimitCalcTable
(
@startVal decimal(25,18),
@LimitVal decimal(25,18),
@YTDVal decimal(25,18),
@NetBenefit decimal(25,18),
@Fed decimal(25,18),
@staterate decimal(25,18),
@Soc decimal(25,18),
@Med decimal(25,18),
@CurRate decimal(25,18)
)

RETURNS @TaxLimit TABLE
(
NewSDIRate decimal(25,18),
adNetval decimal(25,18),
adjustedval decimal(25,18),
Fed decimal(25,18),
Med decimal(25,18),
staterate decimal(25,18),
Soc decimal(25,18)
)


AS
/****************************************************

SP Name: fn_TaxLimitCalcTable

Description: This function re-calculates the associated tax and rates when
the maximum tax limit has been reached or exceeded.

****************************************************/

BEGIN

DECLARE @w2amt1 decimal(25,18), @NewSDIRate decimal(25,18),
@adjustedval decimal(25,18), @adNetval decimal(25,18),@Returned decimal(25,18),
@Fed1 decimal(25,18),@Med1 decimal(25,18), @staterate1 decimal(25,18),@Soc1 decimal(25,18)
SELECT @NewSDIRate = @CurRate,
@adjustedval=@startVal,
@adNetval=@NetBenefit*(1/(1-(@CurRate+@Fed+@staterate+@Soc+@Med))) --set if startvalue matches

IF @YTDVal > 0 and @LimitVal > 0
BEGIN
WHILE @adjustedval <> (@LimitVal-@YTDVal)
BEGIN

SELECT @NewSDIRate = (@LimitVal-@YTDVal)/@adNetval
SELECT @adNetval = ROUND(@NetBenefit*(1/(1-(@NewSDIRate+@Fed+@staterate+@Soc+@Med))),2)
SELECT @adjustedval = ROUND(@NewSDIRate * @adNetval,2)
END
SELECT @Fed1 = round(@Fed * @adNetval,2)
SELECT @Med1 = ROUND(@Med * @adNetval,2)
SELECT @staterate1 = ROUND(@staterate * @adNetval,2)
SELECT @Soc1 = ROUND(@Soc * @adNetval,2)

END

-- Return the result of the function
insert @TaxLimit
select
@NewSDIRate,
@adNetval,
@adjustedval,
@Fed1,
@Med1,
@staterate1,
@Soc1
RETURN
END


GO


Thank you,
Jeff


I'm not 100% sure about your calculation here. SDI tax is usually set at a certain % by the state with a limit on the amount of taxable income. For CA, for instance, I think the rate is 1.1% on (up to) the first ~$90,600 paid. So if the employee has paid their 1.1% SDI on the first ~$90,600 of pay then the rate is now 0.0% -- no need to back into it.

But all that aside, can you provide 4 or 5 sample data inputs and expected outputs for this function?

Thanks
Mike C
Post #943505
Posted Tuesday, June 29, 2010 7:38 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 14, 2015 8:55 AM
Points: 644, Visits: 2,168
Hmmm...this thread seems familiar...but let's not poke that sleeping tiger.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #944649
Posted Friday, July 2, 2010 1:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 5, 2013 7:19 AM
Points: 69, Visits: 65
I agree - don't awaken the beast.

In the eyes of developers, cursors are like Marmite.
Post #946629
Posted Friday, July 2, 2010 6:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
ZZZZZZZZZZZZZZZZZZZZZZ ... mmha grmmah mrmma ... ZZZZZZZZZZZZZZZZZZZZ

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #946783
Posted Sunday, July 4, 2010 9:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 12:19 AM
Points: 1, Visits: 12
I also found this article rather insulting. I almost never use cursors. In fact, I think I should use them more. I sometimes find myself dong mental jujitsu, trying to write set-based T-SQL, when the task lends itself more easily to procedural code.

Of course they are slower, but sometimes performance is not as important as writing something that is simple and easily maintainable. Arguing that programmers are lazy and dumb is all the more reason to write something in a way that your (lazy, dumb) successors will understand.

Obviously anyone reading this article is interested in avoiding cursors, so why start off with a self-indulgent rant? JMHO.
Post #947368
Posted Sunday, July 4, 2010 11:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 19, 2015 11:18 AM
Points: 1,277, Visits: 1,150
mschroeppel (7/4/2010)
I also found this article rather insulting. I almost never use cursors. In fact, I think I should use them more. I sometimes find myself dong mental jujitsu, trying to write set-based T-SQL, when the task lends itself more easily to procedural code.

Of course they are slower, but sometimes performance is not as important as writing something that is simple and easily maintainable. Arguing that programmers are lazy and dumb is all the more reason to write something in a way that your (lazy, dumb) successors will understand.

Obviously anyone reading this article is interested in avoiding cursors, so why start off with a self-indulgent rant? JMHO.


Set-based processing does stretch the old brain cells a bit, but as you point out everyone understands a loop.

I just had a recent discussion with a group about the benefits of writing extremely simplistic code versus efficiency (and several other programming oddities, btw). I listed out several coding priorities and challenged them to put their coding priorities in the correct order for their business. If you try this exercise and "efficiency" is at the bottom of the list, then you obviously have to code to your priorities.

Mike C
Post #947375
« Prev Topic | Next Topic »

Add to briefcase «««3435363738

Permissions Expand / Collapse