Script that is running too long | SQL Server Performance Forums

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<br /><br />CREATE PROCEDURE dbo.qryBomExplosion<br />AS<br />SET NOCOUNT ON<br />– define the required temporary variables<br /><br />DECLARE @id_item_previous varchar(50)<br />DECLARE @id_item_current varchar(50)<br />DECLARE @current_level int<br />DECLARE @qty_per decimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @qty_per_stackdecimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DECLARE @id_item_select varchar(50)<br />DECLARE @order_item_curcursor<br />DECLARE @OrderNumbervarchar(50)<br />DECLARE @LineNumbervarchar(50)<br />DECLARE @LineQuantityint<br /><br />CREATE TABLE #stack <br />(<br />stack_nbr INTEGER IDENTITY NOT NULL PRIMARY KEY,<br />depth_level INTEGER NOT NULL,<br />id_item_parent varchar(50) NULL,<br />id_item_component varchar(50) NOT NULL,<br />qty_per_stack decimal(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />)<br />DELETE FROM twrkBomExploded<br /><br />SET @order_item_cur = CURSOR STATIC FOR SELECT OrderNumber, LineNumber, Quantity FROM dbo.tblSchedule ORDER BY OrderNumber, LineNumber<br />OPEN @order_item_cur<br />IF @@Cursor_Rows &lt;&gt; 0<br />BEGIN<br />FETCH NEXT FROM @order_item_cur INTO @OrderNumber, @LineNumber, @LineQuantity<br />WHILE (@@FETCH_STATUS &lt;&gt; -1)<br />BEGIN<br />IF (@@FETCH_STATUS &lt;&gt; -2)<br />BEGIN<br />DELETE FROM #stack<br />SET @id_item_select = (SELECT PartNo FROM dbo.tblSchedule WHERE dbo.tblSchedule.OrderNumber = @OrderNumber AND dbo.tblSchedule.LineNumber = @LineNumber)<br /><br /> — define the initial values of the variables<br /> SET @id_item_previous = NULL<br /> SET @id_item_current= NULL<br /> SET @current_level = 0<br /> INSERT INTO #stack VALUES (0, null, @id_item_select, @LineQuantity)<br /><br /> — process items in the stack table<br /> — as long as there is still a processing level<br /> WHILE @current_level &gt; -1<br />BEGIN<br />IF EXISTS (SELECT * FROM #stack WHERE depth_level = @current_level)<br />BEGIN<br />– select highest level item from the stack<br />SET ROWCOUNT 1<br /><br />SELECT @id_item_current= id_item_component, @id_item_previous = id_item_parent, @qty_per_stack = qty_per_stack<br />FROM #stack<br />ORDER BY stack_nbr DESC<br /><br />SET ROWCOUNT 0<br /><br />– calculate the bom qty_per for the new item<br />IF @id_item_previous IS NULL<br />BEGIN<br />SET @qty_per = 1<br />END<br />ELSE<br />BEGIN<br />SET @qty_per = @qty_per_stack<br />END<br /><br />– write the item into the bom table<br /><br />IF @id_item_previous is null<br />BEGIN<br />INSERT INTO twrkBomExploded<br />VALUES (@OrderNumber, @LineNumber, @current_level, @id_item_select, @id_item_select, @id_item_current, @qty_per)<br />END<br />ELSE<br />BEGIN<br />INSERT INTO twrkBomExploded<br />VALUES (@OrderNumber, @LineNumber, @current_level, @id_item_select, @id_item_previous, @id_item_current, @qty_per)<br />END<br /><br />– removed the process item from the stack<br /><br />DELETE FROM #stack<br />WHERE depth_level = @current_level<br />AND id_item_component = @id_item_current<br /><br />– insert into the stack any sub items for the current item<br /><br />INSERT INTO #stack<br />SELECT @current_level + 1, fparent, fcomponent, (@qty_per_stack * fqty)<br />FROM dbo.inboms<br />WHERE fparent = @id_item_current<br /><br />IF @@ROWCOUNT &gt; 0<br />BEGIN<br />SET @current_level = @current_level + 1<br />END<br />END<br />ELSE<br />BEGIN<br />SET @current_level = @current_level – 1<br />END<br />END<br /><br />INSERT INTO dbo.tblBomExploded<br />(<br />ORDER_NUMBER,<br />LINE_NUMBER,<br />BOM_LEVEL, <br />ID_ITEM_PARENT_TOP_LEVEL, <br />ID_ITEM_PARENT, <br />ID_ITEM_COMPONENT, <br />QTY_PER_TOTAL<br />)<br />SELECT <br />ORDER_NUMBER,<br />LINE_NUMBER,<br />BOM_LEVEL, <br />ID_ITEM_PARENT_TOP_LEVEL, <br />ID_ITEM_PARENT, <br />ID_ITEM_COMPONENT, <br />SUM(QTY_PER) AS QTY_PER_TOTAL<br />FROM dbo.twrkBomExploded<br />GROUP BY ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT<br /><br />DELETE FROM dbo.twrkBomExploded<br /><br />FETCH NEXT FROM @order_item_cur INTO @OrderNumber, @LineNumber, @LineQuantity<br />END<br />END<br />CLOSE @order_item_cur END<br /><br />Deallocate @order_item_cur<br /><br /><br />/*<br /> — ADD TO tblBomExploded<br /><br />DELETE FROM dbo.tblBomExploded<br /><br />INSERT INTO dbo.tblBomExploded<br />(<br />ORDER_NUMBER,<br />LINE_NUMBER,<br />BOM_LEVEL, <br />ID_ITEM_PARENT_TOP_LEVEL, <br />ID_ITEM_PARENT, <br />ID_ITEM_COMPONENT, <br />QTY_PER_TOTAL<br />)<br />SELECT <br />ORDER_NUMBER,<br />LINE_NUMBER,<br />BOM_LEVEL, <br />ID_ITEM_PARENT_TOP_LEVEL, <br />ID_ITEM_PARENT, <br />ID_ITEM_COMPONENT, <br />SUM(QTY_PER) AS QTY_PER_TOTAL<br />FROM dbo.twrkBomExploded<br />GROUP BY ORDER_NUMBER, LINE_NUMBER, BOM_LEVEL, ID_ITEM_PARENT_TOP_LEVEL, ID_ITEM_PARENT, ID_ITEM_COMPONENT<br />*/<br /><br />DROP TABLE #stack<br />GO<br />
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.