Cursor is the only choice?

31 Dec 2005 6:38 AM

Frank Lee

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?

31 Dec 2005 7:23 AM

Razvan Socol

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

1 Jan 2006 2:24 AM

Frank Lee

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" <rso***@gmail.com>

???????:1136013807.311645.156***@g49g2000cwa.googlegroups.com...
Show quote

> 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
>

1 Jan 2006 11:14 AM

Erland Sommarskog

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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Jan 2006 12:43 PM

David Portas

Frank Lee wrote:
Show quote

> 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" <rso***@gmail.com>
> ???????:1136013807.311645.156***@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
--

1 Jan 2006 2:39 PM

Frank Lee

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org>

???????:1136119382.878745.243***@f14g2000cwb.googlegroups.com...

>>
>> "Razvan Socol" <rso***@gmail.com>
>> ???????:1136013807.311645.156***@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.

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.

Show quote

> --
> David Portas
> SQL Server MVP
> --
>

31 Dec 2005 9:20 AM

David Portas

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
--

1 Jan 2006 2:26 AM

Frank Lee

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_dpor***@acm.org>

???????:1136020809.428204.141***@o13g2000cwo.googlegroups.com...
Show quote

> 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
> --
>

31 Dec 2005 8:54 PM

--CELKO--

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).