August 6, 2009 at 7:15 pm
I included the cursor example just to show you what a simple one looked like, not so you could use it for the basis of all future work. They aren't a "powerful construct", they're (usually) a sign that you've given up trying to find a better set-based solution.
The cursor example does one update per row and is the least efficient/scalable. The WHILE loop that does one update per party (at most) is better, and the single UPDATE with the XML subquery is the best of the three. Considering that you populated the oc_daddr table with a GROUP BY query and are coming back to it with an UPDATE to fill in parties, the most efficient of the four would be to load the table in one pass from the SELECT. But that can still be improved on.
The XML subquery is a form of correlated subquery, so the oc_addr table will be scanned again for every unique address. Something that only requires one pass would be more efficient. If the list of possible parties is known in advance, this would work:
TRUNCATE TABLE dbo.oc_daddr;
INSERT INTO dbo.oc_daddr
SELECT Addr, COUNT(*) AS members,
parties = ISNULL(MAX(CASE party WHEN 'AI' THEN '|AI' ELSE NULL END), '')
+ ISNULL(MAX(CASE party WHEN 'DEM' THEN '|DEM' ELSE NULL END), '')
+ ISNULL(MAX(CASE party WHEN 'DS' THEN '|DS' ELSE NULL END), '')
+ ISNULL(MAX(CASE party WHEN 'REP' THEN '|REP' ELSE NULL END), '')
FROM oc_addr
GROUP BY Addr
There is probably someone out there who can come up with something better still. And they're probably lurking on this website ready to answer your questions for free (as long as you ask nicely). If you read Books Online at least to the point where you know what @ and @@ are for (nothing about passing by reference), you'll get even better answers.
August 7, 2009 at 10:10 am
I'll have to keep in mind that holding onto my previous row-wise approach is a crutch, and one likely to handicap my ability to exploit SQL Server. I'll look at and link books online today. Thanks!
As an aside, depending on the county, there are between 60 and 206 "ACTIVE" party designations including such "mainstream" groups as:
Rock and Roll Party
Dudes International Party
More Money for Women Party
Block Party
Birthday Party
Stargazer Party
Formenorian Monarchy Party (WTF?)
Superhappy Evolution & Neuroscience (with ties to the pharmaceutical lobby ya think?)
We Like Women and Science (pretty sure this one started in the dorms at CalTech), and
Hespanic (yes, with an 'E'!)
etc.
I don't know if all this is a hopeful sign, or an indication of our impending doom... Just thought you'd get a kick out of it.
August 7, 2009 at 11:11 am
What, no Jedi party?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply