none
Fast searching via sqlserver stored procuedure RRS feed

  • Question

  •  Hi friends,


    Following sqlserver 2012 stored procedure I am calling from vb.net which is used for product searching by code and by name and same time I am querying  from inv_balance table, I am encounting problem its very slow as I have about 20000 items, please help me how i can optimized so it will be fast thank you.

    ALTER PROCEDURE [dbo].[stockdetail]
    (

    @search_string nvarchar(24),@mstoreid nvarchar(3),@mcityid nvarchar(3),@mregionid nvarchar(3),@search_type nvarchar(12)
    )

    AS
    BEGIN

    DECLARE @PartNO nvarchar(24);
    DECLARE @itemName nvarchar(100);
    DECLARE @Brand nvarchar(60);
    DECLARE @Origion nvarchar(60);
    DECLARE @Location nvarchar(60);
    DECLARE @Qty nvarchar(10);
    declare @producttype nvarchar(20);

    SET NOCOUNT ON;

    Declare @sttable TABLE(

    [item_code] bigint NULL,
    [item_description] [nvarchar](100) NULL,
    product_type nvarchar(20) null,
    [qty_on_hand] [int] NULL

    )
    if @search_type='ProductNo'

    begin
    DECLARE mycursor CURSOR FAST_FORWARD
    FOR 
    select 
     item_code,
    item_description,
    Product_type
    from    product_MASTER  
    where LTRIM(RTRIM(item_code)) like  @search_string +'%'
    and active_flag='Yes'

    OPEN mycursor
     FETCH NEXT FROM myCursor INTO   @PartNO, @itemName,@producttype;

    WHILE (@@FETCH_STATUS = 0 )
    BEGIN 
    set @qty=0;

    SELECT @qty=SUM(isnull(qty_in_hand,0))   FROM inv_balance 
    where product_number=@PartNO

    insert into @sttable values  (@partno,@itemName,@producttype,@qty)

     FETCH NEXT FROM myCursor INTO   @PartNO, @itemName,@producttype;

    end

    CLOSE myCursor ;
    DEALLOCATE myCursor;
    end 


    if @search_type= 'ProductName'



    begin

    DECLARE mycursor CURSOR FAST_FORWARD
    FOR 

    select 
     item_code,
    item_description,
    product_type
    from    product_MASTER  

    where LTRIM(RTRIM(item_description)) like @search_string+'%'
    and active_flag='Yes'


    OPEN mycursor
     FETCH NEXT FROM myCursor INTO   @PartNO, @itemName,@producttype;

    WHILE (@@FETCH_STATUS = 0 )
    BEGIN 

    set @qty=0;

    SELECT @qty=isnull(SUM(isnull(qty_in_hand,0)),0)   FROM inv_balance 
    where product_number=@PartNO



    insert into @sttable values( @partno,@itemName,@producttype,@qty)


     FETCH NEXT FROM myCursor INTO    @PartNO, @itemName,@producttype;

    end

    CLOSE myCursor ;
    DEALLOCATE myCursor;
    end 




    select * from @sttable


    end

      

    asad

    Thursday, May 30, 2019 10:32 AM

Answers

  • Generally speaking, a cursor will NOT boost performance. That is a RBAR approach that is typically incompatible with the design and intent of sql and a relational database. However, feel free to implement both approaches and compare the execution plans - that will be a far more illuminating experience than just reading such things. 
    • Marked as answer by malikasad Thursday, May 30, 2019 3:21 PM
    Thursday, May 30, 2019 2:45 PM

All replies

  • So what madness convinced you to use a cursor? This appears to be a simple query that can be accomplished with a single select statement. And for the sake of future generations, BE CONSISTENT

    select 
       prod.item_code,
       prod.item_description,
       prod.Product_type
       sum(isnull(bal.qty_in_hand, 0) as qty_on_hand
    from dbo.product_MASTER as prod
    left join dbo.inv_balance as bal
      on bal.product_number= prod.item_code 
    where prod.item_code like  @search_string +'%'
      and prod.active_flag='Yes'
    
    
    

    Why do you join on columns that have different names? It is far easier to understand a schema where common columns (like foreign keys/primary keys) have the same name in the related tables. 
    • Edited by scott_morris-ga Thursday, May 30, 2019 11:43 AM premature button click
    Thursday, May 30, 2019 11:41 AM
  • thank you for reply, I was under impression using cursor it will boost the performance, in future inv_balance table will have more rows against one master record in product master this can slow down or no matter

    asad

    Thursday, May 30, 2019 1:50 PM
  • Generally speaking, a cursor will NOT boost performance. That is a RBAR approach that is typically incompatible with the design and intent of sql and a relational database. However, feel free to implement both approaches and compare the execution plans - that will be a far more illuminating experience than just reading such things. 
    • Marked as answer by malikasad Thursday, May 30, 2019 3:21 PM
    Thursday, May 30, 2019 2:45 PM