If I want to do a process in a sequence order, is Cursor my only choice?
for example,
----
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from table
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoSomething @.ID
end
close MyCursor
deallocate MyCursor
----
In fact, I have found a way which does NOT work as followed,
select @.Null=dbo.DoSomething(id)
from table
where created>thedate
order by created
As I just metioned, it does not work. There are too many restrictions in the
function declaration. No newid(), no xxxxxx, no xxxxxx, too many
restrictions! But the code is short and clear. I like.
So I would like to know are there another choices for me in this simple
example?Hi, Frank
You can do something this:
declare @.ID int
set @.ID=(
select top 1 id from table
where created>thedate
order by created, id
)
while @.ID is not null begin
exec DoSomething(@.ID)
set @.ID=(
select top 1 id from table
where created>thedate
and id>@.ID
order by created, id
)
end
But this method would not have a better performance than a cursor. The
real solution is to rewrite the DoSomething procedure to process all
rows at once. That's what SQL is for: set-based operations. Please tell
us what DoSomething is supposed to do, or post the actual procedure,
along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
...), so we can really help.
Razvan|||Frank Lee wrote:
> If I want to do a process in a sequence order, is Cursor my only choice?
That depends what the process is. What is the thing represented by
"DoSomething" in your example code? If it is just more data
manipulation then there's a good chance that it can be done without
using a cursor.
David Portas
SQL Server MVP
--|||Go to http://www.sciencecartoonsplus.com/gallery.htm. That is what
your "exec DoSomething @.ID" is like!
I doubt you really need a cursor. In my career (`20 years of SQL
coding, 35 years total) I have written five cursors in production SQL
code; I know that if I had the CASE expression back in the old days, I
know I could have avoided three of them.
We will need more details and probably have to re-write your DDL (good
SQL programmers do not use uniqueidentifier, and names like "id" or
"@.null" even in pseudo-code).|||The Scenario for me is an (I Called) asynchornous Request-and-Process model.
--DDL
Create table Request (
ID uniqueidentifier primary key default newid(),
types int not null, --RequestType, depend on use case, not
important here.
data varchar(255) not null, --pseudo code, maybe many columns,
depend.
created datetime not null default getdate(),
done bit not null default 0
)
go
create trigger xxxxxxxxxxx on Request for insert as
begin
--Do nothing or DoSomethings which will complete in short time
--And then activate a job which process the Request
exec sp_start job xxxxxxxxxxxxxxxxxxxx
end
go
Client-Side (C/S or asp.net) insert something by using
insert into Request (types, data)
The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
or activate by Request_Insert_Trigger. There is nothing to do for the job
but just exec MyProcessSP. In MyProcessSP, there is a loop to call
DoMyProcessSP @.ID as descripted in last post.
create procedure DoMyProcessSP (@.ID uniqueidentifier) as
begin
declare @.types int
declare @.data varchar(255)
--pseudo code
select @.types=types, @.data=data
from Request
where id=@.id
if @.types=0
begin
insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else if @.type=1
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
else
begin
insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
values (data)
exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
complex and take a long time.
end
update request
set Done=1
where id=@.id
end
go
create procedure MyProcessSP as --A caller in sequent order
begin
--pseudo code
declare @.ID uniqueidentifier
declare MyCursor cursor local for
select id
from request
where created>thedate
order by created
for read only
open MyCursor
while (1=1)
begin
fetch next from MyCursor into @.ID
if @.@.fetch_status<>0
break
exec DoMyProcessSP @.ID
end
close MyCursor
deallocate MyCursor
end
go
"Razvan Socol" <rsocol@.gmail.com>
'?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
> Hi, Frank
> You can do something this:
> declare @.ID int
> set @.ID=(
> select top 1 id from table
> where created>thedate
> order by created, id
> )
> while @.ID is not null begin
> exec DoSomething(@.ID)
> set @.ID=(
> select top 1 id from table
> where created>thedate
> and id>@.ID
> order by created, id
> )
> end
> But this method would not have a better performance than a cursor. The
> real solution is to rewrite the DoSomething procedure to process all
> rows at once. That's what SQL is for: set-based operations. Please tell
> us what DoSomething is supposed to do, or post the actual procedure,
> along with DDL (CREATE TABLE-s) and sample data (INSERT INTO ... VALUES
> ...), so we can really help.
> Razvan
>|||There is a more detail example code in the post I reply to Razvan.
If you do have a good suggestion, please read that post. thx.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136020809.428204.141060@.o13g2000cwo.googlegroups.com...
> Frank Lee wrote:
>
> That depends what the process is. What is the thing represented by
> "DoSomething" in your example code? If it is just more data
> manipulation then there's a good chance that it can be done without
> using a cursor.
> --
> David Portas
> SQL Server MVP
> --
>|||Frank Lee (Reply@.to.newsgroup) writes:
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
>...
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
>...
So we still can't tell whether you can rewrite this into set-based code.
However, it cannot be denied that there is a trade-off. Even in T-SQL
it is easier to express logic scalarly, handling one row at a time. For
starters, if you want to split code between stored procedures, it's
easy to pass parameters, but you can only pass scalar parameters, not
tables. (Procedures can still share data over tables, see my article
http://www.sommarskog.se/share_data.html for some techniques.)
Rewriting existing code that uses iterative processesing into set-based
can for complex cases be quite an effort. I rewrote a central procedure
of our system in October/November, and it spent over 80 hours on that,
including testing. We have more that we need to rewrite, and the total
estimate is over 500 hours.
Obviously, there is a trade-off. As long as performance is acceptable
for the task, it can be difficult to justify a rewrite, but be prepared
that in some point in the life-time of the system, the situation may
become untenable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Frank Lee wrote:
> The Scenario for me is an (I Called) asynchornous Request-and-Process mode
l.
> --DDL
> Create table Request (
> ID uniqueidentifier primary key default newid(),
> types int not null, --RequestType, depend on use case, not
> important here.
> data varchar(255) not null, --pseudo code, maybe many columns,
> depend.
> created datetime not null default getdate(),
> done bit not null default 0
> )
> go
> create trigger xxxxxxxxxxx on Request for insert as
> begin
> --Do nothing or DoSomethings which will complete in short time
> --And then activate a job which process the Request
> exec sp_start job xxxxxxxxxxxxxxxxxxxx
> end
> go
> Client-Side (C/S or asp.net) insert something by using
> insert into Request (types, data)
> The job named xxxxxxxxxxxxxxxxxxxx will auto activate by system every hour
> or activate by Request_Insert_Trigger. There is nothing to do for the job
> but just exec MyProcessSP. In MyProcessSP, there is a loop to call
> DoMyProcessSP @.ID as descripted in last post.
> create procedure DoMyProcessSP (@.ID uniqueidentifier) as
> begin
> declare @.types int
> declare @.data varchar(255)
> --pseudo code
> select @.types=types, @.data=data
> from Request
> where id=@.id
> if @.types=0
> begin
> insert into Another_A table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessA @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else if @.type=1
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessB @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> else
> begin
> insert into Another_B table (xxxxxxxxxxxxxxxxxxxx)
> values (data)
> exec DoAnotherProcessC @.ID --Depend on business rule. Maybe very
> complex and take a long time.
> end
> update request
> set Done=1
> where id=@.id
> end
> go
> create procedure MyProcessSP as --A caller in sequent order
> begin
> --pseudo code
> declare @.ID uniqueidentifier
> declare MyCursor cursor local for
> select id
> from request
> where created>thedate
> order by created
> for read only
> open MyCursor
> while (1=1)
> begin
> fetch next from MyCursor into @.ID
> if @.@.fetch_status<>0
> break
> exec DoMyProcessSP @.ID
> end
> close MyCursor
> deallocate MyCursor
> end
> go
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1136013807.311645.156520@.g49g2000cwa.googlegroups.com...
Here's a slightly different approach that has worked for me in the
past. Use your scheduled job or some other scheduled prgram to pull
processes from your request table one at a time. That way you can scale
it by spawning new threads, each of which will take the next pending
request. Also it's probably easier to handle and debug errors if each
job execution is associated with only a single request at a time. You
can use TOP 1 to retrieve the next request. If your processes need to
be serialized then you can use an extra attribute to group those so
that they are taken as a sequence.
I am assuming that these requests are some unrelated and inherently
procedural tasks rather than straight data-manipulation, otherwise
there may be better solutions without cursors or procedural code.
In SQL Server 2005 we have the Service Broker architecture to take care
of messaging and queueing functionality. Take a look at Service Broker
if you haven't already.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
'?:1136119382.878745.243360@.f14g2000cwb.googlegroups.com...
> Here's a slightly different approach that has worked for me in the
> past. Use your scheduled job or some other scheduled prgram to pull
> processes from your request table one at a time.
Thanks, I use SQL Agent Job.
> That way you can scale
> it by spawning new threads, each of which will take the next pending
> request. Also it's probably easier to handle and debug errors if each
> job execution is associated with only a single request at a time.
Yes, I agree.
> You
> can use TOP 1 to retrieve the next request. If your processes need to
> be serialized then you can use an extra attribute to group those so
> that they are taken as a sequence.
>
Good point. Thx.
> I am assuming that these requests are some unrelated and inherently
> procedural tasks rather than straight data-manipulation, otherwise
> there may be better solutions without cursors or procedural code.
>
Yes, they are unrelated.
>
> In SQL Server 2005 we have the Service Broker architecture to take care
> of messaging and queueing functionality. Take a look at Service Broker
> if you haven't already.
>
Yes, I know. I have test it, and try to rewrite one implementation which
used to use SQL Agent job to implement. However, I find Service Broker is
too BIG for me. I would like, and am planning, to use it to do another big
things. Thanks anyway.
> --
> David Portas
> SQL Server MVP
> --
>
sql
