Musings of an anonymous geek

February 19, 2007

How to replace a view, a cursor loop, and a few other queries with one SQL statement

Filed under: Database,Scripting,Technology — m0j0 @ 1:38 pm

I had a stored procedure that defined a cursor using a crazy query, and then a LOOP that did a FETCH and went row-by-row and performed updates and inserts to 3 or 4 different tables. I was having some trouble completing the very last thing the procedure needed to do. So I figured I’d take a little ‘learning break’ and do s’more research on what I was trying to accomplish.

I came across Roland Bouman’s blog, and read quite a few of the articles there. He’s a good presenter of his knowledge, and would appear to be incredibly knowledgeable about all things SQL-related, and something in those articles got me thinking that what I was trying to do may not be the greatest use of the DB server’s resources.

I had a cursor that fetched something like 350 rows for my loop to process. On average, probably fewer than 5 of those 350 rows would point to changes the procedure would need to make (either inserts or updates). Further, the cursor query depended on three views I had created, in part to work around shortcomings in an application’s database design, and in part to map external data to id’s defined in the application. This was quickly turning into a steaming heap of rotten garbage that I was going to have to provide for the care and feeding of. I had entered the No Bueno Zoneā„¢

I got s’more ideas from Roland’s blog, thought on this for the weekend, futzed around with some stuff, fell down a few times, and then, first thing this morning, I came in, sat down, stroked my chin for about 5 minutes, did some typing, and then sat back. I inspected the code. I looked at the output of a few other small queries. I tested the code, and immediately put my arms in the air signaling the touchdown that had just been achieved.

Here’s the final task I needed to accomplish in the stored procedure. Thanks again to Roland for sharing his knowledge on his blog. It was very useful.

UPDATE groups_members AS grpmem,
    (
        SELECT
            u.id AS uid,
            g.id AS newgid,
            m.groupid AS oldgid
        FROM
            bb_data_map AS b
            JOIN user AS u ON u.username = b.netid
            JOIN course AS c ON c.idnumber = b.coursename
            JOIN groups AS g ON g.name = b.precept AND g.courseid = c.id
            LEFT JOIN
                (
                    SELECT
                        gm.userid AS userid,
                        gm.groupid AS groupid,
                        g.name AS grpname,
                        c.idnumber AS coursename
                    FROM
                        groups_members AS gm,
                        groups AS g,
                        course AS c
                    WHERE g.id = gm.groupid
                        AND c.id = g.courseid
                 ) AS m ON m.userid = u.id AND m.coursename = b.coursename
        WHERE b.precept IS NOT NULL
        AND c.id IS NOT NULL
        AND m.groupid != g.id
    ) AS newgrp
    SET grpmem.groupid = newgrp.newgid
    WHERE (grpmem.userid = newgrp.uid AND grpmem.groupid = newgrp.oldgid)

Technorati Tags: , , , , , ,

Social Bookmarks:

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: