SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 insert and update take time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

saracom
Starting Member

Nepal
6 Posts

Posted - 03/04/2013 :  23:17:38  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 03/04/2013 :  23:52:55  Show Profile  Reply with Quote
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/

Go to Top of Page

saracom
Starting Member

Nepal
6 Posts

Posted - 03/05/2013 :  03:21:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/05/2013 :  03:52:52  Show Profile  Reply with Quote
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/

Go to Top of Page

saracom
Starting Member

Nepal
6 Posts

Posted - 03/05/2013 :  04:07:38  Show Profile  Reply with Quote
sir
can you give me example how can i clear procedure cashes

it would be your great help
thanks
help the forum

Edited by - saracom on 03/05/2013 04:12:03
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/05/2013 :  04:09:59  Show Profile  Reply with Quote
DBCC FREEPROCCACHE

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

Go to Top of Page

saracom
Starting Member

Nepal
6 Posts

Posted - 03/06/2013 :  06:19:59  Show Profile  Reply with Quote
thanks
it works great
javascript:insertsmilie('')

help the forum
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/06/2013 :  11:15:35  Show Profile  Reply with Quote
welcome

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

Go to Top of Page

saracom
Starting Member

Nepal
6 Posts

Posted - 03/07/2013 :  22:59:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/08/2013 :  00:11:44  Show Profile  Reply with Quote
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/

Go to Top of Page

saracom
Starting Member

Nepal
6 Posts

Posted - 03/08/2013 :  01:28:35  Show Profile  Reply with Quote
ok i will try and let you know the problem
thanks


help the forum
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/08/2013 :  05:34:19  Show Profile  Reply with Quote
ok..let us know if you face any issues in it

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000