October 5, 2011 at 8:46 am
My current project for performance improvement of a database includes changing from a sequence generated by inserting into a table with an identity column, retrieving the identity value created, deleting the row from the table and then using the generated value in the insert. This takes place in an SP like this:
CREATE PROCEDURE dbo.get_order_id (@order_id INT OUTPUT)
AS
BEGIN;
INSERT INTO dbo.generate_order_id
(dummy)
VALUES
(0) ;
SELECT
@order_id = SCOPE_IDENTITY() ;
DELETE FROM
dbo.generate_order_id
WHERE
id = @order_id ;
RETURN ;
END ;
This is used like this:
CREATE PROCEDURE dbo.insert_order (@customer_id INT, @order_date SMALLDATETIME)
AS
BEGIN;
DECLARE @order_id INT;
EXEC dbo.generate_order_id @order_id OUTPUT;
INSERT INTO dbo.orders
(
order_id,
customer_id,
order_date
)
VALUES
(
@order_id,
@customer_id,
@order_date
)
RETURN ;
END ;
As you can imagine this causes deadlocks. The fix is to change the order_id column in the orders table to be an identity column, since that's all the code is doing anyway.
You can't just change a column to identity, you have to re-create the table. When using SSMS to generate the script for this you get something like this:
/*
Wednesday, October 05, 201110:31:34 AM
User:
Server: .
Database: Play
Application:
*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/* Start process with Set options Transaction 1 */
BEGIN TRANSACTION -- SET options Transaction 1
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT -- SET options Transaction 1
/* Drop FK to Customers Transaction 2 */
BEGIN TRANSACTION --
GO
ALTER TABLE dbo.orders
DROP CONSTRAINT FK_orders_customers
GO
ALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)
GO
COMMIT -- Drop FK to Customers Transaction 2
/* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_orders
(
order_id int NOT NULL IDENTITY (1, 1),
customer_id int NOT NULL,
order_date smalldatetime NOT NULL
)
GO
ALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_orders ON
GO
IF EXISTS(SELECT * FROM dbo.orders)
EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_orders OFF
GO
ALTER TABLE dbo.order_items
DROP CONSTRAINT FK_order_items_order_items
GO
DROP TABLE dbo.orders
GO
EXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT'
GO
ALTER TABLE dbo.orders ADD CONSTRAINT
PK_orders PRIMARY KEY CLUSTERED
(
order_id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
ALTER TABLE dbo.orders ADD CONSTRAINT
FK_orders_customers FOREIGN KEY
(
customer_id
) REFERENCES dbo.customers
(
customer_id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT -- create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3
/* add FK to order_items Transaction 4 */
BEGIN TRANSACTION --
GO
ALTER TABLE dbo.order_items ADD CONSTRAINT
FK_order_items_order_items FOREIGN KEY
(
order_id
) REFERENCES dbo.orders
(
order_id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)
GO
COMMIT -- add FK to order_items Transaction 4
AS you can see this sets up 4 separate transactions. I'm wondering if I should modify it to be one transaction so that if there is an error somewhere, the whole thing gets rolled back. This is what I'm thinking:
BEGIN TRY
SET XACT_ABORT ON;
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
ALTER TABLE dbo.orders
DROP CONSTRAINT FK_orders_customers
ALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)
/* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */
CREATE TABLE dbo.Tmp_orders
(
order_id int NOT NULL IDENTITY (1, 1),
customer_id int NOT NULL,
order_date smalldatetime NOT NULL
)
ALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)
SET IDENTITY_INSERT dbo.Tmp_orders ON
IF EXISTS(SELECT * FROM dbo.orders)
EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')
SET IDENTITY_INSERT dbo.Tmp_orders OFF
ALTER TABLE dbo.order_items
DROP CONSTRAINT FK_order_items_order_items
DROP TABLE dbo.orders
EXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT'
ALTER TABLE dbo.orders ADD CONSTRAINT
PK_orders PRIMARY KEY CLUSTERED
(
order_id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE dbo.orders ADD CONSTRAINT
FK_orders_customers FOREIGN KEY
(
customer_id
) REFERENCES dbo.customers
(
customer_id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
/* add FK to order_items */
ALTER TABLE dbo.order_items ADD CONSTRAINT
FK_order_items_order_items FOREIGN KEY
(
order_id
) REFERENCES dbo.orders
(
order_id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
ALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)
IF XACT_STATE() = 0
BEGIN;
COMMIT TRANSACTION;
END;
ELSE
BEGIN;
ROLLBACK TRANSACTION;
END;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER(),
ERROR_LINE(),
ERROR_MESSAGE(),
ERROR_SEVERITY(),
ERROR_STATE()
IF XACT_STATE <> 0
ROLLBACK TRANSACTION;
END CATCH
There will also be SP changes to be made to eliminate the call to the SP that generates the id and to remove the insertion of order_id into the orders table since it is now an identity column.
What do you think?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2011 at 10:18 am
First thing, a single transaction is better.
Secondly, I recommend renaming the old table instead of dropping immediately just in case.
Third, are there indexes, triggers, etc. that also need to be created on the new table?
October 5, 2011 at 10:36 am
Robert Davis (10/5/2011)
First thing, a single transaction is better.
That's what I think as well. The only issue is length of transaction, but consistency overrides concurrency in my opinion.
Secondly, I recommend renaming the old table instead of dropping immediately just in case.
That's what I think as well. I just posted what is created by SSMS when you script the change made in the GUI.
Third, are there indexes, triggers, etc. that also need to be created on the new table?
I didn't add any indexes to the table in my example just to keep it simpler and shorter. If there are indexes, script changes does include the indexes. I haven't tried with a trigger yet. I'll have to check on that. I don't think there are any triggers on any tables I have to make the changes on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply