January 5, 2005 at 8:24 pm
I have a number of these that I have written, on an ERP database. Basically what they do is:
1) Create a table, dropping if it exists
2) Load the table with basic data (employee number)
3) Read the data into a cursor, and then calculate each variable. One example is the rate someone pays for insurance, another is the deduction total for a period of time for a type of deduction.
Step 3 is very slow. Not that it shouldn't be, due to the amount of work involved, but users should not have to wait an hour for a query to run.
I have done a lot of optimization. There are two issues I can see. One is that logging is taking time, and since this database is used just for interfaces, and a complete loss of it would be no big deal at all, I would like to eliminate as much as possible. The other is that I am simply doing a lot of queries to calculate results. This is where most of the time is spent.
An example is SELECT SUM(DED_AMT) FROM TABLE WHERE DED_CODE = '010' (and employee = et cetera...)
Any suggestions?
January 5, 2005 at 9:05 pm
If there is anyway to avoid the use of cursors by using set statements, you will see a big gain in performance. I recently converted the cursor type code to set statements in a proc and reduced the running time from 22 minutes to 4 minutes. Another custom process from our accounting software vendor took 2 to 3 hours to run using cursors (I think some blocking was causing problems). Luckily, the source code was available, so I rewrote the process in a stored proc using set statements. My proc runs in 7 minutes and produces identical output.
Maybe you will need to create some work tables with summary data instead of the individual queries for each employee as in your example. Then update your main table with the work tables. Your example query may be doing a table scan each time through the loop!
At the other extreme, queries that update millions of rows should be broken down into smaller batches. Even though this is done by the use of a loop, it is not a cursor.
Hope this gives you some ideas on how you can improve your process.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 6, 2005 at 6:14 am
Can you post a whole example and let us see if we can offer you a alternate and maybe better solution to the code you created?
January 6, 2005 at 6:42 pm
I do something like this usually to avoid cursors.
DECLARE @TempTable TABLE
(
ID int IDENTITY (1, 1) NOT NULL,
SomeData varchar(20),
SomeMoredata int NULL
)
-- dump all params for sproc into the table variable
insert @TempTable select SomeVarchar, SomeInt from someTables
select @MaxRows = Count(1) from @TempTable
Set @CurRow = 1
-- loop through using the ID field of the temp table as the where clause to fire of the sproc
while @CurRow <= @MaxRows
begin
select @IntParam = SomeInt, @VarCharParam = SomeVarchar from @TempTable where ID = @CurRow
exec SomeStoredProcedure @VarCharParam,@IntParam
set @CurRow = @CurRow + 1
end
The basic idea is put all your pertinent data into a table variable with an identity colum. Then loop through and fire off any logic or sproc using the data from the table variable where the ID = current iteration number. When I do this it seems to increase performance and sometimes drastically.
January 7, 2005 at 7:47 am
mpinnow is correct. Eliminating the use of cursors will impact performance significantly. As such, I've used a variation of a script I found on this site to implement a "cusror-less" loop of a recordset using a WHILE contruct:
declare @cnt int,
@reccnt int
select @cnt= 1, @reccnt = count(*) from
DB_REM_FILELOAD_ARCHIVE
where
FILE_DT = 'PX060105'
while @cnt <= @reccnt
begin
select * from
(
select
(select count(*)
from DB_REM_FILELOAD_ARCHIVE s2
where
s2.LOCATION <= s.LOCATION and
s2.FILE_DT = 'PX060105'
) [Counter],*
from DB_REM_FILELOAD_ARCHIVE s
where
FILE_DT = 'PX060105'
) a
where
@cnt = Counter
select @cnt = @cnt + 1
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply