| Author |
 Topic  |
|
|
saracom
Starting Member
Nepal
6 Posts |
 Posted - 03/04/2013 : 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
India
48032 Posts |
Posted - 03/04/2013 : 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
Nepal
6 Posts |
Posted - 03/05/2013 : 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
India
48032 Posts |
Posted - 03/05/2013 : 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
Nepal
6 Posts |
Posted - 03/05/2013 : 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 |
Edited by - saracom on 03/05/2013 04:12:03 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48032 Posts |
Posted - 03/05/2013 : 04:09:59
|
DBCC FREEPROCCACHE
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
saracom
Starting Member
Nepal
6 Posts |
Posted - 03/06/2013 : 06:19:59
|
thanks
it works great
javascript:insertsmilie(' ')
help the forum |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48032 Posts |
Posted - 03/06/2013 : 11:15:35
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
saracom
Starting Member
Nepal
6 Posts |
Posted - 03/07/2013 : 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
India
48032 Posts |
Posted - 03/08/2013 : 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
Nepal
6 Posts |
Posted - 03/08/2013 : 01:28:35
|
ok i will try and let you know the problem
thanks
help the forum |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48032 Posts |
Posted - 03/08/2013 : 05:34:19
|
ok..let us know if you face any issues in it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
insert and update take time
