Please give me a replacement for a Cursor
-
Can Exec accept a scalar UDF as a parameter? If so you can pass the group to a UDF that COALESCEs the relevant group email addresses into a comma or semicolon delimited string (depending on your email transport), i.e.
EXEC usp_MyProc @MsgToSend='Foo', @UserName=dbo.udf_getuserids('MyGroup')
where dbo.udf_getuserids uses COALESCE to return a string like:
"email1@domain.com, email2@domain.com, etc, etc"
Here's an example of a udf that returns a comma delimited list of entries from a table:
CREATE FUNCTION [dbo].[getbusunit]
(
@projectid int
)
RETURNS varchar(255)
AS
BEGIN
declare @unittext varchar(255)
SET @unittext = ''
select @unittext=coalesce(@unittext+',','')+unit_name
FROM Project_Unit_Association JOIN Unit_Names ON PU_UnitID=UnitID
WHERE <A href="mailto
U_ProjectID=@projectid">PU_ProjectID=@projectidif left(@unittext,1)=',' SET @unittext=right(@unittext,len(@unittext)-1)
set @unittext= rtrim(ltrim(@unittext))
RETURN(@unittext)
END
You have to trim off the last comma and I threw in the RTRIM/LTRIM just for the halibut. I'm sure Remi or one of the others can probably optimize this even more...
-
Unfortunately, changing the parameter from a single userID to a concatenated list is not an option, or I could use a UDF to return them to a parameter and pass that via EXEC.
Since I am at the third abstraction layer -
EXEC'ing an sproc that in turn performs a calculation/function and invokes a another sproc using EXEC and some of the same params -
I am not asking how to rewrite the overall functionality (and the underlying sprocs that are called) - simply confirming if there is a way to call an sProc with a parameter that is a result set (invoke it iteratively for each item in the resultset), without using cursors. For the purposes of the question, consider the first and second layers (sprocs) to be unmodifiable.
-
You can switch the cursor to a loop, but that's not gonna be a big improvement. How much logic is stored in those procs? Is is possible to consider moving all that logic directly to the main statement?
-
Yes, in this particular instance it would be possible to totally rewrite all 3 of the sprocs to accomplish this task.
The original question was mostly for my education/knowledge - as this was one example/sample that I could not think of a good cursor replacement or conversely, say - an example of when a cursor might be necessary.
There are times when we need to use/execute an existing sproc iteratively and rewriting is not an option.
-
It's really rare that you can't rewrite sps like that to make a single statement.
-
I agree Remi - and if this was heavily used / a source of contention, then I would. Mostly, I was trying to make sure I was not missing / failing to consider some other methods.
I think this highlights the point made earlier that procedural/object oriented 'thinking' can be detrimental when it comes to TSQL. I know that I have/continue to struggle with those differences. The sprocs I was referring to started out in typical OO fashion, abstracting functionality and creating methods (sprocs) - only to realize that what sounded good in theory, doesn't work out that way in practice.
I did not mean to hijack this thread - it was very informative and I appreciate the effort/input you and others provided.
-
OO is good for programming. What managers have to understand is that a db is not a programming tool, it's a data storage tool. They are not meant to accomplish the same tasks and they should not be assumed to be used in the same fashion.
-
But even then, maybe a TOP n and a loop over a result set... even using a temp with an identity col and just the primary keys (and not all the table) ... Everything to avoid a cursor
-
When in doubt test... you'd be surprised to see how fast that can run
. I know you never HAVE TO use a cursor... but sometimes it's a good solution. Sometimes = 0.0001% of the time . -
Colleagues
"The one and only case I'm aware of is this one : Let's say you want to get the nth line of a big query"
The principle is:
DECLARE @datStart datetime
DECLARE @datEnd datetime
SELECT @datStart = getdate()
DECLARE @n int
SELECT @n = 20
DECLARE @sql varchar(8000)
SELECT @sql = 'SELECT Top 1 L.Log_ID FROM ( SELECT TOP ' + CAST(@n AS varchar(10))
+ ' Log_ID FROM T_Archived_Incident_Logs ORDER BY Log_Id ) L'
+ ' ORDER BY L.Log_ID DESC'
EXEC(@SQL)
SELECT @datEnd = getdate()
SELECT 'RunTimeInMS' = DATEDIFF(millisecond, @datStart, @datEnd)
Log_ID is the PK, T_Archived_Incident_Logs has nearly 21 million rows
execution times for various n (SQL Server caching probably helped):
n = 20, RunTimeInMS = 0
n = 200, RunTimeInMS = 173
n = 2,000, RunTimeInMS = 110
n = 20,000, RunTimeInMS = 310
Just my $0.02 worth ... I like derived tables! ....
Russ
-
I said big query. Go with over 1 M records.
Viewing 11 posts - 61 through 70 (of 70 total)
You must be logged in to reply to this topic. Login to reply