April 27, 2009 at 9:37 am
I'm a newbie at this but here is my solution to the second tougher example. I think it's right. I get the same results back.
Select h.SalesOrderID AS OrderNo, d.SalesOrderDetailID AS LineNum, h.CustomerID AS CustomerID, d.ProductID AS ProductNo
From Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d on h.SalesOrderID = d.salesorderid
WHERE h.CustomerID = d.ProductID
Order By d.SalesOrderID, d.SalesOrderDetailID, d.ProductID
The difference in processing is
CpuMsLogRdsElapsed
6032 8348386089
CpuMsLogRdsElapsed
62 292 110
April 27, 2009 at 9:38 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
Great example!
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 27, 2009 at 9:42 am
Steve Jones - Editor (4/27/2009)
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.
As someone has posted already - operations like emailing should be done externally from the database, using SSIS or in your case a .NET application.
Defeats the purpose of the article though as it avoids the central premise of the piece which is to replace cursors with set based SQL
From Part one of the series
What I will be showing you in this series of articles is how to use the new and old features of Transact-SQL to both create and convert SQL routines that are faster, smaller, cleaner, clearer and more supportable without the use of Cursors or While loops.
Any cursor based operation can be replaced with an external application 😛
April 27, 2009 at 10:09 am
Ditto on the fact people think this kind of article is even needed/scarey (at least so far). I can't imagine ever writing the cursors for the examples the author has shown. WOW.
April 27, 2009 at 10:28 am
Here is an example of how to use the STUFF...FOR XML using the example from AdventureWorks:
Set NoCount On;
Declare @cpu_ int;
Declare @lreads_ int;
Declare @eMsec_ int;
Select
@cpu_ = cpu_time,
@lreads_ = logical_reads,
@eMsec_ = total_elapsed_time
From
sys.dm_exec_requests
Where
session_id = @@spid;
--======
with TerritoryHistory (
SalesPersonID,
Territories
) as (
select distinct top 100 percent
sth.SalesPersonID,
stuff((select ', ' + st.Name from Sales.SalesTerritoryHistory sth1 inner join Sales.SalesTerritory st on (sth1.TerritoryID = st.TerritoryID) where sth.SalesPersonID = sth1.SalesPersonID order by st.Name for xml path('')),1,2,'')
from
Sales.SalesTerritoryHistory sth
order by
sth.SalesPersonID
)
select
c.FirstName + ' ' + c.Lastname as SalesPerson,
th.Territories
from
Person.Contact c
inner join TerritoryHistory th
on (c.ContactID = th.SalesPersonID)
order by
c.FirstName,
c.LastName
--======
Select
cpu_time-@cpu_ as CpuMs,
logical_reads- @lreads_ as LogRds,
total_elapsed_time - @eMsec_ as Elapsed
From
sys.dm_exec_requests
Where
session_id = @@spid
GO
April 27, 2009 at 10:30 am
Samuel Vella (4/27/2009)
Steve Jones - Editor (4/27/2009)
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.As someone has posted already - operations like emailing should be done externally from the database, using SSIS or in your case a .NET application.
Defeats the purpose of the article though as it avoids the central premise of the piece which is to replace cursors with set based SQL
From Part one of the series
What I will be showing you in this series of articles is how to use the new and old features of Transact-SQL to both create and convert SQL routines that are faster, smaller, cleaner, clearer and more supportable without the use of Cursors or While loops.Any cursor based operation can be replaced with an external application 😛
Just because any cursor based operation can be replaced with an external application doesn't mean it should be (and yes I noticed the emoticon, but others might not).
April 27, 2009 at 10:46 am
Try this code in MSSql 2005 or 2008...
[font="Courier New"]
/****** Object: StoredProcedure [dbo].[util_Create_Temp_Table_Iterator] Script Date: 04/27/2009 09:45:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_Create_Temp_Table_Iterator]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[util_Create_Temp_Table_Iterator]
GO
/****** Object: StoredProcedure [dbo].[util_Create_Temp_Table_Iterator] Script Date: 04/27/2009 09:40:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
Utility to make a temp table iterator rather than use a cursor
Param:@TableName - the name of the table you wish to iterate
Comment: See comments in code about inserting and doing work
Created:Nick Stein11-14-2005
************************************************************/
CREATE PROCEDURE [dbo].[util_Create_Temp_Table_Iterator]
@TableName varchar(75)
AS
DECLARE @SSQL varchar(8000)
SELECT @SSQL = '/****** BEGIN ' + @TableName + ' LOOP ******/' + char(10)
SELECT @SSQL = @SSQL + '-- Temp table columns as variables' + char(10)
-- Declare the variables
SELECT @SSQL = @SSQL + 'DECLARE @' + Replace(S.[NAME],' ','_') + ''
+ CASE S.system_type_id
WHEN 34 THEN ' [varchar] (2000)' + CHAR(10)
WHEN 35 THEN ' [text]' + CHAR(10)
WHEN 36 THEN ' [uniqueidentifier]' + CHAR(10)
WHEN 48 THEN ' [tinyint]' + CHAR(10)
WHEN 52 THEN ' [smallint]' + CHAR(10)
WHEN 56 THEN ' [int]' + CHAR(10)
WHEN 58 THEN ' [smalldatetime]' + CHAR(10)
WHEN 59 THEN ' [real]' + CHAR(10)
WHEN 60 THEN ' [money]' + CHAR(10)
WHEN 61 THEN ' [datetime]' + CHAR(10)
WHEN 62 THEN ' [float]' + CHAR(10)
WHEN 98 THEN ' [sql_variant]' + CHAR(10)
WHEN 99 THEN ' [ntext]' + CHAR(10)
WHEN 104 THEN ' [bit]' + CHAR(10)
WHEN 106 THEN ' [decimal]' + CHAR(10)
WHEN 108 THEN ' [numeric]' + CHAR(10)
WHEN 122 THEN ' [smallmoney]' + CHAR(10)
WHEN 127 THEN ' [bigint]' + CHAR(10)
WHEN 165 THEN ' [varbinary] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
WHEN 167 THEN ' [varchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
WHEN 173 THEN ' [binary] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
WHEN 175 THEN ' [char] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
WHEN 189 THEN ' [timestamp]' + CHAR(10)
WHEN 231 THEN ' [nvarchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
WHEN 239 THEN ' [nchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') ' + CHAR(10)
ELSE ' Unknown system_type_id ' + CAST(S.system_type_id AS VARCHAR(10)) + char(10)
END
FROM SYS.Columns S
WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)
ORDER BY column_id
SELECT @SSQL = @SSQL + CHAR(10)
SELECT @SSQL = @SSQL + '-- Iterator variables ' + CHAR(10)
SELECT @SSQL = @SSQL + 'DECLARE@I_' + @TableName + ' INT' + CHAR(10)
SELECT @SSQL = @SSQL + 'DECLARE@M_' + @TableName + ' INT' + CHAR(10)
SELECT @SSQL = @SSQL + CHAR(10)
-- Make the Create Table
SELECT @SSQL = @SSQL + 'CREATE TABLE #' + @TableName + ' (' + char(10) +
'[IDENT] int IDENTITY, ' + CHAR(10)
SELECT @SSQL = @SSQL + '[' + Replace(S.[NAME],' ','_') + ']'
+ CASE S.system_type_id
WHEN 34 THEN ' [varchar] (2000) NULL,' + CHAR(10)
WHEN 35 THEN ' [text] NULL,' + CHAR(10)
WHEN 36 THEN ' [uniqueidentifier] NULL,' + CHAR(10)
WHEN 48 THEN ' [tinyint] NULL,' + CHAR(10)
WHEN 52 THEN ' [smallint] NULL,' + CHAR(10)
WHEN 56 THEN ' [int] NULL,' + CHAR(10)
WHEN 58 THEN ' [smalldatetime] NULL,' + CHAR(10)
WHEN 59 THEN ' [real] NULL,' + CHAR(10)
WHEN 60 THEN ' [money] NULL,' + CHAR(10)
WHEN 61 THEN ' [datetime] NULL,' + CHAR(10)
WHEN 62 THEN ' [float] NULL,' + CHAR(10)
WHEN 98 THEN ' [sql_variant] NULL,' + CHAR(10)
WHEN 99 THEN ' [ntext] NULL,' + CHAR(10)
WHEN 104 THEN ' [bit] NULL,' + CHAR(10)
WHEN 106 THEN ' [decimal] NULL,' + CHAR(10)
WHEN 108 THEN ' [numeric] NULL,' + CHAR(10)
WHEN 122 THEN ' [smallmoney] NULL,' + CHAR(10)
WHEN 127 THEN ' [bigint] NULL,' + CHAR(10)
WHEN 165 THEN ' [varbinary] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
WHEN 167 THEN ' [varchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
WHEN 173 THEN ' [binary] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
WHEN 175 THEN ' [char] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
WHEN 189 THEN ' [timestamp] NULL,' + CHAR(10)
WHEN 231 THEN ' [nvarchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
WHEN 239 THEN ' [nchar] (' + CAST(S.max_length AS VARCHAR(20)) + ') NULL,' + CHAR(10)
ELSE ' UNKNOWN system_type_id ' + CAST(S.system_type_id AS VARCHAR(10)) + ' ' + CHAR(10)
END
FROM SYS.Columns S
WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)
ORDER BY column_id
SELECT @SSQL = @SSQL + 'CONSTRAINT [PK_IDENT_' + @TableName + '] PRIMARY KEY CLUSTERED
(
[IDENT]
) ON [PRIMARY] )
'
-- Print the results so far
PRINT @SSQL
SELECT @SSQL = ''
-- Make the iterator
SELECT @SSQL = @SSQL + '
-- Insert into temp table
INSERT INTO #' + @TableName + CHAR(10)
SELECT @SSQL = @SSQL + 'SELECT '
PRINT @SSQL
SELECT @SSQL = ''
SELECT @SSQL = @SSQL + '[' + S.[NAME] + '],' + CHAR(10)
FROM SYS.Columns S
WHERE object_id = OBJECT_ID(@TABLENAME)
ORDER BY column_id
-- Clip the CHAR(10) and ,
SELECT @SSQL = LEFT(@SSQL, LEN(@SSQL)-2) + CHAR(10)
SELECT @SSQL = @SSQL + ' FROM ' + @TableName + '
--WHERE -- SET WHERE CLAUSE
--ORDER BY -- SET ORDER BY
'
PRINT @SSQL
SELECT @SSQL = '
-- For Debugging uncomment the next line
-- SELECT * FROM #' + @TableName
-- Print the results so far and start a new string
PRINT @SSQL
SELECT @SSQL = '
-- Initialize iterator variables
SELECT@I_' + @TableName + ' = MIN(IDENT) FROM #' + @TableName + '
SELECT@M_' + @TableName + ' = MAX(IDENT) FROM #' + @TableName + '
-- Loop through temp table doing work
WHILE@I_' + @TableName + ' <= @M_' + @TableName + '
BEGIN
'
-- Print the results so far and start a new string
PRINT @SSQL
SELECT @SSQL = 'SELECT ' + CHAR(10)
-- make the select into variables
SELECT @SSQL = @SSQL + '@' + Replace(S.[NAME],' ','_') + ' = [' + Replace(S.[NAME],' ','_') + '],' + CHAR(10)
FROM SYS.Columns S
WHERE object_id = (SELECT object_id FROM SYS.Objects WHERE NAME=@TableName)
ORDER BY column_id
-- Clip the CHAR(10) and ,
SELECT @SSQL = LEFT(@SSQL, LEN(@SSQL)-2) + CHAR(10)
SELECT @SSQL = @SSQL + 'FROM #' + @TableName + ' WHERE IDENT = @I_' + @TableName + char(10)
PRINT @SSQL
-- increment and end the iterator
SELECT @SSQL = '-- Add work here for ' + @TableName + ' Loop
SELECT @I_' + @TableName + ' = @I_' + @TableName + ' + 1
END
DROP TABLE #' + @TableName + CHAR(10) +
'/****** END ' + @TableName + ' LOOP' + ' ******/' + char(10)
PRINT @SSQL
GO
[/font]
April 27, 2009 at 10:48 am
Samuel Vella (4/27/2009)
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).
Nah... no such dirty work arounds are necessary. No security violations, either. I do agree that a Cursor won't hurt in such a situation, but they're not necessary if you don't want them to be. Still, I'm not going to steal Barry's thunder on this set of articles. I don't actually know if he's planning on showing the technique, but I don't mind waiting to see.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 10:53 am
Samuel Vella (4/27/2009)
Any cursor based operation can be replaced with an external application 😛
Absolutely true. But it's still amazing to me what people actually think they need a cursor for. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 10:56 am
Andy DBA (4/27/2009)
Thanks for the post! I bet that blows the function based approach out of the water!
Like anything else, it depends... I've seen it go both ways. Knowledge of both methods is a great thing to know.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 11:17 am
Coming from a novice point of view, I can't understand why cursors are really even used? They seem so much more complicated than anything set-based in SQL. I'd never even seen actual cursors before - heard about them in some of the posts on this site - reading this article. Having now seen them and gone through the explanation in this article of their structure, they seem about twice as complicated as anything set-based. Glad the references, mentors, and instruction I've had to this point in using SQL Server hadn't mentioned cursors, as they seem thoroughly more confusing!
Great article
April 27, 2009 at 11:23 am
I've always found cursors to be a pain and set-based to be fun. So, I'm no fan of cursors. Also, as soon as I was able to use For XML, I jumped on it and use it whenever the need arises.
I say all that as background so that you can understand the context in which the next comment is made: The For XML is not an ideal replacement for a true "List" function as someone else posted about. The For XML is about making XML text and the encoding that it does can be a real problem. For some data sets, it is no big deal. But having to do Replace() function to deal with the coding can be a pain.
If MS is not going to implement the List function and instead reply on the Stuff/For XML statements, then I'd like to see some kind of function called "DeEncode" or something that will take care of all the Replace needs at once.
Otherwise, I might still be tempted to use Cursors sometimes. It would depend on the data. I haven't hit such a situation yet, but I'm not going to rule it out as the best solution in some cases.
April 27, 2009 at 11:25 am
Just stepped in for a quick read up on the article...good article, however the examples are a bit naive and are not applied toward anything I would have ever thought someone might use a cursor for - why would anyone use a cursor to loop over a table when the same result is created with a simple select statement. I understand the examples are made simple to make a point, but the only time I have ever needed a cursor is to achieve some sort of lookahead in the result set, such that data in in row 1 depended on knowing the value of some data in row 10 and the only way to get that (without building an external app/script) was with a cursor.
Thanks for the article just the same.
April 27, 2009 at 11:26 am
I forgot to add that I think this is a well written article. It would be a great intro for beginners who are cursor prone. It is great because of the method you give for "converting" cursors. I like that. It is the first time I have seen something concrete that a beginner could really stick his/her teeth into.
Viewing 15 posts - 31 through 45 (of 316 total)
You must be logged in to reply to this topic. Login to reply