When inserting above 300k records its get timeout error?

Question

I get timeout error from executing store procedure (insert query), its only get inserting the large records. I have to use SQL Server 2008.

This is my code,

for (int k = 0; k <= MaxItemCode; k += 100)
{
Application.DoEvents();
string[] ParamValues = { Convert.ToString(Microsoft.VisualBasic.Conversion.Val(k)) ,
    Convert.ToString(Microsoft.VisualBasic.Conversion.Val(k + 100))};
    SqlClientUtility.ExecuteNonQuery("SPInsertItemMasterEntriesMMRP", destConn, ParamValues);
ScreenRefresh();
 }

I get this error from this line:

SqlClientUtility.ExecuteNonQuery("SPInsertItemMasterEntriesMMRP", destConn, ParamValues);

This is SP

set dateformat dmy
BEGIN TRANSACTION
BEGIN---------------------------------------------------------------------------------------------------------------------



DECLARE ItemMasterCursor CURSOR FOR
    SELECT Item_Code, Item_Name, Brand_Code, Cat_Code, Manufact_Code, Supplier_Code, Subject_Code, Publisher_Code, 
    Author_Code, Co_Aut1, Co_Aut2, Currency_Code_P, Currency_Code_S, Blurb, Section_Code, Department_Code, 
    Product_Code, Ref_Code, Description, Item_UOM, Repacking, Weight, Pieces, Item_ISBN, Item_Reorder_Level, 
    Item_Reorder_Qty, PhotoPath, Item_OpBal, Item_COpBal, Item_Bal, Item_CBal, Pur_Date, PurDate_OpBal, 
    PurDate_COpBal, PurDate_Bal, PurDate_CBal, Item_MRP, Pur_Disc, Pur_Rate, Pur_Tax, Landing_Cost, 
    Profit, VAT, CST, Sale_Rate, Sale_Disc, Item_OEM, Item_SNo, Color_Code, Item_Size_Code, Batch_No, 
    Expiry_Date, R1, R2, R3, R4, R5, Item_Status, Branch_Code, Product_Rate, Type_Code, Main_Item, PrintName,Ing_Arabic,Ing_English
    FROM [Item Master Temp] where Item_code>=@MinItemCode and Item_code<=@MaxItemCode
OPEN ItemMasterCursor
FETCH NEXT FROM ItemMasterCursor INTO 
    @Item_Code, @Item_Name, @Brand_Code, @Cat_Code, @Manufact_Code, @Supplier_Code, @Subject_Code, @Publisher_Code, 
    @Author_Code, @Co_Aut1, @Co_Aut2, @Currency_Code_P, @Currency_Code_S, @Blurb, @Section_Code, @Department_Code, 
    @Product_Code, @Ref_Code, @Description, @Item_UOM, @Repacking, @Weight, @Pieces, @Item_ISBN, @Item_Reorder_Level, 
    @Item_Reorder_Qty, @PhotoPath, @Item_OpBal, @Item_COpBal, @Item_Bal, @Item_CBal, @Pur_Date, @PurDate_OpBal, 
    @PurDate_COpBal, @PurDate_Bal, @PurDate_CBal, @Item_MRP, @Pur_Disc, @Pur_Rate, @Pur_Tax, @Landing_Cost, 
    @Profit, @VAT, @CST, @Sale_Rate, @Sale_Disc, @Item_OEM, @Item_SNo, @Color_Code, @Item_Size_Code, @Batch_No, 
    @Expiry_Date, @R1, @R2, @R3, @R4, @R5, @Item_Status, @Branch_Code, @Product_Rate, @Type_Code, @Main_Item, @PrintName,@IngArabic,@IngEnglish

WHILE @@FETCH_STATUS = 0
BEGIN


    DECLARE @MultiMRP BIT
    SET @MultiMRP = (Select ISNULL(Multiple_Mrp,0) from [Company])

    IF @MultiMRP = 0
    BEGIN
        IF NOT EXISTS( SELECT * FROM [Item Master] WHERE Branch_Code = @Branch_Code
                           and Item_Code = @Item_Code and Type_code= @Type_Code)
        BEGIN  
            INSERT INTO [ITEM MASTER] 
                (Item_Code, Item_Name, Brand_Code, Cat_Code, Manufact_Code, Supplier_Code, Subject_Code, Publisher_Code, 
                Author_Code, Co_Aut1, Co_Aut2, Currency_Code_P, Currency_Code_S, Blurb, Section_Code, Department_Code, 
                Product_Code, Ref_Code, Description, Item_UOM, Repacking, Weight, Pieces, Item_ISBN, Item_Reorder_Level, 
                Item_Reorder_Qty, PhotoPath, Item_OpBal, Item_COpBal, Item_Bal, Item_CBal, Pur_Date, PurDate_OpBal, 
                PurDate_COpBal, PurDate_Bal, PurDate_CBal, Item_MRP, Pur_Disc, Pur_Rate, Pur_Tax, Landing_Cost, 
                Profit, VAT, CST, Sale_Rate, Sale_Disc, Item_OEM, Item_SNo, Color_Code, Item_Size_Code, Batch_No, 
                Expiry_Date, R1, R2, R3, R4, R5, Item_Status, Branch_Code, Product_Rate, Type_Code, Main_Item, PrintName, Transfer_flag, Ascend_flag,Ing_Arabic,Ing_English)
            VALUES
                (@Item_Code, @Item_Name, @Brand_Code, @Cat_Code, @Manufact_Code, @Supplier_Code, @Subject_Code, @Publisher_Code, 
                @Author_Code, @Co_Aut1, @Co_Aut2, @Currency_Code_P, @Currency_Code_S, @Blurb, @Section_Code, @Department_Code, 
                @Product_Code, @Ref_Code, @Description, @Item_UOM, @Repacking, @Weight, @Pieces, @Item_ISBN, @Item_Reorder_Level, 
                @Item_Reorder_Qty, @PhotoPath, @Item_OpBal, @Item_COpBal, @Item_Bal, @Item_CBal, @Pur_Date, @PurDate_OpBal, 
                @PurDate_COpBal, @PurDate_Bal, @PurDate_CBal, @Item_MRP, @Pur_Disc, @Pur_Rate, @Pur_Tax, @Landing_Cost, 
                @Profit, @VAT, @CST, @Sale_Rate, @Sale_Disc, @Item_OEM, @Item_SNo, @Color_Code, @Item_Size_Code, @Batch_No, 
                @Expiry_Date, @R1, @R2, @R3, @R4, @R5, @Item_Status, @Branch_Code, @Product_Rate, @Type_Code, @Main_Item, @PrintName,0,0,@IngArabic,@IngEnglish)
        END
    END
    ELSE
    BEGIN
        IF NOT EXISTS( SELECT * FROM [Item Master] WHERE Branch_Code = @Branch_Code
                           and Item_Code = @Item_Code
                           and Item_MRP = @Item_MRP and Type_code= @Type_Code)
        BEGIN  
            INSERT INTO [ITEM MASTER] 
                (Item_Code, Item_Name, Brand_Code, Cat_Code, Manufact_Code, Supplier_Code, Subject_Code, Publisher_Code, 
                Author_Code, Co_Aut1, Co_Aut2, Currency_Code_P, Currency_Code_S, Blurb, Section_Code, Department_Code, 
                Product_Code, Ref_Code, Description, Item_UOM, Repacking, Weight, Pieces, Item_ISBN, Item_Reorder_Level, 
                Item_Reorder_Qty, PhotoPath, Item_OpBal, Item_COpBal, Item_Bal, Item_CBal, Pur_Date, PurDate_OpBal, 
                PurDate_COpBal, PurDate_Bal, PurDate_CBal, Item_MRP, Pur_Disc, Pur_Rate, Pur_Tax, Landing_Cost, 
                Profit, VAT, CST, Sale_Rate, Sale_Disc, Item_OEM, Item_SNo, Color_Code, Item_Size_Code, Batch_No, 
                Expiry_Date, R1, R2, R3, R4, R5, Item_Status, Branch_Code, Product_Rate, Type_Code, Main_Item, PrintName, Transfer_flag, Ascend_flag,Ing_Arabic, Ing_English)
            VALUES
                (@Item_Code, @Item_Name, @Brand_Code, @Cat_Code, @Manufact_Code, @Supplier_Code, @Subject_Code, @Publisher_Code, 
                @Author_Code, @Co_Aut1, @Co_Aut2, @Currency_Code_P, @Currency_Code_S, @Blurb, @Section_Code, @Department_Code, 
                @Product_Code, @Ref_Code, @Description, @Item_UOM, @Repacking, @Weight, @Pieces, @Item_ISBN, @Item_Reorder_Level, 
                @Item_Reorder_Qty, @PhotoPath, @Item_OpBal, @Item_COpBal, @Item_Bal, @Item_CBal, @Pur_Date, @PurDate_OpBal, 
                @PurDate_COpBal, @PurDate_Bal, @PurDate_CBal, @Item_MRP, @Pur_Disc, @Pur_Rate, @Pur_Tax, @Landing_Cost, 
                @Profit, @VAT, @CST, @Sale_Rate, @Sale_Disc, @Item_OEM, @Item_SNo, @Color_Code, @Item_Size_Code, @Batch_No, 
                @Expiry_Date, @R1, @R2, @R3, @R4, @R5, @Item_Status, @Branch_Code, @Product_Rate, @Type_Code, @Main_Item, @PrintName,0,0, @IngArabic,@IngEnglish)
        END
    END
    FETCH NEXT FROM ItemMasterCursor INTO 
        @Item_Code, @Item_Name, @Brand_Code, @Cat_Code, @Manufact_Code, @Supplier_Code, @Subject_Code, @Publisher_Code, 
        @Author_Code, @Co_Aut1, @Co_Aut2, @Currency_Code_P, @Currency_Code_S, @Blurb, @Section_Code, @Department_Code, 
        @Product_Code, @Ref_Code, @Description, @Item_UOM, @Repacking, @Weight, @Pieces, @Item_ISBN, @Item_Reorder_Level, 
        @Item_Reorder_Qty, @PhotoPath, @Item_OpBal, @Item_COpBal, @Item_Bal, @Item_CBal, @Pur_Date, @PurDate_OpBal, 
        @PurDate_COpBal, @PurDate_Bal, @PurDate_CBal, @Item_MRP, @Pur_Disc, @Pur_Rate, @Pur_Tax, @Landing_Cost, 
        @Profit, @VAT, @CST, @Sale_Rate, @Sale_Disc, @Item_OEM, @Item_SNo, @Color_Code, @Item_Size_Code, @Batch_No, 
        @Expiry_Date, @R1, @R2, @R3, @R4, @R5, @Item_Status, @Branch_Code, @Product_Rate, @Type_Code, @Main_Item, @PrintName, @IngArabic, @IngEnglish

