WANTED:Tips for handling large FP7 Databases 2

[JoeInOhio]

WANTED: Tips/Tricks on Handling Large FoxPro 7 Databases

I'm looking to put together a list of tips/tricks for managing large FoxPro 7 databases and would greatly appreciate any items you can submit.

For example:
How does re-indexing help?
How do you balance Memory against CPU?
Tips/Tricks to optimize Paging
Tricks to increase performance
Any network settings that might help speed things up?

Any information you can share would be greatly appreciated!
Thanks!
Joe

 

[Mike Gagnon]

JoeInOhio

WANTED: Tips/Tricks on Handling Large FoxPro 7 Databases

I assume you are refering to the tables being large and not the database itself. First question might be what do you consider to be large database?
I was just "flying" through a table of 680,000 records today with no problems.
[ol][li]How does re-indexing help?
No, not unless it got corrupted for some reason.[/li]
[li]How do you balance Memory against CPU?
Are you refering to "load Balancing" on a Citrix server farm?[/li]
[li]Any network settings that might help speed things up?
It depends on what type of Network you are using, NT, Novell, Citrix?


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[flintstone42]

We work with relativing large (Over 1 gig) foxpro
dbf and have found

How does re-indexing help?
PACKING does help, by getting rid of the deleted records,
we have found this is also faster with VFP table then 2.6

How do you balance Memory against CPU?
Memory is alot more imporeant then CPU,
Sometimes Fox will take up too much memory, then when other
applications are open, foxpro will have to start swapping, new feature in VFP8 fixes this.

WE have found NOT to make a SQL statement on the entired DBF, we do a copy (with an index) to a subset into memory and perform ths functions on that,

Tips/Tricks to optimize Paging
Tricks to increase performance
Any network settings that might help speed things up?
WE use a novell network, we find making a file sharable and
transactable to increase performance,
Also increase the cache on the server is always a good idea,
but it would also depend on the type of server

 

[baltman]

I've worked on data sets with over 10+ gigs of data. I was provided the data in 1-2 gig text files, both delimited and fixed width.

Since I did not need all data at one time, and given my particular data access needs, I found it optimal to keep the data stored as the original text files and grab out the data as needed using programs that could read the delimiters or by position in the line depending upon the file format as well as perform simple functions such as summing several fields durring the data grab.

Here's a quick example of the type of program I'm speaking of:
clear
CLOSE ALL
set talk off
cutoff={8/31/2002}

delimchar="^"
totalfieldstoget=8

fieldnum1=1 &&acct key
fieldnum2=8 &&cont date
fieldnum3=33 &&cred lim
fieldnum4=35 &&cycl delq
fieldnum5=55 &&state
fieldnum6=48 &&gross balance
fieldnum7=58 &&period enddate
fieldnum8=81 &&comp principal balance

for numberoffiles= 1 to 5

putcmd=""
for x=totalfieldstoget to 1 step -1
putcmd="Valfieldnum"+alltr(str(x))+"+delimchar+"+putcmd
endfor
putcmd="lcString="+left(putcmd,len(putcmd)-11)

varinfile="cards"+alltr(str(numberoffiles))+".txt"
varoutfile="cards"+alltr(str(numberoffiles))+"_out.txt"


lnhandle= fopen(varinfile)
lnNewFile= fcreate(varoutfile)
do while not Feof(lnHandle)
lcString = "^"+fgets(lnHandle,4000)
lcString = strtran(lcString,"','","^&quot
lcString = chrtran(lcString," '","&quot
lcString = chrtran(lcString,chr(44),"&quot
IF LEN(ALLTRIM(CHRtran(lcString," ","&quot))>0
for x=1 to totalfieldstoget
cmd1=" SUBSTR(lcString,AT(delimchar,lcString,fieldnum"+alltr(str(x))+&quot,2)=delimchar+delimchar=.t."
if &cmd1
cmd2="Valfieldnum"+alltr(str(x))+"=''"
&cmd2
endif
cmd3=" SUBSTR(lcString,AT(delimchar,lcString,fieldnum"+alltr(str(x))+&quot,2)=delimchar+delimchar=.f."
if &cmd3
cmd4="Valfieldnum"+alltr(str(x))+"=SUBSTR(lcString,AT(delimchar,lcString,fieldnum"+alltr(str(x))+&quot+1,(AT(delimchar,lcString,fieldnum"+alltr(str(x))+"+1)-1)-(AT(delimchar,lcString,fieldnum"+alltr(str(x))+&quot))"
&cmd4
endif
cmd5="right(alltr(Valfieldnum"+alltr(str(x))+&quot,1)='-'"
if &cmd5
cmd6="Valfieldnum"+alltr(str(x))+"='-'+(left(Valfieldnum"+alltr(str(x))+",len(Valfieldnum"+alltr(str(x))+&quot-1))"
&cmd6
endif

endfor
&putcmd
= fput(lnNewFile,lcString)
ENDIF
enddo
=fclose(lnHandle)
close all
endfor
?TIME()
create table tempdata (loanid c(30), contdate c(8),creditlim n(9,2),cycdelq n(3),;
state c(2), currbal n(10,2), prdenddate c(8), compprin n(10,2), age n(5))

copy to combo type fox2
use combo

for numberoffiles= 1 to 5
cmd="APPEND FROM cards"+alltr(str(numberoffiles))+"_out.txt DELIMITED WITH CHARACTER ^"
&cmd
endfor
return

repl all age with iif(val(contdate)>0,(val(left(prdenddate,4))*12+val(substr(prdenddate,5,2)))-(val(left(contdate,4))*12+val(substr(contdate,5,2))),0)

calc sum(principal) to a1
calc sum(adminrecv) to b1
calc avg(currbal) to a
calc avg(creditlim) to b
sum currbal to cbal
sum(age*currbal)/cbal to wage

set talk on
\sum principal balance
?a1

\sum finance and admin rec
?b1

\avg gross balance
?a

\avg credit limit
?b

\avg gross balance over avg credit limit
?a/b*100

\wt avg age
?wage

 

[Mike Lewis]

Joe,

You didn't mention the most important thing: Rushmore optimisation. This will make a world of difference to the performance of the table.

A couple of other quick points:

- Re-indexing is usually unnecessary, and won't speed thing up at all. It is usually done only to cure damaged indexes.

- If the table has a lot of deleted records, it should be packed from time to time. This will definitely improve performance, but the packing itself takes time, and it requires exclusive use so cannot be done while other users are active.
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk