Inserting bulk data to SQL DB (SQL 2000) [modified]
-
I am generating bulk invoices using front end and when I try to insert it to the data base it takes hours it’s unacceptable. This is how I am doing the task Get relevant Cx accounts to data tables Generates Invoice header and detail records and store those records on Invoice Header and Detail Data tables Loop the invoice header data table and Insert recodes using Strode procedure. Loop the invoice detail data table and Insert recodes using Strode procedure. To generate for 200000 invoices it takes more than 24 Hrs. How can I improve the performance? Please refer the code. //Inserting the Invoice Header records foreach(DataRow InvHDR in DS_BillData.Tables["InvoiceHeaderInformation"].Rows) { SqlParameter[] para =new SqlParameter[14]; para[0]=new SqlParameter("@GUAccountID",InvHDR["GUAccountID"]); para[1]=new SqlParameter("@GUInvID",InvHDR["GUInvoiceID"]); para[2]=new SqlParameter("@InvoiceNo",InvHDR["InvoiceNo"]); para[3]=new SqlParameter("@InvoiceDate",StartDate); para[4]=new SqlParameter("@DueDate",DueDate); para[5]=new SqlParameter("@BatchNo",BatchID); para[6]=new SqlParameter("@GUStatmentID",InvHDR["GUInvoiceID"]); para[7]=new SqlParameter("@BatchSNCount",BatchSNCount); para[8]=new SqlParameter("@PAYMENTRECIVED",InvHDR["PAYMENTRECIVED"]); para[9]=new SqlParameter("@SubTotal",InvHDR["SubTotal"]); para[10]=new SqlParameter("@InvoiceAmount",InvHDR["InvoiceAmount"]); para[11]=new SqlParameter("@VAT",InvHDR["VAT"]); para[12]=new SqlParameter("@OtherTax",InvHDR["OtherTax"]); para[13]=new SqlParameter("@StatmentNo",InvHDR["InvoiceNo"]); //para[13]=new SqlParameter("@WaveOFF","Y"); callSp("BillingInvoiceCreation_forFront",para); } Stored Procedure--- Procedure BillingInvoiceCreation_forFront ( @GUAccountID as nvarchar(30), @GUInvID as nvarchar(30), @InvoiceNo as numeric, @InvoiceDate as datetime, @DueDate as datetime, @BatchNo as Decimal, @GUStatmentID as nvarchar(30), @StatmentNo as numeric, @BatchSNCount as decimal, @PAYMENTRECIVED as decimal(9,2), @SubTotal as decimal(9,2), @InvoiceAmount as decimal(9,2), @VAT as decimal(9,2), @OtherTax as decimal(9,2) ) as BEGIN Declare @PrintDescription as nvarchar(100) Declare @GUItemID as nvarchar(30) Declare @PaymentAmt as decimal(9,2) Declare @DueAmount as decimal(9,2) Declare @CreditNoteAmount as decimal(9,2) Declare @BFAmount as decimal(9,2) INSERT INTO B_BillingInvoiceHead
-
I am generating bulk invoices using front end and when I try to insert it to the data base it takes hours it’s unacceptable. This is how I am doing the task Get relevant Cx accounts to data tables Generates Invoice header and detail records and store those records on Invoice Header and Detail Data tables Loop the invoice header data table and Insert recodes using Strode procedure. Loop the invoice detail data table and Insert recodes using Strode procedure. To generate for 200000 invoices it takes more than 24 Hrs. How can I improve the performance? Please refer the code. //Inserting the Invoice Header records foreach(DataRow InvHDR in DS_BillData.Tables["InvoiceHeaderInformation"].Rows) { SqlParameter[] para =new SqlParameter[14]; para[0]=new SqlParameter("@GUAccountID",InvHDR["GUAccountID"]); para[1]=new SqlParameter("@GUInvID",InvHDR["GUInvoiceID"]); para[2]=new SqlParameter("@InvoiceNo",InvHDR["InvoiceNo"]); para[3]=new SqlParameter("@InvoiceDate",StartDate); para[4]=new SqlParameter("@DueDate",DueDate); para[5]=new SqlParameter("@BatchNo",BatchID); para[6]=new SqlParameter("@GUStatmentID",InvHDR["GUInvoiceID"]); para[7]=new SqlParameter("@BatchSNCount",BatchSNCount); para[8]=new SqlParameter("@PAYMENTRECIVED",InvHDR["PAYMENTRECIVED"]); para[9]=new SqlParameter("@SubTotal",InvHDR["SubTotal"]); para[10]=new SqlParameter("@InvoiceAmount",InvHDR["InvoiceAmount"]); para[11]=new SqlParameter("@VAT",InvHDR["VAT"]); para[12]=new SqlParameter("@OtherTax",InvHDR["OtherTax"]); para[13]=new SqlParameter("@StatmentNo",InvHDR["InvoiceNo"]); //para[13]=new SqlParameter("@WaveOFF","Y"); callSp("BillingInvoiceCreation_forFront",para); } Stored Procedure--- Procedure BillingInvoiceCreation_forFront ( @GUAccountID as nvarchar(30), @GUInvID as nvarchar(30), @InvoiceNo as numeric, @InvoiceDate as datetime, @DueDate as datetime, @BatchNo as Decimal, @GUStatmentID as nvarchar(30), @StatmentNo as numeric, @BatchSNCount as decimal, @PAYMENTRECIVED as decimal(9,2), @SubTotal as decimal(9,2), @InvoiceAmount as decimal(9,2), @VAT as decimal(9,2), @OtherTax as decimal(9,2) ) as BEGIN Declare @PrintDescription as nvarchar(100) Declare @GUItemID as nvarchar(30) Declare @PaymentAmt as decimal(9,2) Declare @DueAmount as decimal(9,2) Declare @CreditNoteAmount as decimal(9,2) Declare @BFAmount as decimal(9,2) INSERT INTO B_BillingInvoiceHead
-
I am generating bulk invoices using front end and when I try to insert it to the data base it takes hours it’s unacceptable. This is how I am doing the task Get relevant Cx accounts to data tables Generates Invoice header and detail records and store those records on Invoice Header and Detail Data tables Loop the invoice header data table and Insert recodes using Strode procedure. Loop the invoice detail data table and Insert recodes using Strode procedure. To generate for 200000 invoices it takes more than 24 Hrs. How can I improve the performance? Please refer the code. //Inserting the Invoice Header records foreach(DataRow InvHDR in DS_BillData.Tables["InvoiceHeaderInformation"].Rows) { SqlParameter[] para =new SqlParameter[14]; para[0]=new SqlParameter("@GUAccountID",InvHDR["GUAccountID"]); para[1]=new SqlParameter("@GUInvID",InvHDR["GUInvoiceID"]); para[2]=new SqlParameter("@InvoiceNo",InvHDR["InvoiceNo"]); para[3]=new SqlParameter("@InvoiceDate",StartDate); para[4]=new SqlParameter("@DueDate",DueDate); para[5]=new SqlParameter("@BatchNo",BatchID); para[6]=new SqlParameter("@GUStatmentID",InvHDR["GUInvoiceID"]); para[7]=new SqlParameter("@BatchSNCount",BatchSNCount); para[8]=new SqlParameter("@PAYMENTRECIVED",InvHDR["PAYMENTRECIVED"]); para[9]=new SqlParameter("@SubTotal",InvHDR["SubTotal"]); para[10]=new SqlParameter("@InvoiceAmount",InvHDR["InvoiceAmount"]); para[11]=new SqlParameter("@VAT",InvHDR["VAT"]); para[12]=new SqlParameter("@OtherTax",InvHDR["OtherTax"]); para[13]=new SqlParameter("@StatmentNo",InvHDR["InvoiceNo"]); //para[13]=new SqlParameter("@WaveOFF","Y"); callSp("BillingInvoiceCreation_forFront",para); } Stored Procedure--- Procedure BillingInvoiceCreation_forFront ( @GUAccountID as nvarchar(30), @GUInvID as nvarchar(30), @InvoiceNo as numeric, @InvoiceDate as datetime, @DueDate as datetime, @BatchNo as Decimal, @GUStatmentID as nvarchar(30), @StatmentNo as numeric, @BatchSNCount as decimal, @PAYMENTRECIVED as decimal(9,2), @SubTotal as decimal(9,2), @InvoiceAmount as decimal(9,2), @VAT as decimal(9,2), @OtherTax as decimal(9,2) ) as BEGIN Declare @PrintDescription as nvarchar(100) Declare @GUItemID as nvarchar(30) Declare @PaymentAmt as decimal(9,2) Declare @DueAmount as decimal(9,2) Declare @CreditNoteAmount as decimal(9,2) Declare @BFAmount as decimal(9,2) INSERT INTO B_BillingInvoiceHead
Have you considered using something like DTS or SSIS instead. They are set up to bulk load data.