Post reply

Those cursed cursors

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

     

  • January 18, 2007 at 5:16 pm

    #684164

    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?

  • January 18, 2007 at 5:47 pm

    #684169

    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

  • January 19, 2007 at 6:15 am

    #684264

    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.

  • January 19, 2007 at 7:39 am

    #684300

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

  • January 19, 2007 at 7:47 am

    #684304

    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.

  • January 19, 2007 at 8:03 am

    #684321

    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)

  • January 19, 2007 at 8:28 am

    #684333

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 19, 2007 at 8:55 am

    #684341

    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

  • January 19, 2007 at 9:30 am

    #684361

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

  • January 19, 2007 at 10:20 am

    #684386

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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