END
CLOSE ItemMasterCursor
DEALLOCATE ItemMasterCursor
END---------------------------------------------------------------------------------------------------------------------

COMMIT TRANSACTION

Answer 1

Increasing the timeout will not completely solve the problem. What you want is to solve the root cause which is the SP's poor performance.

The slowness of the SP is due to the fact that it is doing a row by row processing as evidenced by the use of CURSOR. You need to rewrite your SP in a set-based fashion. Here is one way:

DECLARE @MultiMRP BIT
SELECT @MultiMRP =ISNULL(Multiple_Mrp, 0) FROM [Company]

IF @MultiMRP = 0 BEGIN
    INSERT INTO [ITEM MASTER] (<column list>) 
    SELECT
        <value list>
    FROM [Item Master Temp] imt
    WHERE
        Item_code >= @MinItemCode
        AND Item_code<=@MaxItemCode
        AND NOT EXISTS(
            SELECT 1 
            FROM [Item Master] 
            WHERE 
                Branch_Code = imt.Branch_Code
                AND Item_Code = imt.Item_Code
                AND Type_code = imt.Type_Code
        )       
END
ELSE BEGIN
    INSERT INTO [ITEM MASTER] (<column list>) 
    SELECT
        <value list>
    FROM [Item Master Temp] imt
    WHERE
        Item_code >= @MinItemCode
        AND Item_code<=@MaxItemCode
        AND NOT EXISTS(
            SELECT 1 
            FROM [Item Master] 
            WHERE 
                Branch_Code = imt.Branch_Code
                AND Item_Code = imt.Item_Code
                AND Type_code = imt.Type_Code
                AND Item_MRP = imt.Item_MRP
        )
END

Answer 2

You inclusion of a cursor is the real culprit.

You don't require a cursor and your IF is independent of any value of the cursor

SET @MultiMRP = (Select ISNULL(Multiple_Mrp,0) from [Company])

Just do a Insert INTO SELECT after fetching the @MultiMRP.

Something like this..

Note: the performance is still subjective to the indexes defined on both the tables.

INSERT INTO [ITEM MASTER] 
                (Item_Code, Item_Name, Brand_Code, Cat_Code, Manufact_Code, Supplier_Code, Subject_Code, Publisher_Code, 
                Author_Code, Co_Aut1, Co_Aut2, Currency_Code_P, Currency_Code_S, Blurb, Section_Code, Department_Code, 
                Product_Code, Ref_Code, Description, Item_UOM, Repacking, Weight, Pieces, Item_ISBN, Item_Reorder_Level, 
                Item_Reorder_Qty, PhotoPath, Item_OpBal, Item_COpBal, Item_Bal, Item_CBal, Pur_Date, PurDate_OpBal, 
                PurDate_COpBal, PurDate_Bal, PurDate_CBal, Item_MRP, Pur_Disc, Pur_Rate, Pur_Tax, Landing_Cost, 
                Profit, VAT, CST, Sale_Rate, Sale_Disc, Item_OEM, Item_SNo, Color_Code, Item_Size_Code, Batch_No, 
                Expiry_Date, R1, R2, R3, R4, R5, Item_Status, Branch_Code, Product_Rate, Type_Code, Main_Item, PrintName, Transfer_flag, Ascend_flag,Ing_Arabic, Ing_English)

SELECT Item_Code, Item_Name, Brand_Code, Cat_Code, Manufact_Code, Supplier_Code, Subject_Code, Publisher_Code, 
    Author_Code, Co_Aut1, Co_Aut2, Currency_Code_P, Currency_Code_S, Blurb, Section_Code, Department_Code, 
    Product_Code, Ref_Code, Description, Item_UOM, Repacking, Weight, Pieces, Item_ISBN, Item_Reorder_Level, 
    Item_Reorder_Qty, PhotoPath, Item_OpBal, Item_COpBal, Item_Bal, Item_CBal, Pur_Date, PurDate_OpBal, 
    PurDate_COpBal, PurDate_Bal, PurDate_CBal, Item_MRP, Pur_Disc, Pur_Rate, Pur_Tax, Landing_Cost, 
    Profit, VAT, CST, Sale_Rate, Sale_Disc, Item_OEM, Item_SNo, Color_Code, Item_Size_Code, Batch_No, 
    Expiry_Date, R1, R2, R3, R4, R5, Item_Status, Branch_Code, Product_Rate, Type_Code, Main_Item, PrintName,Ing_Arabic,Ing_English
    FROM [Item Master Temp] T    
    WHERE Item_code>=@MinItemCode and Item_code<=@MaxItemCode
    AND NOT EXISTS ( 
                     SELECT 1 
                     FROM [ITEM MASTER] IM 
                     WHERE IM.Branch_Code = T.Branch_Code
                           AND IM.Item_Code = T.Item_Code 
                           AND IM.Type_code= T.Type_Code 
                           AND ( @MultiMRP = 0 OR IM.Item_MRP = T.Item_MRP))