Those cursed cursors

Question

Those cursed cursors

Lee Roper

Mr or Mrs. 500

Points: 585
More actions
January 18, 2007 at 5:04 pm

#171920

Hi, folks.
I'm treading into the world of cursors with some trepidation. I see from a little posts perusal that most, if not all, of you decry the use of the things, but I may need to use one. I just dunno right now.
My problem is that I need to update a balance column in...let's say...a customers table by getting a sum of all the invoice totals for individual customers and a sum of all their payments. The balance of course will be the difference between the two.
It seems to me there should be a way to do it without a cursor, but I can't figure it out. To me the problem seems to require one to iterate through the customers table, using the customerid to get the totals from the other tables.
Anyhoo...could someone point me in the direction in which I should be headed. Right now I'm simply scratching it.
Thanks,
Lee...

Viewing 11 posts - 1 through 10 (of 10 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Lee-

It sounds like what you're talking about can be done without a cursor and I'd echo that you should try other approaches first. Can you perhaps post what the tables in question are like?

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 2

Purists would argue that a balance column has no place in a correctly normalized database schema (except, perhaps, in a view) however I understand that there are circumstances where this would be a desirable deviation.

You can do it, here is one example:

CREATE TABLE #Customer
(
    CustID      INT         IDENTITY(1,1) PRIMARY KEY,
    Cust_Name   VARCHAR(50),
    Balance     SMALLMONEY
)

CREATE TABLE #Payments
(
    PaymentId   INT     IDENTITY(1,1) PRIMARY KEY,
    InvoiceId   INT,
    PayDate     SMALLDATETIME,
    Amount      SMALLMONEY
)

CREATE TABLE #Inv_Parent
(
    InvoiceID   INT     IDENTITY(1,1) PRIMARY KEY,
    CustID      INT,
    Inv_Date    SMALLDATETIME
)

CREATE TABLE #Inv_Items
(
    InvItemId   INT     IDENTITY(1,1)  PRIMARY KEY,
    InvoiceID   INT,
    ItemId      INT,
    Quantity    SMALLINT,
    Price       SMALLMONEY
)


INSERT INTO #Customer(Cust_name)
VALUES('cust1')

INSERT INTO #Customer(Cust_name)
VALUES('cust2')

INSERT INTO #Customer(Cust_name)
VALUES('cust3')

INSERT INTO #Customer(Cust_name)
VALUES('cust4')
    
INSERT INTO #Inv_Parent(custid, Inv_date)
VALUES(1, GETDATE())
INSERT INTO #Inv_Parent(custid, Inv_date)
VALUES(2, GETDATE())
INSERT INTO #Inv_Parent(custid, Inv_date)
VALUES(3, GETDATE())
INSERT INTO #Inv_Parent(custid, Inv_date)
VALUES(4, GETDATE())
INSERT INTO #Inv_Parent(custid, Inv_date)
VALUES(5, GETDATE())

INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(1, 53, 20, 25.60)
INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(1, 70, 11, 5.40)

INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(2, 53, 20, 25.60)
INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(2, 70, 21, 5.40)

INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(3, 53, 20, 25.60)
INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(3, 70, 55, 5.40)

INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(5, 53, 34, 25.60)
INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(5, 70, 33, 5.40)

INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(4, 53, 10, 25.60)
INSERT INTO #Inv_Items(InvoiceId, ItemId, Quantity, Price)
VALUES(4, 70, 16, 5.40)


INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(1, GETDATE(), 500.00)
INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(1, GETDATE(), 100.00)
INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(2, GETDATE(), 200.00)
INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(3, GETDATE(), 500.00)
INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(3, GETDATE(), 400.00)
INSERT INTO #Payments(InvoiceId, Paydate, Amount)
VALUES(4, GETDATE(), 200.00)

GO

UPDATE C 
    SET C.Balance = B.Balance
FROM #Customer C
    INNER JOIN (
                SELECT IP.CustId, SUM(I.Quantity*I.Price) - SUM(ISNULL(P.Amount, 0)) AS Balance
                FROM #Inv_Parent IP
                    INNER JOIN #Inv_Items I
                        ON IP.InvoiceId = I.InvoiceId
                    LEFT OUTER JOIN #Payments P
                        ON I.InvoiceId = P.InvoiceId
                GROUP BY IP.custId
                ) B
        ON C.custID = B.CustId

Go

DROP TABLE #Customer
DROP TABLE #Payments
DROP TABLE #inv_Parent
DROP TABLE #Inv_Items

SQL guy and Houston Magician

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 3

Another way to disallow direct updates to balance, and have triggers update the balance as invoice and payment tables have new rows inserted / updated / deleted.

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 4

>> You can do it, here is one example: <<

Now, why didn't I think of that? (Don't anwer that. <g&gt

That'll do it, Robert. Thanks. Thanks to all you guys that hang out here.

Lee...

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 5

Good suggestion, and that's probably what I'll do also, but I need something to do periodic reconciliations to show the tables are, in fact, in sync, something the client requires.

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 6

Hi, Aaron.

Well, I would but there's really nothing that special about them. Like I said, one contains payment amounts, another invoice amounts, and the customers table carries a balance column. (Yes I know, this flies in the face of a normalized database, but some people live on the edge. <g&gt

I'm currious why cursors are anathema to DBAs, when they seem to be such a handy tool when used, dare I say it, wisely? For instance, what if the customers table isn't all that big so moving through it once is a breeze? I mean, a company can have a very limited number of clients or customers, or what-have-you. Unfortunately, that's not my situation, which is why I'm here asking you guys what I should do.

I understand that relational databases are set oriented, but in certain limited circumstances it seems to me that cursors are no big deal. In fact, in some cases they seem to be the ideal solution.

Lee...("Just trying to keep the conversation lively" - Nick in The Big Chill)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

It's not so much the database that's set oriented, although that's a big part of the issue. It's that TSQL is set oriented. Especially 2005 with common table expressions & CROSS APPLY & OVER & ranking functions... It just makes sense to use the language in the manner it was designed rather than tossing out the baby, the bathwater and the wash basin to build cursors to walk through data that can be easily solved in sets.

That said, not all problems are solved or solvable using set based logic. Sometimes, rarely, cursors work better. The problem is, most people jump to cursors as their first choice rather than last (or at least near last).

"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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 8

In most cases than not, cursors impact the performance adversely for obvious reasons of looping through and hitting db. Just to experience what difference it makes with reference to the performance just write your code using cursor and the solution posted by Robert and execute both and observe the actual execution plan and you will be able to appreciate and concur with whats been said.

Prasad Bhogadi
www.inforaise.com

Lee Roper Mr or Mrs. 500 Points: 585 More actions · Answer 9

Hi, Grant.

>> ... to walk through data that can be easily solved in sets. <<

Easy for you to say!

No, I understand what you're saying, really. But thinking in sets is not as straightforward for some as for others, or so it seems to me. My problem with sets likely stems from the fact I started programming databases using Paradox for DOS, later moving to Paradox for Windows, both of which used cursors extensively. At least that's how their manuals presented application design, and I RTFMs.

Furthermore, set logic is rather abstract...to me, anyway, and I'm fairly good at mathematics in general. I guess I need to bone up on sets if I'm gonna stay in this business, huh?

Lee...

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 10

Are you kidding? Set logic is bloody difficult. I have a hard time wrapping my brain around it almost daily. I just know that I have to take the approach of finding a set based method for the problems in front of me in order to arrive at scalable, well performing, easy to maintain procedures.

"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