locked
alternative to cursor RRS feed

  • Question

  • User154448021 posted

    I have a table with 4 columns: ID, signId, storeNum, quantity with ID, SignId, and storeNum as composite primary key

    if storeNum is -1, that means the quantity is the default quantity.  If a store has a store-specific quantity, the storeNum is this store's number. 

    I need to show all rows that have specific quantity for a store, and all rows that have default quantity and of which the store does not have store-specific quantity

    currently I use table-value function with storeNum as parameter to return the list. first, I get all rows that have specific quantity for a store; then, I use cursor to loop through all rows that have default quantity and check each row if the store has a specific quantity.  It takes 9 seconds to run the function for a store. 

    The function is called within a stored procedure, which in turn is called by a function in a class, which in turn is called by a aspx code behind file.  It takes about 20 seconds to load the page.  20 seconds to load a page is too long.  I am trying to improve the performance. 

    I wonder if there is an alternative method to the cursor to get the second part of the list.

    Thanks.

    Thursday, August 19, 2010 12:49 PM

All replies

  • User1828997928 posted

    You certainly can do this without a cursor. Can you post the query? 

    Thursday, August 19, 2010 12:55 PM
  • User-1734649046 posted

    Hi,

    You can use Table Variables to speed up your operation. Please refer following

    http://msdn.microsoft.com/en-us/library/aa175774%28SQL.80%29.aspx

    http://odetocode.com/Articles/365.aspx

    hope this helps

    Thursday, August 19, 2010 12:58 PM
  • User154448021 posted
    You certainly can do this without a cursor. Can you post the query?  
     
    alter FUNCTION [dbo].[sg_functionTblStoreQty](@storeNum int)
    
    RETURNS @StoreQty TABLE 
    (
    	-- Add the column definitions for the TABLE variable here
    	Division varchar(50) not null
    	,Department varchar(50) null
    	,[Sub Department] varchar(50) null
    	,Class varchar(50) null
    	,[Sub Class] varchar(50) null
    	,StoreDivId int not null
    	,DEPARTMENT_NUMBER VARCHAR(15) NULL
    	,SUB_DEPARTMENT_NUMBER VARCHAR(15) NULL
    	,CLASS_NUMBER VARCHAR(15) NULL
    	,SUB_CLASS_NUMBER VARCHAR(15) NULL
    	,StoreNumber int not null
    	,DefaultQty int not null
    	,PRODUCT_HIERARCHY_ID NUMERIC(20,0) NOT NULL
    	,SignSize varchar(50) not null
    	,SortOrder int not null
    	,SignSizeId int null
    )
    AS
    BEGIN
    	-- Fill the table variable with the rows for your result set
    
    --first, insert all store defined qty rows
    insert @StoreQty
    SELECT [Division]
          ,[Department]
          ,[Sub Department]
          ,[Class]
          ,[Sub Class]
          ,[StoreDivId]
          ,[DEPARTMENT_NUMBER]
          ,[SUB_DEPARTMENT_NUMBER]
          ,[CLASS_NUMBER]
          ,[SUB_CLASS_NUMBER]
          ,[StoreNumber]
          ,[DefaultQty]
          ,[PRODUCT_HIERARCHY_ID]
          ,[SignSize]
          ,[SortOrder]
          ,[SignSizeId]
      FROM [dbo].[sg_vwStoreQtyList]
      where (StoreNumber = @storeNum) 
    
    --then, get corporate default qty that the store does not have a store-defined qty 
    declare @Division varchar(50) 
    declare @Department varchar(50) 
    declare @SubDepartment varchar(50) 
    declare @Class varchar(50) 
    declare @SubClass varchar(50) 
    declare @StoreDivId int 
    declare @DEPARTMENT_NUMBER VARCHAR(15) 
    declare @SUB_DEPARTMENT_NUMBER VARCHAR(15) 
    declare @CLASS_NUMBER VARCHAR(15) 
    declare @SUB_CLASS_NUMBER VARCHAR(15) 
    declare @StoreNumber int 
    declare @DefaultQty int 
    declare @PRODUCT_HIERARCHY_ID NUMERIC(20,0) 
    declare @SignSize varchar(50) 
    declare @SortOrder int 
    declare @SignSizeId int
    
      declare myCursor Cursor 
    		for 
    		SELECT [Division]
          ,[Department]
          ,[Sub Department]
          ,[Class]
          ,[Sub Class]
          ,[StoreDivId]
          ,[DEPARTMENT_NUMBER]
          ,[SUB_DEPARTMENT_NUMBER]
          ,[CLASS_NUMBER]
          ,[SUB_CLASS_NUMBER]
          ,[StoreNumber]
          ,[DefaultQty]
          ,[PRODUCT_HIERARCHY_ID]
          ,[SignSize]
          ,[SortOrder]
          ,[SignSizeId]
      FROM [dbo].[sg_vwStoreQtyList]
      where (StoreNumber =-1) 
    
    			open myCursor			
    				fetch next from myCursor into @Division,@Department,@SubDepartment,@Class,@SubClass,@StoreDivId,@DEPARTMENT_NUMBER,@SUB_DEPARTMENT_NUMBER,@CLASS_NUMBER,@SUB_CLASS_NUMBER,@StoreNumber,@DefaultQty,@PRODUCT_HIERARCHY_ID,@SignSize,@SortOrder,@SignSizeId
    				
    				while @@Fetch_Status = 0
    				begin						
    					-- find out if there is a store defined qty
    					declare @excessStoreNum int
    					set @excessStoreNum = (select count(ProductHierarchyId) from StoreQty where ProductHierarchyId = @Product_Hierarchy_Id and StoreNumber = @StoreNum and SignSizeId = @SignSizeId)
    						if @excessStoreNum = 0 -- if there is no store defined qty, use this corporate default qty
    						begin
    							insert @StoreQty
    							select @Division,@Department,@SubDepartment,@Class,@SubClass,@StoreDivId,@DEPARTMENT_NUMBER,@SUB_DEPARTMENT_NUMBER,@CLASS_NUMBER,@SUB_CLASS_NUMBER,@StoreNumber,@DefaultQty,@PRODUCT_HIERARCHY_ID,@SignSize,@SortOrder,@SignSizeId
    						end
    					fetch next from myCursor into @Division,@Department,@SubDepartment,@Class,@SubClass,@StoreDivId,@DEPARTMENT_NUMBER,@SUB_DEPARTMENT_NUMBER,@CLASS_NUMBER,@SUB_CLASS_NUMBER,@StoreNumber,@DefaultQty,@PRODUCT_HIERARCHY_ID,@SignSize,@SortOrder,@SignSizeId
    				end	
    			
    			close myCursor
    	deallocate myCursor
    
    	RETURN 
    END
    
    

     
    Thursday, August 19, 2010 1:02 PM