Greetings and thanks for reading!
I was wondering what the best practice for encapsulating stored procedure logic using a set-based SQL technique.
For example, I have a product entry application that I am developing. While attempting to limit code duplication, I created a stored procedure called AddItem which creates a new product in the product tables. The downside is that in order to utilize this procedure in a situation that requires adding a group of products, I am forced to use a cursor or WHILE loop to execute the procedure in a "FOR EACH" type of way. This is resulting in very poor performance on large sets of items.
Of course I could hard-code the INSERT statements into the calling procedure but that makes me feel icky because the whole point is to be able to change (in one place) the "add item" logic. So if a column changed I would have to remember to change all of the INSERT statements in all of the places that use it. I just figured there has to be a better way of doing this and any advice would be appreciated.
EDIT: Quite right, I should provide a code example. Here is the contents of the AddItem procedure which is being executed on a MS SQL2005 database:
ALTER PROCEDURE [dbo].[AddItem]
@ProjectNumber int,
@ItemName varchar(255),
@SupplierID int,
@SKUType int,
@Store varchar(3),
@Category varchar(4),
@AddedBy varchar(255),
@ParentSKU varchar(255) = NULL,
@SetNumber int = NULL,
@NewItemNumber int OUTPUT
AS
SET NOCOUNT ON
DECLARE @DiscontinuedStatus bit
BEGIN TRY
BEGIN TRAN
SET @NewItemNumber = 0
INSERT INTO Produ开发者_运维技巧ctEntry.dbo.Items
(ProjectNumber, SetNumber, SKUType, Store, Category, AddedBy, ParentSKU, EntryTime)
VALUES(@ProjectNumber, @SetNumber, @SKUType, @Store, @Category, @AddedBy, @ParentSKU, CURRENT_TIMESTAMP)
SET @NewItemNumber = SCOPE_IDENTITY()
IF @SKUType = 1
BEGIN
SET @DiscontinuedStatus = 1
END
ELSE
BEGIN
SET @DiscontinuedStatus = 0
END
INSERT INTO ProductEntry.dbo.ItemInfo
(ItemNumber, ItemName, Discontinued)
VALUES (@NewItemNumber, @ItemName, @DiscontinuedStatus)
INSERT INTO ProductEntry.dbo.ItemSupplierInfo
(ItemNumber, SupplierID)
VALUES(@NewItemNumber, @SupplierID)
INSERT INTO ProductEntry.dbo.ItemWebInfo
(ItemNumber)
VALUES(@NewItemNumber)
INSERT INTO ProductEntry.dbo.ItemTags
(ItemNumber)
VALUES (@NewItemNumber)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
I have a need for a procedure that adds multiple items at a time (with numbers greater than 1000) and when using a cursor the performance is very bad. It takes over a minute to add a group of 800 products and it only gets worse from there.
EDIT: To further clarify the solution, the application allows items that have a parent-child relationship. When adding a new parent item, the user selects from a list of options and a set of child items is generated based on the option set.
For example, a user could create a product called "Awesome Boot" with an option set of Colors="Brown, Black", Size="10M, 11M, 12M", ToeStyle="SteelToe, SoftToe" - this would generate a set of 12 items. Obviously you can see how this could increase exponentially, considering that most boots have around 36 sizes, multiple colors and toe styles, as well as other options. This can result in a parent item with a large number of child items.
I guess one solution would be to combine all of the different item information into one "Items" table, eliminating the need for storing the same IDENTITY in multiple tables. I kind of like the convenience of splitting up the related data logically into different tables though. Maybe I'm trying to have my cake and eat it too!
Thanks! :)
This is just a stray comment - the two tables ItemWebInfo and ItemTags - unless those tables will eventually have multiple rows per entity, I think it would make a lot more sense (at least in most cases - there are always exceptions) if those columns were in the primary table. I also might suggest the same for the supplierInfo, unless an item can have more than one supplier the supplierID should just be a column in the primary table as well.
精彩评论