April 27, 2009 at 6:57 am
Another good one.
And on whatever the medical situation is, get well soon, man.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2009 at 7:25 am
Neat article and nicely written. I'm new to SQL but it's great to have this reference on how to change that procedural thought into a set thought.
By the way, the link to Part 1 is pointing to Part 2.
Good job and get well soon! Your fans eagerly await the next installment. 🙂
-- Kit
April 27, 2009 at 7:33 am
Jeff Moden (4/27/2009)
Heh... in SQL Server 2005, even that can be done without a cursor. We're just gonna have to wait for it in Barry's series of articles.
OK it *can* be done without a cursor... I have seen some dirty work arounds to get stored procedure calls inside select statements using linked servers but they need certain security features turned on and would still end up as a row by row operation when processed within the database engine (not to mention all the other overheads associated with opening new DB connections) so no performance benefit.
IMO a last resort technique (even after the possibility of SSIS has been excluded).
April 27, 2009 at 7:38 am
Writing a .net app to pull the dataset and iterate through it sending the mails would be faster than doing it in SQL, especially as merging the html template will involve some substring / replace activities (which in volume SQL isn't very fast at).
Depends on your volumes of course, if you are sending only a few thousand at a time a cursor or while loop would probably be fine.
April 27, 2009 at 8:07 am
jwheeler (4/27/2009)
Samuel Vella (4/27/2009)
jwheeler (4/27/2009)
The fact you even have to tell so called developers this stuff is frightening.Not really and it's something I've seen a few times...
When someone has a background in VB or C and has been dumped into SQL development then they might be able to write code but they lack the set based thinking which a good DB developer needs (they might not even be aware of being able to return sets depending on how much exposure they're had to databases in their previous life).
Usually a helping hand with their first few pieces of work will get them thinking the right way from the outset.
I know from experience when dabbling in ASP .NET that something which I've spent hours and several tens of lines of code trying to get to work can be ripped apart and rewritten as a very simple class by someone who knows what they're doing
Then surely the manager hasn't done their job by employing the wrong tool for the task?
If you have database development to be done you either employ a db dev or a C# / VB developer who is also a SQL dev. You don't buy a hammer to put a screw in the wall, surely?
That's a grossly oversimplified depiction. For a more realistic scenario, consider the following:
You're an IT manager for a medium-sized business, and you need a developer to build a custom database application -- but you've got almost no budget. You finally manage to convince the accountant to let you hire ONE developer.
Since you've got no budget, you do a little research and find the Visual Studio Express editions. You want to have some understanding of the created code, and you have some VB background. Now you've got a databse (SQL Server Express), a development environment (Visual Studio Express) and a development language (Visual Basic). So you put those requirements into your favorite jobs website and wait for your prince to come.
All of the responses you get back are specialized developers, SQL devs or VB devs who don't have a lot of db experience. What do you do?
From this (much more realistic) scenario, you should see that the problem is more like having a square bolt, and only having closed-end metric and standard hex wrenches. Sometimes none of the tools available to you fit the job quite right, and you have to pick the one that comes closest. Sometimes there's a tool you already have which isn't the right one but is a lot more easy to justify than spending $80k on a new tool for one job.
Of course the problem with all of these analogies is that we developers always come out looking like tools. :ermm:
April 27, 2009 at 8:16 am
sknox (4/27/2009)
Of course the problem with all of these analogies is that we developers always come out looking like tools. :ermm:
Lol "this code was written by a tool", yes as you say very simplified.
But even so, this just seems like application of common sense to me. Obviously this is even more uncommon than I had thought. Surely if you google 'select 2 fields from table SQL' you would get loads of hits with the straight forward, non mad solution?
I think If I had a developer come to me proudly showing off his routine to select 2 fields of data as given above I would... I don't know what I would do, should you laugh or cry, or both?
I have a SQL and VB background so I guess it just seems impossible to me that these examples aren't slap you in the face obvious.
April 27, 2009 at 8:23 am
Another good article Barry. I look forward to part 3 as we are starting to get to the "meat".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 27, 2009 at 8:31 am
While the sending e-mail task presents an interesting challenge for those seeking to eliminate loops, my guess is that a tiny percentage of resources are being wasted on looping code compared to the resources used by sendmail. Even if you could reduce looping overhead to 0, most of your original execution time would still be taken up by sendmail. Time would probably be better spent working on a way to offload the e-mailing task to another application.
I look forward to future installments, Barry! Didn't learn anything new about cursor replacement yet, but learned from your "test harness". Using sys.dm_exec_requests seems like a much more efficient bench marking approach than taking the difference of before and after values from sysprocesses (Which also gives occasional negative values, BTW).
Hate to be the first to point this nit pick out, but in your first cursor replacement example I think you meant to use:
Select LastName + ', ' + Firstname
From Sales.vIndividualCustomer
Hope you're feeling well, soon. Bring on installment 3!
April 27, 2009 at 8:34 am
If the idea of the article is present set-based solutions to common cursor implementations, I have one that I'd like to see. It is the simple list result. I want a query that gives me a comma-delimited list of another table's results. For example, using the AdventureWorks database (pubs was much simpler for this type of stuff IMO) we might have:
Select C.FirstName + ' ' + C.LastName, ST.Name
from Person.Contact As C
Join Sales.SalesTerritoryHistory As SH
On C.ContactID = SH.SalesPersonID
Join Sales.SalesTerritory As ST
On ST.TerritoryID = SH.TerritoryID
Order By C.FirstName, C.LastName
This lists out the sales territory history for each contact (sales person). What I want is a single query that gives me:
Maciej DuszaCentral, Northeast
Shelley DyckSouthwest
Linda EcoffeyCentral, Northeast
Carla EldridgeCanada
Carol Elliott Southeast
Shannon ElliottNorthwest
Jauna ElsonSouthwest
Michael EmanuelCanada, United Kingdom
Terry EminhizerNorthwest
Gail EricksonCanada
Mark EricksonFrance
Martha EspinozaNorthwest
Janeth EstevesGermany
Twanna EvansAustralia
Note that the comma-delimited list is sorted alphabetically. I'm aware that there is a hack using Replace, Stuff and For Xml to achieve this but it is pretty slow especially as the resultset (outer or inner) gets large. I'm also aware that doing this in the middle tier with something like LINQ is the preferred solution.
However, I get this type of request regularly and the common solution for people doing this in database (which again, I realize is not the ideal) is to create a UDF that uses a cursor or use the For Xml.
Once upon a time, Joe Celko mentioned that there was an ANSI SQL function called LIST to do this very thing but that no one implemented it.
April 27, 2009 at 9:07 am
Thomas (4/27/2009)
Note that the comma-delimited list is sorted alphabetically. I'm aware that there is a hack using Replace, Stuff and For Xml to achieve this but it is pretty slow especially as the resultset (outer or inner) gets large.
Strange. I use STUFF & FOR XML for this all the time and it's actually pretty fast.
The only time I had a problem was when operating on tables with 10 million+ records.
Any amount smaller than that worked pretty quick.
April 27, 2009 at 9:15 am
Goldie Graber (4/27/2009)
Thomas (4/27/2009)
Note that the comma-delimited list is sorted alphabetically. I'm aware that there is a hack using Replace, Stuff and For Xml to achieve this but it is pretty slow especially as the resultset (outer or inner) gets large.Strange. I use STUFF & FOR XML for this all the time and it's actually pretty fast.
The only time I had a problem was when operating on tables with 10 million+ records.
Any amount smaller than that worked pretty quick.
I'd be interested in seeing the STUFF and FOR XML solutions. This might be hidden RBAR, but here's an old school udf approach (based on learnings from this forum!) that works on older SQL versions. I'm betting performance takes a nose dive as the list length grows, but look, No "Replace, Stuff, or For Xml"!
create function [dbo].[ufn_territorylist](@salesPersonID as int) returns varchar(max) as
begin
declare @territory as varchar(max)
select @territory = Coalesce(@territory + ', ', '') + Temp.TerritoryName
from
(select distinct ST.Name as TerritoryName
from Sales.SalesTerritoryHistory As SH
Inner Join Sales.SalesTerritory As ST
On ST.TerritoryID = SH.TerritoryID
where salesPersonID = @salesPersonID) Temp
order by TerritoryName
return @territory
end
go
--This lists all salesperson regardless of whether or not they have a territory
Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)
from Person.Contact As C
Order By C.LastName, C.FirstName --I think this is the order you meant
--This lists only the ones that have at least one territory
Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)
from Person.Contact As C
where ContactID in
(Select SalesPersonID from Sales.SalesTerritoryHistory)
Order By C.LastName, C.FirstName --I think this is the order you meant
April 27, 2009 at 9:22 am
For the email solution, we run a .NET app here to send the mail, offloading the load of XP_sendmail from SQL Server. We grab a bunch of items to send using a set-based solution to merge the recpients with the email and replace some tokens. The app sends, and then once it gets a sent OK, it updates a single row in the DB. It can retry emails with issues, without loading the db.
April 27, 2009 at 9:22 am
Andy DBA (4/27/2009)
Goldie Graber (4/27/2009)
Thomas (4/27/2009)
Note that the comma-delimited list is sorted alphabetically. I'm aware that there is a hack using Replace, Stuff and For Xml to achieve this but it is pretty slow especially as the resultset (outer or inner) gets large.Strange. I use STUFF & FOR XML for this all the time and it's actually pretty fast.
The only time I had a problem was when operating on tables with 10 million+ records.
Any amount smaller than that worked pretty quick.
I'd be interested in seeing the STUFF and FOR XML solutions. This might be hidden RBAR, but here's an old school udf approach (based on learnings from this forum!) that works on older SQL versions. I'm betting performance takes a nose dive as the list length grows, but look, No "Replace, Stuff, or For Xml"!
create function [dbo].[ufn_territorylist](@salesPersonID as int) returns varchar(max) as
begin
declare @territory as varchar(max)
select @territory = Coalesce(@territory + ', ', '') + Temp.TerritoryName
from
(select distinct ST.Name as TerritoryName
from Sales.SalesTerritoryHistory As SH
Inner Join Sales.SalesTerritory As ST
On ST.TerritoryID = SH.TerritoryID
where salesPersonID = @salesPersonID) Temp
order by TerritoryName
return @territory
end
go
--This lists all salesperson regardless of whether or not they have a territory
Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)
from Person.Contact As C
Order By C.LastName, C.FirstName --I think this is the order you meant
--This lists only the ones that have at least one territory
Select C.FirstName + ' ' + C.LastName, dbo.ufn_territorylist(C.ContactID)
from Person.Contact As C
where ContactID in
(Select SalesPersonID from Sales.SalesTerritoryHistory)
Order By C.LastName, C.FirstName --I think this is the order you meant
This type of function usually performs slower than the FOR XML version.
As I don't have AdventureWorks, I'm posting an example here for you.
DECLARE @Colors TABLE
(
ColorID INT IDENTITY(1,1)
,ColorName VARCHAR(15)
,ColorFamily VARCHAR(15)
)
INSERT INTO @Colors
SELECT 'RED', 'RED'
UNION ALL SELECT 'BLUE', 'BLUE'
UNION ALL SELECT 'WHITE', 'NONE'
UNION ALL SELECT 'PINK', 'RED'
UNION ALL SELECT 'BLACK', 'NONE'
UNION ALL SELECT 'GREEN', 'BLUE'
UNION ALL SELECT 'PURPLE', 'RED'
UNION ALL SELECT 'YELLOW', 'YELLOW'
UNION ALL SELECT 'ORANGE', 'YELLOW'
UNION ALL SELECT 'GOLD', 'YELLOW'
UNION ALL SELECT 'SILVER', 'NONE'
SELECT A.ColorFamily
,STUFF((SELECT ',' + B.ColorName
FROM @Colors B
WHERE A.ColorFamily = B.ColorFamily
FOR XML PATH('')),1,1,'') AS ColorList
FROM @Colors A
GROUP BY A.ColorFamily
April 27, 2009 at 9:31 am
Thanks for the post! I bet that blows the function based approach out of the water!
April 27, 2009 at 9:35 am
I'm a .NET developer who is learning SQL on-the-fly. Due to budget constraints, we can not hire a .NET AND a SQL developer. The last 2 years have been a fascinating ride learning SQL. My introduction was from a well-seasoned DBA who uses cursors all over the place.
Since I joined this site, I have learned the set-based way of thinking thanks to the writings of Jeff Moden and others. I wish I had this article two years ago. It's a great way to teach. Step-by-step. I look forward to part 3. Get well soon, Barry.
BTW, if I really think I need a RBAR solution, such as sending email, I write a simple stored procedure to return the data to my .NET code and do it on the client. I try to use the tool that best fits the situation.
Viewing 15 posts - 16 through 30 (of 316 total)
You must be logged in to reply to this topic. Login to reply