insert and update take time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert and update take time

Author

Topic

saracom
Starting Member

6 Posts

Posted - 2013-03-04 : 23:17:38

dear forum
i have make one procedure to insert and update in to table
when there is about 1500 rows its worked fine but when there is more than 35000 data then it takes too much time i cannot understand why
and any buddy give any ideas
thanks
below is my store procedure

create procedure [dbo].[sp_InsertCashIn_Outforerror]
as
declare @DateValue Datetime
declare @transctionTime datetime
declare @AccountHeading varchar (30)
declare @PreBalance money
declare @CashIn money
declare @CahOut money
declare @BalanceCash money
declare @UserName varchar (30)
declare @TrackId int

truncate table CashIn_Out
Declare @LoopCounter int
Set @LoopCounter = 0
Select @LoopCounter =Count(*) From dbo.transctiondetail_test
While @LoopCounter <> 0
Begin
select top 1 @DateValue =transctiondetail_test.datefield from transctiondetail_test
select top 1 @transctionTime =transctiondetail_test.datefield from transctiondetail_test
select top 1 @AccountHeading =transctiondetail_test.AccountHeading from transctiondetail_test
select top 1 @PreBalance ='0'
select top 1 @CashIn =transctiondetail_test.newamount from transctiondetail_test
select top 1 @CahOut =transctiondetail_test.preamount from transctiondetail_test
select top 1 @BalanceCash ='0'
select top 1 @UserName =transctiondetail_test.customer from transctiondetail_test
select top 1 @TrackId =transctiondetail_test.Transno from transctiondetail_test

Insert into [dbo].[CashIn_Out]
(DateValue,transctionTime,AccountHeading,PreBalance,CashIn,CahOut,BalanceCash,UserName,TrackId)values
(@DateValue,@transctionTime,@AccountHeading,@PreBalance,@CashIn,@CahOut,@BalanceCash,@UserName,@TrackId)

Delete Top (1) from transctiondetail_test
select @LoopCounter = Count(*) From transctiondetail_test
update cashin_out
set cahout=-cashin_out.cashin
where cashin_out.cashin<0
update cashin_out
set cashin='0'
where cashin_out.cashin<0

end

help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 23:52:55

why do you need a cursor here?

why cant you use set based insert using insert select?
like


Insert into [dbo].[CashIn_Out]
(DateValue,transctionTime,AccountHeading,PreBalance,CashIn,CahOut,BalanceCash,UserName,TrackId)
select transctiondetail_test.datefield,transctiondetail_test.datefield,
transctiondetail_test.AccountHeading,'0',transctiondetail_test.newamount,transctiondetail_test.preamount,'0',
transctiondetail_test.customer,transctiondetail_test.Transno 
from transctiondetail_test 

update cashin_out
set cahout=-cashin_out.cashin ,
 cashin='0'
where cashin_out.cashin<0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saracom
Starting Member

6 Posts

Posted - 2013-03-05 : 03:21:23

sir it take too much time from your code. from i code it takes 48 sec for 56000 rows updata but i try from your code it goes more than 2 min
any suggestion sir

help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 03:52:52

thats not true. can you clear the proc cache and try your query?
its doing row by row comparison whreas mine is doing set based operation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saracom
Starting Member

6 Posts

Posted - 2013-03-05 : 04:07:38

sir
can you give me example how can i clear procedure cashes

it would be your great help
thanks
help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 04:09:59

DBCC FREEPROCCACHE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saracom
Starting Member

6 Posts

Posted - 2013-03-06 : 06:19:59

thanks
it works great
javascript:insertsmilie('

')

help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 11:15:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saracom
Starting Member

6 Posts

Posted - 2013-03-07 : 22:59:54

dear sir
below is also the insert procedure which i have make with case
please suggest me that what is the best way to make it fast

create procedure [dbo].[sp_getinformationstu]
as
declare @accounttpe as varchar(100)
declare @datefiled as datetime
declare @accountCode as varchar(100)
declare @accountheading as varchar(100)
declare @debit as decimal(16,2)
declare @credit as decimal(16,2)
declare @GroupHeading as varchar(100)
declare @Trackid as int
Truncate table dbo.assetsdetail
Truncate table dbo.Liabilitesdetail
Truncate table dbo.incomedetail
Truncate table dbo.expendituredetail

Declare @LoopCounter int
Set @LoopCounter = 0
Select @LoopCounter =Count(*) From dbo.transctiondetail_test
While @LoopCounter <> 0
Begin
set @accountCode=''
select top 1 @accountCode=transctiondetail_test.accountcode from transctiondetail_test
set @accounttpe=''
Select top 1 @accounttpe = accountheading.AccountType from dbo.accountheading where accountcode=@accountCode
Select top 1 @datefiled = transctiondetail_test.datefield from transctiondetail_test
set @accountheading=''
select top 1 @accountheading=transctiondetail_test.AccountHeading from transctiondetail_test
set @debit='0'
select top 1 @debit =transctiondetail_test.PreAmount from transctiondetail_test
if @debit is null
set @debit='0'
set @credit='0'
select top 1 @credit =transctiondetail_test.NewAmount from transctiondetail_test
if @credit is null
set @credit='0'
set @GroupHeading='0'
select top 1 @GroupHeading=transctiondetail_test.Remarks from transctiondetail_test
set @Trackid='0'
select top 1 @Trackid=transctiondetail_test.transno from transctiondetail_test
if @Trackid is null
set @Trackid='0'

if @accounttpe='Assets'
insert into assetsdetail
(
DateFiled,
AccountCode,
AccountHeading,
Amount,
GroupHeading,
Trackid
)values
(
@datefiled,
@accountCode,
@accountheading,
@debit,
@GroupHeading,
@Trackid
)
if @accounttpe='Liabilities'
insert into Liabilitesdetail
(
DateFiled,
AccountCode,
AccountHeading,
Amount,
GroupHeading,
Trackid
)values
(
@datefiled,
@accountCode,
@accountheading,
@credit,
@GroupHeading,
@Trackid
)
if @accounttpe='Income'
insert into incomedetail
(
DateFiled,
AccountCode,
AccountHeading,
Amount,
GroupHeading,
Trackid
)values
(
@datefiled,
@accountCode,
@accountheading,
@credit,
@GroupHeading,
@Trackid
)
if @accounttpe='Expenditure'
insert into expendituredetail
(
DateFiled,
AccountCode,
AccountHeading,
Amount,
GroupHeading,
Trackid
)values
(
@datefiled,
@accountCode,
@accountheading,
@debit,
@GroupHeading,
@Trackid
)

Delete Top (1) from transctiondetail_test
select @LoopCounter = Count(*) From transctiondetail_test
End

help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 00:11:44

try converting it to set based just like what i showed you earlier and post if you face any issue

there's no need for loop here
I've already given you pointers for converting logic to set based by earlier solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

saracom
Starting Member

6 Posts

Posted - 2013-03-08 : 01:28:35

ok i will try and let you know the problem
thanks

help the forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 05:34:19

ok..let us know if you face any issues in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources