Slow Execution Intermitent

[Admigha]

Hi, I'm reading a table of 600.000 records to update an other table, for the frst time read within the same foxpro session it takes more than 60 seconds, the second time it takes less than 5 seconds. Any help?
here is the procedure:
SET ORDER TO CUSTID IN invoices
SELECT bilstmt
GO top
DO WHILE NOT EOF()
WAIT WINDOW NOWAIT STR(100*RECNO()/RECCOUNT())+'% Calculating Payments in display table'
mcust=custid
SELECT invoices
SEEK mcust
IF FOUND()
mflag=.f.
mtotpmt=0
DO WHILE custid=mcust
mpmt=cc1+cc2+cash
IF date>=mdateF AND date<=mdateT AND invstatus<>'V'
mtotpmt=mtotpmt+mpmt
ENDIF
SKIP 1
ENDDO
SELECT bilstmt
replace tot_pmt WITH mtotpmt + tot_pmt
ENDIF
SELECT bilstmt
SKIP 1
ENDDO

Tony Lama
Admigha SoftHouse

 

[Mike Lewis]

Hi Tony,

It's not unusual for a process to take twelve times as long to run the first time it's called in a session compared to subsequent times. VFP does a lot of internal caching on the local machine, so if the data if fairly static, the second and subsequent executions could be very much faster.

As to why it's taking as long as 60 seconds the first time -- well, that depends on the number of records (including the number of child Bilstment records for each parent Invoice), the speed of the network, and other factors.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[jimoo]

Also, remember that the slowest point is usually the network. It could be a network issue. If possible, try running it on your local pc if you are not doing so already and see how long it takes the first time.

Finally, selecting tables (moving work areas) is an I/O intensive operation. You can clean the code a bit so it does not unnecessary move the work area from the correct area to the correct area.

exmaple:
SELECT bilstmt
replace tot_pmt WITH mtotpmt + tot_pmt
ENDIF
SELECT bilstmt
SKIP


Can be changed to:
SELECT bilstmt
replace tot_pmt WITH mtotpmt + tot_pmt
ELSE
SELECT bilstmt
ENDIF
SKIP





Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[TamarGranor]

Finally, selecting tables (moving work areas) is an I/O intensive operation.

That's just not true. Opening tables is I/O intensive, but once they're open, switching work areas doesn't introduce slowdowns.

Tony, seems to me the process you're performing could be done without any loops. Without digging in too deeply, it appears tome you could do your computations with a query and use SQL UPDATE to do your updates.

Can you explain in plain English what you're trying to do?

Tamar

 

[Admigha]

Hi Tamar,
I have one small table called BILSTMT which have custid, and I want to update the field Total payments TOT_PMT from the huge table called INVOICES, this one has an Index on custid, the update must be done if the invoice date is within a given range.

Thanks for replying.

Tony Lama
Admigha SoftHouse

 

[Mike Lewis]

Tony,

I'm still not clear exactly what your problem is. Is it the the perceived slowness of the operation? Or is it that it takes much longer the first time round compared to subsequent executions?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

I would simply try to avoid 600.000 Wait windows being drawn. While it's all in memory, there are lot's of things involved, even for such a tiny window. Writing Text in Windows means drawing graphics, there's GDI involved, Canvas, Brushes, Fonts,... and you do this 600.000 times. Imagine how long it takes if you would draw 600.000 windows/forms. Just because it's always in the same place it's still very time consuming.

Local lnEachNRecords
lnEachNRecords = MAX(1,INT(RECCOUNT()/1000))
...
DO WHILE !EOF() (replace by SCAN and ENDDO by ENDSCAN!)
...
IF RECNO()%lnEachNRecords=0
   WAIT WINDOW NOWAIT STR(100*RECNO()/RECCOUNT(),5,1)+'% ...'
ENDIF

Otherwise I can just repeat what other's said, the difference in time needed for first and subsequent times is due to caching. Without the first slower run, subsequent runs can't be faster or vice versa: you can't make the first run as fast as subsequent runs other than by loading all the data into the cache, even if you do a Select * from bilstmt Into Cursor curCahce Nofilter beforehand, that will surely take that time difference between the first and subsequent runs or even more time.

Your only chance in enhancing the process is to redesign. What you're doing here is a full table update, while in huge parts of the table, amounts may not have changed and thus the calculated values also don't change. You're doing much for a few updates here. You could gain better performance by updating those calculated amounts each time some child record changes by an update trigger, or at least flag records changed since the last of these runs and then only process changed records.

Bye, Olaf.

 

[Olaf Doschke]

Soem other things:

If you would be SETting a RELATION between bilstmt and invoices, you'd not need to SEEK, instead every SKIP in bilstmt would trigger a SEEK in invoices, so take a look at SET RELATION. That's just a detail, on a larger scale I assume you could use some correlated update, if you have VFP9 (or 8) at hand.

Bye, Olaf.

 

[Admigha]

Hi,
I have tested the program with scan-endscan but it was slower than do-enddo
and I tested with SET RELATION, and also it was slower even in the subsequent runs.
I tried to just read the table without any updates or conditions:
Do while not eof()
skip 1
enddo
It took long time (more than 60 sec.)
but after that, when I ran the update program it was fast less than 5 sec.
knowing that I tried to read some selected records, and when I ran the program it was fast for these records but it was slow for the rest.
In my openion it could be a question of caching OR a matter of some SETS commands.

Tony Lama
Admigha SoftHouse

 

[Olaf Doschke]

Hi Tony,

Why do you think it could be any SETting? Did you change any setting between the first 60 second scan and the subsequent ones? It's a matter of caching.

I'm not saying SCAN is much faster, it's surely just playing a marginal role in the total time, but i's much simpler than to manualle Select the table you scan through and SKIP.

It's nonsense Do..Enddo could be much faster, unless you do it after a SCAN ENDSCAN and thus profit from caching. To compare runtimes and see which code is faster you must restart VFP to empty all caches, at least FLUSH in between tests.

Setting a relation also can't be much slower, also not much faster, but of course you need to do it right to profit of it, eg you need to check NOT EOF(childalias) to see if the relation found something.

Your wait window for each record is taking up much time. It could even be the main time consumption. On my notebook the following loop of 600000 WAIT WINDOWs takes 200 seconds alone:

lnT = Seconds() 
For lnI = 1 to 600000
WAIT WINDOW NOWAIT STR(100*lnI/600000)+'% Calculating Payments in display table'
EndFor lnI 
? Seconds()-lnT

while this modified loop only updating the wait window if there is a 0.1% advance takes only about 1.5 seconds:

lnT = Seconds() 
lnEachNRecords = 600
For lnI = 1 to 600000
If lnI%lnEachNRecords=0
WAIT WINDOW NOWAIT STR(100*lnI/600000,5,1)+'% Calculating Payments in display table'
EndIf 
EndFor lnI 
? Seconds()-lnT

While your computer is faster than my notebook, as it takes only 60 seconds for the loop and even only 5 seconds for further loops, this factor of about 133 from 200 seconds down to 1.5 seconds shows how much time you could waste drawing wait windows for each record. You even didn't other to try that out, at least you didn't report back about that issue.

Bye, Olaf.

 

[dbMark]

If you are calling the data from a server, then you get better speed on subsequent runs due to the computer caching files and also the server also caching files.

I also agree that screen displays can consume an inordinate amount of system resources.

 

[Admigha]

Hi Olaf,
You are right about the Scan vs do while and the WAIT WINDOW NOWAIT, but this does not affect too mutch my procedure because it's displaying the message at the parent level which is a small table.

Also, the big table is local and not on the sever.

Tony Lama
Admigha SoftHouse

 

[Olaf Doschke]

Let me see if I understand your code right. As you adress fields with their name only I'm not sure if I got the structures right or if some names are rather variable names than field names.

Create Cursor blstmt (custid I, tot_pmt Y)

Create Cursor invoices (custid I, mdateF D, mDateT D, invstatus C(1), cc1 Y, cc2 Y, cash Y)

Update blstmt set tot_pmt = tot_pmt + VInvoices.mtotpmt FROM (;
select blstmt.custid, sum(cc1+cc1+cash) as mtotpmt from blstmt left join invoices on invoices.custid = blstmt.custid;
where Date() between mdateF and mdateT and invstatus<>'V' group by 1) As VInvoices WHERE blstmt.custid = VInvoices.custid

I think this will need VFP9 because of the subselect and the alias VInvoices given to it, but you could split it in a SQL-Select and an SQL-Update.

I'm not sure if you meant Date() instead of date, perhaps date is a field and mdateF, mdateT are variables, perhaps all names beginning with m are variables in your code.

Bye, Olaf.

 

[Admigha]

all names beginning with m are variables
field to update in bilstmt is:TOT_PMT
fields to get from invoices are :CC1, CC2, CASH and DATE not "date()" to compare to variables MDATEF and MADTET.

I will try to split in SQL statements, and give you an answer.

Tony Lama
Admigha SoftHouse

 

[jimoo]

Not to get off subject, but since it was pointed out switching tables does not induce slow downs I decided to try a simple test. The table was already open. I added the use...\customer statement as a comment.


The 1st loop took 9 seconds on my pc and the 2nd loop was 17 seconds (using 2 select statements).

I will concede that I used such a large number (one hundred million) as a test that is statistically irrelevant, but it still introduced slow downs and has some truth.


Here is the code - you can try it for yourself:


* USE "C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\customer"


lnstart1 = time()
for lni = 1 to 100000000
select customer
endfor
lnEnd1 = time()
messagebox(lnstart1)
messagebox(lnEnd1)



lnstart1 = time()
for lni = 1 to 100000000
select customer
select customer
endfor
lnEnd1 = time()
messagebox(lnstart1)
messagebox(lnEnd1)






Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Admigha]

I KNOW THAT using select can reduce the speed, but why for one run it takes twelve times more than subsequent runs?

I worked around the problem, and I solved it by indexing the big table, which I did not want to do from the beginning.
The new index is custid+date_sc (date_sc represents the string of DATE) and I set the relation custid+date_sc. than I got the result in 0.3 sec. instead of 60 sec. 200 times faster, even for the first run.

But I still wonder about this issue.


Tony Lama
Admigha SoftHouse