|
|
You're in the
sql server programming
group:Cursor is the only choice?
sql server programming:
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
[quoted text, click to view]
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).
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?
[quoted text, click to view]
Frank Lee wrote: > 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... 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 --
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... [quoted text, click to view] > 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... [quoted text, click to view] > 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 > -- >
Frank Lee (Reply@to.newsgroup) writes:
[quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> ???????:1136119382.878745.243360@f14g2000cwb.googlegroups.com... [quoted text, click to view] >> >> "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. Thanks, I use SQL Agent Job. [quoted text, click to view] > 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. [quoted text, click to view] > 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. [quoted text, click to view] > 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. [quoted text, click to view] > > > 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. [quoted text, click to view] > -- > David Portas > SQL Server MVP > -- >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |