question

please do suggest me some changes to increase the speed of following insert batch query set transaction isolation level serializable begin transaction declare @TransID numeric declare @GiftID numeric declare @ModTransId numeric declare @VchTransId numeric declare @VoucherNo numeric declare @CustomerId numeric declare @OurBankID numeric declare @CCBankID numeric declare @ModVchrTransid numeric declare @CashVchTransID numeric declare @BcTransId numeric declare @MatId numeric declare @ModMatId numeric select @CustomerId = AccountCode from AccountCode where AccountName = 'Cash Sale' select @TransId = ( isnull(max(TransactionID), 0) + 1 ) from SalesVoucher set @VoucherNo = 951 select @VoucherNo = ( isnull(max(VoucherNo), 0) + 1 ) from SalesVoucher where branchnm = 2 and CreditTo = 36 and VoucherDate >= convert(datetime, '01/04/2013', 103) and VoucherDate <= convert(datetime, '31/03/2014', 103) update Label_table set SaleFlag = 0, SaleDate = null, SaleTransID = 0 from Label_table as LT inner join SalesVchMatDesc SM on LT.material_Name = SM.Material_Name and SM.Voucherdate = LT.Saledate and SM.Netweight = LT.NetWt where SM.Labelno = LT.Label and SM.Transactionid = @Transid and SaleFlag = 1 and sm.branchnm = lt.branchnm and sm.branchnm = 2 delete from SalesVoucher where TransactionID = @TransId and branchnm = 2 delete from SalesVchMatDesc where TransactionID = @TransId and branchnm = 2 select @GiftId = GMatId from GMatMAster where GMatName = '.' insert into SalesVoucher ( TransactionID, VoucherNo, VoucherDate, DebitTo, CreditTo, TotalAmt, Discount, ModAmt, ModWt, Other, Othertype, TaxPerc, TaxAmt, NetAmt, Advance, Narration, Haste, BCAmt, UserName, TransDate, TransTime, BalDate, EntryFrom, PurTransID, GiftNAme, disctype, BranchNm, CompNm ) values ( @TransID, @VoucherNo, convert(datetime, '01/05/2013', 103), @CustomerId, 36, 8910, 0, 2000, 0.8, 12, 'Khade', '1.10', 98.14, 9020, 0, '.', '', 0, 1, convert(datetime, '01/05/2013', 103), '6:31:43 PM', convert(datetime, '01/01/1900', 103), 'SALE', 0, @GiftID, 0, 2, 'COMP2' ) select @MatId = Materialid from MaterialMaster where category = 'Gold' and MaterialName = '01.MIX ITEAM' insert into SalesVchMatDesc ( TransactionID, VoucherNo, VoucherDate, SMCode, Labelno, Category, Material_Name, Qty, Carrate, GrossWt, CuttingWt, NetWeight, Rate, LabourType, LabourCharges, Amount, UserName, TransDate, TransTime, EntryFrom, PurTransID, BranchNm, CompNm ) values ( @TransID, @VoucherNO, convert(datetime, '01/05/2013', 103), 1, '', 'Gold', @MatId, 1, '22 crt', 1, 0, 1, 2700, 'Per GM', 270, 2970, 1, convert(datetime, '01/05/2013', 103), '6:31:43 PM', 'SALE', 0, 2, 'COMP2' ) select @MatId = Materialid from MaterialMaster where category = 'Gold' and MaterialName = '02.RANIHAR' insert into SalesVchMatDesc ( TransactionID, VoucherNo, VoucherDate, SMCode, Labelno, Category, Material_Name, Qty, Carrate, GrossWt, CuttingWt, NetWeight, Rate, LabourType, LabourCharges, Amount, UserName, TransDate, TransTime, EntryFrom, PurTransID, BranchNm, CompNm ) values ( @TransID, @VoucherNO, convert(datetime, '01/05/2013', 103), 1, '', 'Gold', @MatId, 1, '23.5 crt', 2, 0, 2, 2700, 'Per GM', 270, 5940, 1, convert(datetime, '01/05/2013', 103), '6:31:43 PM', 'SALE', 0, 2, 'COMP2' ) select @ModTransId = ( isnull(max(TransactionID), 0) + 1 ) from ModPurchase select @VoucherNo = ( isnull(max(VoucherNo), 0) + 1 ) from ModPurchase where branchnm = 2 and category = 'Gold' and VoucherDate >= convert(datetime, '01/04/2013', 103) and VoucherDate <= convert(datetime, '31/03/2014', 103) delete from ModPurchase where salestransid = @TransId and EntryFrom = 'SALE' select @ModMatId = Materialid from MaterialMaster where category = 'Gold' and MaterialName = 'Raw Gold' insert into ModPurchase ( TransactionID, VoucherNo, Voucherdate, DebitTo, CreditTo, SMcode, Category, Material_name, GrossWeight, MeltingPt, NetWeight, Rate, Amount, [Note], EntryFrom, UserName, TransDate, TransTime, SalesTransID, BranchNm, CompNm ) values ( @ModTransId, @VoucherNo, convert(datetime, '01/05/2013', 103), 35, @CustomerId, 1, 'Gold', @ModMatId, 1, 80, 0.8, 2500, 2000, 'Mod Given In Bill No:' + cast(@VoucherNo as nvarchar), 'SALE', 1, convert(datetime, '01/05/2013', 103), '6:31:43 PM', @TransId, 2, 'COMP2' ) select @VchTransId = ( isnull(max(TransactionID), 0) + 1 ) from Voucher select @VoucherNo = ( isnull(max(VoucherNo), 0) + 1 ) from Voucher where branchnm = 2 and VoucherDesc = 'Payment' and VoucherDate >= convert(datetime, '01/04/2013', 103) and VoucherDate <= convert(datetime, '31/03/2014', 103) delete from Voucher where SalesTransID = @TransId and EntryFrom = 'SALE' and VoucherDesc = 'Payment' insert into Voucher ( Srno, Voucherno, voucherDate, Ledgername, Debit, Credit, Accmod, Narration, EntryFrom, ChequeNo, ChequeDate, BankName, CreditCardBank, CommissionPer, AmtDrawnFromCreditCard, Commission, CardApprovalNo, CardOtherNo, SalesTransId, BillRoundOff, VoucherDesc, TransactionID, UserName, TransDate, TransTime, Interest, IntDisc, BranchNm, Category, compnm ) values ( 1, @voucherNO, convert(datetime, '01/05/2013', 103), @CustomerID, 2000, 0, 'Dr', 'Cash Paid Against SALE Bill No : ' + cast(@VoucherNo as nvarchar), 'SALE', 0, convert(datetime, '01/05/2013', 103), '0', 0, 0, 0, 0, 0, 0, @TransId, 0, 'Payment', @VchTransId, 1, convert(datetime, '01/05/2013', 103), '18:31:43', 0, 0, 2, '.', 'COMP2' ) insert into Voucher ( Srno, Voucherno, voucherDate, Ledgername, Debit, Credit, Accmod, Narration, EntryFrom, ChequeNo, ChequeDate, BankName, CreditCardBank, CommissionPer, AmtDrawnFromCreditCard, Commission, CardApprovalNo, CardOtherNo, SalesTransId, BillRoundOff, VoucherDesc, TransactionID, UserName, TransDate, TransTime, Interest, IntDisc, BranchNm, Category, compnm ) values ( 2, @voucherNO, convert(datetime, '01/05/2013', 103), 34, 0, 2000, 'Cr', 'Cash Paid Against SALE Bill No : ' + cast(@VoucherNo as nvarchar), 'SALE', 0, convert(datetime, '01/05/2013', 103), '0', 0, 0, 0, 0, 0, 0, @TransID, 0, 'Payment', @VchTransId, 1, convert(datetime, '01/05/2013', 103), '18:31:43', 0, 0, 2, '.', 'COMP2' ) select @VchTransId = ( isnull(max(TransactionID), 0) + 1 ) from Voucher select @VoucherNo = ( isnull(max(VoucherNo), 0) + 1 ) from Voucher where branchnm = 2 and VoucherDesc = 'Receipt' and VoucherDate >= convert(datetime, '01/04/2013', 103) and VoucherDate <= convert(datetime, '31/03/2014', 103) delete from Voucher where salestransid = @TransId and EntryFrom = 'SALE' and VoucherDesc = 'Receipt' insert into Voucher ( Srno, Voucherno, voucherDate, Ledgername, Debit, Credit, Accmod, Narration, EntryFrom, ChequeNo, ChequeDate, BankName, CreditCardBank, CommissionPer, AmtDrawnFromCreditCard, Commission, CardApprovalNo, CardOtherNo, SalesTransId, BillRoundOff, VoucherDesc, TransactionID, UserName, TransDate, TransTime, Interest, IntDisc, BranchNm, category, compnm ) values ( 1, @voucherNO, convert(datetime, '01/05/2013', 103), @CustomerID, 0, 9020, 'Cr', 'Cash Paid Against Sale Bill No : ' + cast(@VoucherNo as nvarchar), 'SALE', 0, convert(datetime, '01/05/2013', 103), '0', 0, 0, 0, 0, 0, 0, @TransID, 0, 'Receipt', @VchTransId, 1, convert(datetime, '01/05/2013', 103), '6:31:43 PM', 0, 0, 2, '.', 'COMP2' ) insert into Voucher ( Srno, Voucherno, voucherDate, Ledgername, Debit, Credit, Accmod, Narration, EntryFrom, ChequeNo, ChequeDate, BankName, CreditCardBank, CommissionPer, AmtDrawnFromCreditCard, Commission, CardApprovalNo, CardOtherNo, SalesTransId, BillRoundOff, VoucherDesc, TransactionID, UserName, TransDate, TransTime, Interest, IntDisc, BranchNm, category, compnm ) values ( 2, @voucherNO, convert(datetime, '01/05/2013', 103), 34, 9020, 0, 'Dr', 'Cash Paid Against Sale Bill No : ' + cast(@VoucherNo as nvarchar), 'SALE', 0, convert(datetime, '01/05/2013', 103), '0', 0, 0, 0, 0, 0, 0, @TransID, 0, 'Receipt', @VchTransId, 1, convert(datetime, '01/05/2013', 103), '6:31:43PM', 0, 0, 2, '.', 'COMP2' ) select @BcTransId = ( isnull(max(TransID), 0) + 1 ) from BcSales1 delete from BcSales1 where TransId = @BcTransId and BillTransId = @TransId and Entryfrom = 'SALE' commit transaction