avoid using cursors....

I am trying to rewrite a sp that I created years ago to avoid using cursors
so there is no problems with mutiprocessor systems and parallelism.
Simplifying, we have an order table and task table for each order:
CREATE TABLE [TOrders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NULL ,
CONSTRAINT [PK_TOrders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [TTasks] (
[OrderID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NOT NULL
CONSTRAINT [PK_TTasks] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TTasks_TOrders] FOREIGN KEY
(
[OrderID]
) REFERENCES [TOrders] (
[ID]
)
) ON [PRIMARY]
GO
The stored procedure under analysis consists of copying (inserting) all the
tasks already existing for an order to another one. Since ID field in TTasks
is not identity, we should retrieve the current maximum ID for the
destination order and continue the insertions from that ID onwards. The SP
as it is now follows:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int,
@.Description varchar(64)
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
-- Cursor to iterate through source taks
DECLARE my_cursor CURSOR LOCAL FOR
SELECT TTasks.Description FROM TTasks
WHERE TTasks.OrderID= @.fromO
ORDER BY TTasks.ID
-- This is a simple iteration to insert tasks but starting at @.i instead
starting at 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @.Description
WHILE (@.@.FETCH_STATUS <> -1) BEGIN
IF (@.@.FETCH_STATUS <> -2) BEGIN
SET @.i = @.i + 1
INSERT INTO TTasks (OrderID, ID, Description)
VALUES (@.toO, @.i, @.Description)
END
FETCH NEXT FROM my_cursor INTO @.Description
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
What I am trying to do is replace the cursor used there by a single insert
statement such as:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ****, Description FROM TTasks
WHERE TTasks.OrderID = @.fromO
ORDER BY TTasks.ID
GO
What is driving me crazy is how to calculate the field marked with ****
Can someone help me? Is there any way to do it without having ID field in
TTasks being an identity? It cannot be an identity since it must start with
1 for every new order.
Thanks in advance.jagb (jagb@.NOSPAM.com) writes:
> What is driving me crazy is how to calculate the field marked with ****
> Can someone help me? Is there any way to do it without having ID field
> in TTasks being an identity? It cannot be an identity since it must
> start with 1 for every new order.
A very simple-minded solution is to bounce the data over a temp table
with an IDENTITY column.
A more "relational" solution is to add a table of numbers to the database.
This is a one-column table that holds all numbers from 1 up to some limit.
Personally, I sort of favour the temp-table solution, as it is more
robust. You don't risk to run out of numbers. Then again, there is a
performance cost for using an extra table, so for this case I might go
for a table of numbers.
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|||You could use a correlated subquery to generate the ranking value within a
SELECT statement like:
INSERT tasks ( ... )
SELECT @.to, ( SELECT COUNT(*)
FROM tasks t2
WHERE t2.orderID = t1.Orderid
AND t2.id <= t1.id )
FROM tasks t1
WHERE t1.orderid = @.p ;
The assumption is that the id values are unique for each Orderid. This is
untested, if you'd like a tested one, please post a few sample data along
with expected results.
Anith|||TOrders (ID, Desc)
1 First Order
2 Second Order
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
After having run the SP to copy tasks from order=1 to order=2 the tasks
table should contain the following values:
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
2 5 a <-- these are the inserted records
2 6 b <-- these are the inserted records
2 7 c <-- these are the inserted records
This is not achieved using your statement wich will incorrectly try to
insert the values:
2 1 a
2 2 b
2 9 c
And will raise a primary key conflict, since the keys (OrderID ID) valued
to (2 1), (2 2) and (2 3) already exist in TTasks.
Note that the (1 9 c) record should be converted to (2 7 c) when
copied along for order 2.
Mhh... it seems that your approach almost hit in the nail... after having
done some tests I have found the solution:
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO,
(SELECT ISNULL(COUNT(*), 0)
FROM dbo.TTasks t2
WHERE (OrderID = t1.OrderID) AND (ID <=
t1.ID)) +
(SELECT ISNULL(MAX(ID), 0)
FROM dbo.TTasks
WHERE (OrderID = @.toO)), Descripcion
FROM dbo.TTasks t1
WHERE (OrderID = @.fromO)
Thanks for your help, Anith.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> You could use a correlated subquery to generate the ranking value within a
> SELECT statement like:
> INSERT tasks ( ... )
> SELECT @.to, ( SELECT COUNT(*)
> FROM tasks t2
> WHERE t2.orderID = t1.Orderid
> AND t2.id <= t1.id )
> FROM tasks t1
> WHERE t1.orderid = @.p ;
> The assumption is that the id values are unique for each Orderid. This is
> untested, if you'd like a tested one, please post a few sample data along
> with expected results.
> --
> Anith
>|||jagb (jagb@.NOSPAM.com) writes:
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
Beware, though, that nested subqueries in the SELECT list often gives
poor performance. Certinly better than your cursor, it can be considerably
slower than bouncing over a temp table, or using a table of numbers.
Then again, it depends on how many rows you insert at time. If it is
< 100, the difference may not be measurable.
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|||You can also use a Table Variable with an IDENTITY column. I like them
better than temp tables for generating row numbers because unlike temp
tables, modifications to table variables don't share the same transaction
space as changes to permanent tables which can reduce the performance hit of
using the extra table. They also reduce recompiles and can reduce
contention on the system table indexes in tempdb.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97428F2528D48Yazorman@.127.0.0.1...
> jagb (jagb@.NOSPAM.com) writes:
> A very simple-minded solution is to bounce the data over a temp table
> with an IDENTITY column.
> A more "relational" solution is to add a table of numbers to the database.
> This is a one-column table that holds all numbers from 1 up to some limit.
> Personally, I sort of favour the temp-table solution, as it is more
> robust. You don't risk to run out of numbers. Then again, there is a
> performance cost for using an extra table, so for this case I might go
> for a table of numbers.
>
> --
> 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|||I think bouncing the values off a temporary object will be faster than a
correlated subquery:
DECLARE @.T TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Description VARCHAR(64) NOT NULL
)
BEGIN TRAN
INSERT @.T (Description)
SELECT Description
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block updates to source order
WHERE OrderID = @.fromO
ORDER BY ID
IF @.@.ROWCOUNT > 0
BEGIN
SELECT @.i = ISNULL(MAX(ID), 0)
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block appending to destination
order
WHERE OrderID = @.to0
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ID + @.i, Description FROM @.T
END
COMMIT TRAN
"jagb" <jagb@.NOSPAM.com> wrote in message
news:esRjDsgEGHA.2704@.TK2MSFTNGP15.phx.gbl...
> TOrders (ID, Desc)
> 1 First Order
> 2 Second Order
>
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> After having run the SP to copy tasks from order=1 to order=2 the tasks
> table should contain the following values:
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> 2 5 a <-- these are the inserted records
> 2 6 b <-- these are the inserted records
> 2 7 c <-- these are the inserted records
> This is not achieved using your statement wich will incorrectly try to
> insert the values:
> 2 1 a
> 2 2 b
> 2 9 c
> And will raise a primary key conflict, since the keys (OrderID ID) valued
> to (2 1), (2 2) and (2 3) already exist in TTasks.
> Note that the (1 9 c) record should be converted to (2 7 c)
> when copied along for order 2.
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
> "Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
> news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>