SQL Server Performance Forum – Threads Archive

Script that is running too long

I have a script that is running way too long. I an very new to t-sql and have it done and wokring but need to improve performance. Here is what I am doing. Hope it is ok to post the full script. Thanks in advance for your help. vmon CREATE PROCEDURE dbo.qryBomExplosion AS SET NOCOUNT ON – define the required temporary variables DECLARE @id_item_previous varchar(50) DECLARE @id_item_current varchar(50) DECLARE @current_level int DECLARE @qty_per decimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @qty_per_stackdecimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @id_item_select varchar(50) DECLARE @order_item_curcursor DECLARE @OrderNumbervarchar(50) DECLARE @LineNumbervarchar(50) DECLARE @LineQuantityint CREATE TABLE #stack ( stack_nbr INTEGER IDENTITY NOT NULL PRIMARY KEY, depth_level INTEGER NOT NULL, id_item_parent varchar(50) NULL, id_item_component varchar(50) NOT NULL, qty_per_stack decimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) DELETE FROM twrkBomExploded SET @order_item_cur = CURSOR STATIC FOR SELECT OrderNumber, LineNumber, Quantity FROM dbo.tblSchedule ORDER BY OrderNumber, LineNumber OPEN @order_item_cur IF @@Cursor_Rows <> 0 BEGIN FETCH NEXT FROM @order_item_cur INTO @OrderNumber, @LineNumber, @LineQuantity WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DELETE FROM #stack SET @id_item_select = (SELECT PartNo FROM dbo.tblSchedule WHERE dbo.tblSchedule.OrderNumber = @OrderNumber AND dbo.tblSchedule.LineNumber = @LineNumber) — define the initial values of the variables SET @id_item_previous = NULL SET @id_item_current= NULL SET @current_level = 0 INSERT INTO #stack VALUES (0, null, @id_item_select, @LineQuantity) — process items in the stack table — as long as there is still a processing level WHILE @current_level > -1 BEGIN IF EXISTS (SELECT * FROM #stack WHERE depth_level = @current_level) BEGIN – select highest level item from the stack SET ROWCOUNT 1 SELECT @id_item_current= id_item_component, @id_item_previous = id_item_parent, @qty_per_stack = qty_per_stack FROM #stack ORDER BY stack_nbr DESC SET ROWCOUNT 0 – calculate the bom qty_per for the new item IF @id_item_previous IS NULL BEGIN SET @qty_per = 1 END ELSE BEGIN SET @qty_per = @qty_per_stack END – write the item into the bom table IF @id_item_previous is null BEGIN INSERT INTO twrkBomExploded VALUES (@OrderNumber, @LineNumber, @current_level, @id_item_select, @id_item_select, @id_item_current, @qty_per) END ELSE BEGIN INSERT INTO twrkBomExploded VALUES (@OrderNumber, @LineNumber, @current_level, @id_item_select, @id_item_previous, @id_item_current, @qty_per) END – removed the process item from the stack DELETE FROM #stack WHERE depth_level = @current_level AND id_item_component = @id_item_current – insert into the stack any sub items for the current item INSERT INTO #stack SELECT @current_level + 1, fparent, fcomponent, (@qty_per_stack * fqty) FROM dbo.inboms WHERE fparent = @id_item_current IF @@ROWCOUNT > 0 BEGIN SET @current_level = @current_level + 1 END END ELSE BEGIN SET @current_level = @current_level – 1 END END INSERT INTO dbo.tblBomExploded ( ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT, QTY_PER_TOTAL ) SELECT ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT, SUM(QTY_PER) AS QTY_PER_TOTAL FROM dbo.twrkBomExploded GROUP BY ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT DELETE FROM dbo.twrkBomExploded FETCH NEXT FROM @order_item_cur INTO @OrderNumber, @LineNumber, @LineQuantity END END CLOSE @order_item_cur END Deallocate @order_item_cur /* — ADD TO tblBomExploded DELETE FROM dbo.tblBomExploded INSERT INTO dbo.tblBomExploded ( ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT, QTY_PER_TOTAL ) SELECT ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT, SUM(QTY_PER) AS QTY_PER_TOTAL FROM dbo.twrkBomExploded GROUP BY ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT */ DROP TABLE #stack GO

The very simplest way to resolve performance issues is to execute the query in the Query Analyzer and allow it to show you the Execution Plan. Simply go to the query menu, and press Show Execution Plan. Then run the query and when it returns the results there will be a tab that will show you exactly what was performed to achieve the results. Each part of your overall script will be in a separate plan and each will tell you the percentage of the overall time that it took to execute. If you have 10 items and 9 of them say ".0001%" and one says "99.999%" you know exactly which area of the overall script to focus on. Initially you want to look for things like "TABLE SCANS" or "INDEX SCANS" which are huge performance hogs, and can be remedied by adding the proper indexes to the tables. You add the index and rerun the command and can see what the net impact is. The other thing to look for that you might be able to speed things up with is "bookmark lookups" that take a huge chunk of time. If you have an index that is being used for a query, but say it has 4 fields, and the query needs to access 5 fields. If you add 1 more field to the index it (make it what is a called a "covering index") will no longer need to do the bookmark lookup. Most times the lookup isn’t that expensive and isn’t worth modifying the index. Occasionally though you’ll come across a table where the lookup may take 90% of the overall query. By adding 1 field or 2 fields to the index you can save a TON of valuable time. The second place to start is with the query itself. If you can do the same thing you are trying to do without the cursor, then you should change and remove it. Since you are new to sql server, I would focus on the indexes aspect of the query to see if you can speed up what is being done using the same logic. As you advance your skills you’ll find ways to do the same tasks in much quicker ways. By watching the execution plans you’ll be able to tweek things and then see side by side what is really be done and which ways of doing things are faster than others. Best of luck

I don’t have really time right now to try to figure out what exactly you are trying to accomplish. In general it is always better to try to find data set based instead of row by row solution. So if you are able to find one that solution will be probably much faster. To just improve current cursor solution you can try: – to include PartNo in columns returned by cursor instead selecting it in a loop for each row.
– to add an index on #stack(depthLevel, id_item_component) if there is big number of rows in #stack table expected at one point of execution, otherwise better use table variable (more probably)
– add an index on inboms(fparent) However, my concern is that whatever you are trying to accomplish you probably need to change your approach. Hope that helps.

]]>