I heartily agree with the suggestion:
Instead of Where colname = (.vdate + 1), (.vdate + 2), (.vdate + 3) etc.,I
set the vars outside the loop
I remember once reading that it has to evaluate the (.vdate + 1) for every row
it looks at which is slower than saying "= .vNewDate". I make it a point
always to set these variables before the where clause and never put the
computation directly in it.
Karen
-----Original Message-----
From: Dennis Fleming <[email protected]>
To: RBASE-L Mailing List <[email protected]>
Sent: Tue, Apr 29, 2014 10:32 am
Subject: [RBASE-L] - Re: Declare cursor timing.
Angelo,
Further to all the good suggestions in this thread...
I just reviewed some code which builds a matrix, summing totals fordifferent
dates which was too slow. For 100,000 rows it took 8.5 secondsto process.
Building temp tables/views to limit row selection made little difference.But,
by just doing calculations outside the While loop reduced the timeto 3.5
seconds.
Instead of Where colname = (.vdate + 1), (.vdate + 2), (.vdate + 3) etc.,I set
the vars outside the loop and the code was colname = .vdate1,.vdate2, .vdate3,
etc.
Try and reduce the number of commands in the While loop by building oneor more
powerful Select command with Union, Join, Having, Group By, etc.In some cases,
as mentioned earlier, you can eliminate the entire Declarecursor code.
Dennis
*****
At 03:15 PM 4/27/2014, you wrote:
Karen
Yes the small table of records is updating the much larger table. Iexpect the
more records I process the longer the time it will take butwhy the drastic
increase of seconds per record?
Thanks
Angelo
From: [email protected]
To: [email protected]
Subject: [RBASE-L] - Re: Declare cursor timing.
Date: Sun, 27 Apr 2014 12:41:01 -0500
I generally agree withwhat you wrote; however, there is not enough information
to get an ideaon how the entire process is structured. If the full code for
therelevant portions of the process is posted, we could probably figure
outwhere the problem is.
I have used cursors inthe past to do this type of upgrades but recently I have
replaced thisapproach with a single multi-table UPDATE statement; the code is
mucheasier to maintain.
Javier,
Javier Valencia,PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: [email protected][mailto:[email protected]] On Behalf Of Karen Tellef
Sent: Sunday, April 27, 2014 11:17 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Declare cursor timing.
But no, Dennis I can't see that being the problem. IfI understand his email,
the customer order table is the one he's updatingand it has 40K rows. He is
setting a cursor through a smallertable. If that smaller table has 1 row it's
fast, and getsincrementally slower as he cursors through each row. Now yes,
thecursor itself takes some time to "open" the recordset, but hisexample shows
a marked difference between 1 row in the table and 30rows. But the number of
rows in customer order table itself isn'tchanging. Again, that's my
understanding of the OP
Karen
-----Original Message-----
From: Dennis McGrath<[email protected]>
To: RBASE-L Mailing List<[email protected]>
Sent: Sat, Apr 26, 2014 7:59 pm
Subject: [RBASE-L] - RE: Declare cursor timing.
Is the column receipt indexd?
If not, that is the reason for your problem.
Dennis McGrath
Software Developer
QMI Security Solutions
1661 Glenlake Ave
Itasca IL 60143
630-980-8461
[email protected]
From:[email protected][mailto:[email protected]] On Behalf
[email protected]
Sent: Saturday, April 26, 2014 4:03 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Declare cursor timing.
Dennis
The update statement is
Update ctrectyd set canceled = .startdat where receipt eq.vrecnumb
Thanks
Angelo
From: [email protected]
To: [email protected]
Date: Fri, 25 Apr 2014 10:49:53 -0500
Subject: [RBASE-L] - RE: Declare cursor timing.
Angelo
Show us the update command.
Dennis McGrath
Software Developer
QMI Security Solutions
1661 Glenlake Ave
Itasca IL 60143
630-980-8461
[email protected]
From:[email protected][mailto:[email protected]] On Behalf
[email protected]
Sent: Friday, April 25, 2014 9:34 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Declare cursor timing.
I have an issue on timing. I have two tables,one holds daily transactions and
the second holds customer orders.
The daily transaction table could hold from 10 to 500 records. Thecustomer
order table holds approx. 40,000 records.
I am just reading a daily transaction, looking it up by order number
andupdating the processed date I the customer
Order table.
In testing I found the following results: If I key an update it iscomplete in
just a second.
If the daiily table has 5 records it takes approx. 4 1/2 seconds perrecord to
process.
5 records take 4.5 seconds per record,
10 records take 8 seconds per record,
20 records take 15 seconds per record,
30 records take 24 seconds per record.
Why the increment in processing time, any suggestions?
If I key an update
Dennis Fleming
IISCO
www.TheBestCMMS.com
Phone: 570 351-5290
