Help Reqd for Optimization
-
Hello All!
I have an application that calculates depreciation for fixed assets. This process of calculation takes about 10-15 minutes on SQL Server on P4 2Ghz with 1GB RAM.
I have been using cursors to a very large extent within stored procedures.
While running the procedures for the first time, it took me approx 2 hours. But after using Indexes on the majorily used tables, I managed to reduce the time to about 20 minutes.
Also I changed the cursor types to STATIC and this further gave me some improvement and I managed to complete the query in about 15-17 mins.
Could anyone please let me know on what further optimzations can be made so as to make the execution much more faster.
Also.. after running the procedures for quite sometime my LOG file has become too large. Is there any easy was to restrict the database size in such a manner that it does not give error in execution. Currently I have restricted the LOG size to 500 MB with 5% growth rate.
Thankx in advance for all your suggestions. Please let me know if you require anymore inputs from me.
Thankx Once Again!
Paras Shah
Evision Technologies
Mumbai, India
Edited by - paras_98 on 07/23/2003 11:13:31 AM
Paras Shah
Evision Technologies
Mumbai, India -
Sorry forgot to mention... I am using SQL Server 2000
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
I hat eto say it, but the causes of poor performance are probably due to your use of cursors. If you post an example of common usage, perhaps we can suggest ways to replace the cursors with set-based solutions. Also, if you are running cursors within a transaction, your transaction log will balloon most likely. You can look in MSDN or Books Online for strategies for reducing tran log size.
-
I am not at all using any kinda of Transactions.
Cursors are important for me as I need to fetch rows, calculate the output and update them.
It would not be possible to send you the codes at this hour as the codes are at my office server.
quote:
I hat eto say it, but the causes of poor performance are probably due to your use of cursors. If you post an example of common usage, perhaps we can suggest ways to replace the cursors with set-based solutions. Also, if you are running cursors within a transaction, your transaction log will balloon most likely. You can look in MSDN or Books Online for strategies for reducing tran log size.Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
Agrees with jpipes, get rid of the cursors. Cursors only as a last resort.
-
try temp tables with indexes or new data type table
cursors are expensive
-
I know it is a hard decision to take to use cursors. But there had been no other solution for me. I have to take data from one row and check for some 10 odd conditions and then update a values in that row.
What other areas can we explore to optimize the database?
Paras Shah
Evision Technologies
Mumbai, India
Edited by - paras_98 on 07/24/2003 12:00:18 PM
Paras Shah
Evision Technologies
Mumbai, India -
It sounds as if you've done about all you can do to optimize SQL2K to work with T-SQL cursors (server-side cursors). Client side cursors will be a whole lot more efficient, will enable you to do these complex updates, but will require re-tooling the SQL procs. I still think you can get rid of the SQL cursors using a CASE expression in your updates. Any chance you can post some code that shows a common use of cursors?
-
CREATE PROCEDURE sp_calculate_depreciation_slm
(
@session_id varchar(100),
@asset_code varchar(50)
)
as
/*
-----------------------------------------------------------------------------
Author : Paras Shah
Date : 02-July-2003
----------------------------------------------------------------------------
*/
declare @rowid numeric(24,6),
@no_of_days numeric(24,6),
@max_days_in_month numeric(24,6),
@asset_accumulated numeric(24,6),
@depreciation_rate numeric(24,6)
declare @depreciation_accumulated_temp numeric(24,6),
@first_row_count numeric(24,6)
declare @amount numeric(24,6),
@depreciation_accumulated numeric(24,6),
@asset_wdv_as_on_date numeric(24,6),
@writeoff_amt numeric(24,6)
set@first_row_count=0
declare curTransaction CURSOR STATIC for
select rowid,no_of_days, max_days_in_month, isnull(asset_accumulated,0) as asset_accumulated ,
isnull(depreciation_rate,0) as depreciation_rate ,depreciation_accumulated
--, isnull(asset_wdv_as_on_date,0) as asset_wdv_as_on_date
from depreciation_temp_calculation
where asset_code=@asset_code
and session_id=@session_id
order by trans_start_date
open curTransaction
while (1=1)
begin
fetch next from curTransaction into @rowid, @no_of_days, @max_days_in_month, @asset_accumulated,
@depreciation_rate, @depreciation_accumulated_temp --, @asset_wdv_as_on_date
if @@fetch_status<>0 break
--if @first_row_count=0 set @depreciation_accumulated=@depreciation_accumulated_temp
--set @first_row_count=1
if @depreciation_accumulated < @asset_accumulated or @depreciation_accumulated is null
begin
set @amount = (@depreciation_rate / 100 / 12) * (@no_of_days / @max_days_in_month) * @asset_accumulated
end
else
begin
set @amount = 0
end
if @asset_wdv_as_on_date < @amount and @asset_wdv_as_on_date < 0
begin
set @amount =isnull(@asset_wdv_as_on_date,0)
end
set @depreciation_accumulated = isnull(@depreciation_accumulated, 0) + isnull(@amount,0)
begin
update depreciation_temp_calculation
set amount = @amount,
depreciation_accumulated = isnull(depreciation_accumulated,0) + isnull( @depreciation_accumulated ,0),
asset_wdv_as_on_date = isnull(asset_wdv_as_on_date,0) + @asset_accumulated - ( isnull(depreciation_accumulated,0) + isnull( @depreciation_accumulated ,0))
where rowid = @rowid
if exists( select asset_wdv_as_on_date from depreciation_temp_calculation where asset_wdv_as_on_date < 0 and rowid = @rowid )
begin
update depreciation_temp_calculation
set amount = amount + asset_wdv_as_on_date,
depreciation_accumulated = depreciation_accumulated + asset_wdv_as_on_date,
asset_wdv_as_on_date= asset_wdv_as_on_date - asset_wdv_as_on_date
where rowid = @rowid
end
select@depreciation_accumulated=depreciation_accumulated,
@asset_wdv_as_on_date = asset_wdv_as_on_date ,
@writeoff_amt =writeoff_amt
from depreciation_temp_calculation
where rowid = @rowid
if @asset_wdv_as_on_date <= @writeoff_amt AND @asset_wdv_as_on_date>0 and @amount - @asset_wdv_as_on_date >0
begin
-- INSERT IS DONE IF THE COMPANY WANTS SEPARATE ENTRY FOR WRITE OFF AMOUNT
insert into depreciation_temp_calculation(
session_id, asset_subtype_id, asset_code, trans_start_date, trans_end_date, trans_flag, account, transaction_type,
trans_id, company_id, division_id, location_id, department_id,
amount, depreciation_accumulated, asset_accumulated, asset_wdv_as_on_date, depreciation_rate, sale_purchase_date, sale_installation_date, entry_by_query_no, shift_type, year_id, start_date, end_date, temp_trans_date, no_of_days, max_days_in_month, depreciation_type, writeoff_amt, entry_desc)
selectsession_id, asset_subtype_id, asset_code, trans_start_date, trans_end_date, trans_flag, account, transaction_type,
trans_id, company_id, division_id, location_id, department_id,
@asset_wdv_as_on_date,@asset_wdv_as_on_date+@depreciation_accumulated, asset_accumulated, 0, depreciation_rate, sale_purchase_date, sale_installation_date, 25, shift_type, year_id, start_date, end_date, temp_trans_date, no_of_days, max_days_in_month, depreciation_type, writeoff_amt, 'WRITEOFF'
from depreciation_temp_calculation
where rowid = @rowid
select @rowid = @@IDENTITY
-- UPDATE IS DONE IF THE COMPANY DOES NOT WANT SEPARATE ENTRY FOR WRITEOFF
/*update depreciation_temp_calculation set amount = amount +( @asset_wdv_as_on_date) ,
depreciation_accumulated = depreciation_accumulated+( @asset_wdv_as_on_date) ,
asset_wdv_as_on_date = asset_wdv_as_on_date - ( @asset_wdv_as_on_date)
where rowid=@rowid
*/
end
select@depreciation_accumulated=depreciation_accumulated,
@asset_wdv_as_on_date = asset_wdv_as_on_date ,
@writeoff_amt =writeoff_amt,
@asset_wdv_as_on_date = asset_wdv_as_on_date
from depreciation_temp_calculation
where rowid = @rowid
end
end
close curTransaction
deallocate curTransaction
Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
paras_98:
first, don't prefix your procedures with "sp_". SQL Server looks in system tables first when it is asked to execute a proc starting with sp_; this will only slow things down. Here's your procedure without cursors (or even any variables.):
CREATE PROCEDURE usp_calculate_depreciation_slm
(
@session_id varchar(100),
@asset_code varchar(50)
)
as
--
UPDATE depreciation_temp_calculation
SET
amount = CASE
WHEN (ISNULL(asset_wdv_as_on_date, 0) < ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated) THEN
ISNULL(asset_wdv_as_on_date, 0)
WHEN (depreciation_accumulated < asset_accumulated) OR @depreciation_accumulated IS NULL
THEN ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated)
ELSE
0
END
, depreciation_accumulated = ISNULL(depreciation_accumulated,0) + ISNULL(
(CASE
WHEN (ISNULL(asset_wdv_as_on_date, 0) < ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated) THEN
ISNULL(asset_wdv_as_on_date, 0)
WHEN (depreciation_accumulated < asset_accumulated) OR @depreciation_accumulated IS NULL
THEN ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated)
ELSE
0
END), 0)
, asset_wdv_as_on_date = ISNULL(asset_wdv_as_on_date, 0) + asset_accumulated - ( ISNULL(depreciation_accumulated, 0) + ISNULL(
(CASE
WHEN (ISNULL(asset_wdv_as_on_date, 0) < ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated) THEN
ISNULL(asset_wdv_as_on_date, 0)
WHEN (depreciation_accumulated < asset_accumulated) OR @depreciation_accumulated IS NULL
THEN ((depreciation_rate / 100 / 12) * (no_of_days / max_days_in_month) * asset_accumulated)
ELSE
0
END), 0))
WHERE asset_code= @asset_code
AND session_id= @session_id
--
UPDATE depreciation_temp_calculation
SET
amount = amount + asset_wdv_as_on_date
, depreciation_accumulated = depreciation_accumulated + asset_wdv_as_on_date
, asset_wdv_as_on_date = asset_wdv_as_on_date - asset_wdv_as_on_date
WHERE asset_code= @asset_code
AND session_id= @session_id
AND asset_wdv_as_on_date < 0
--
INSERT INTO depreciation_temp_calculation
(
session_id
, asset_subtype_id
, asset_code
, trans_start_date
, trans_end_date
, trans_flag
, account
, transaction_type
, trans_id
, company_id
, division_id
, location_id
, department_id
, amount
, depreciation_accumulated
, asset_accumulated
, asset_wdv_as_on_date
, depreciation_rate
, sale_purchase_date
, sale_installation_date
, entry_by_query_no
, shift_type
, year_id
, start_date
, end_date
, temp_trans_date
, no_of_days
, max_days_in_month
, depreciation_type
, writeoff_amt
, entry_desc
)
SELECT
session_id
, asset_subtype_id
, asset_code
, trans_start_date
, trans_end_date
, trans_flag
, account
, transaction_type
, trans_id
, company_id
, division_id
, location_id
, department_id
, asset_wdv_as_on_date
, asset_wdv_as_on_date+depreciation_accumulated
, asset_accumulated
, 0
, depreciation_rate
, sale_purchase_date
, sale_installation_date
, 25
, shift_type
, year_id
, start_date
, end_date
, temp_trans_date
, no_of_days
, max_days_in_month
, depreciation_type
, writeoff_amt
, 'WRITEOFF'
FROM depreciation_temp_calculation
WHERE asset_code= @asset_code
AND session_id= @session_id
AND asset_wdv_as_on_date <= writeoff_amt
AND asset_wdv_as_on_date> 0
and amount < asset_wdv_as_on_date -
And before anyone points it out, I'd like to state that I deliberately LEFT OUT transactions from the procedure I posted to give some weight to the fact that the original procedure would have left records orphaned if it had failed within the cursor, since there was no transaction started before the cursor began. Cheers.
-
jpipes 10 out of 10 for effort.
For a start you got rid of the cursor and paras_98 can continue with the reworked code.
-
Hey Thankx A TON!
I shall test this code out tomorrow morning ie abt 10 hrs from now! and get back to you.
I shall try and get the other part of the code rectified on the similar lines!
Thankx!
quote:
And before anyone points it out, I'd like to state that I deliberately LEFT OUT transactions from the procedure I posted to give some weight to the fact that the original procedure would have left records orphaned if it had failed within the cursor, since there was no transaction started before the cursor began. Cheers.Paras Shah
Evision Technologies
Mumbai, India
Paras Shah
Evision Technologies
Mumbai, India -
No problem. I guess I have a bit of a prejudice against cursors in stored procedures...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply