I tend to agree with Dan on his assessment, mostly based on my own
observations and a gut feel. I have never felt the need to formally measure
the actual throughput of data of my VFP tables on a WAN. I can tell you I
have two production applications used at locations with anywhere from 20 to
over 200 PCs on a LAN, and most of the PCs are using either or both of my
core applications. I do some parameterized views here and there, for small
data squirts, but the majority of my data source comes from native VFP
tables (apps compiled in v-7.0). In many cases I open multiple instances of
the same table, having about 35 - 40 cursors at a time, nearly all VFP
tables. The tables range in record count from 100,000+ customer records,
and 1,000,000+ Repair Order Detail records. My table byte sizes can get
upwards of 1Gb with heavily used Memo fields (read only, they do not change
values in that one app), but more typically they run at between 100Mg and
850Mg in size When my apps initially light up I get about 4 - 7 seconds of
data load delay on a lightly used network, and no more than an 8 - 10 second
delay on a busy network (100BaseTx, for 100vg LANs the load time is
unaffected by traffic load). After that initial load the apps flat out
scream, including any record edit/save processes. I normally use record
level pessimistic buffering with any End User edits, rarely use table
buffering, and I do not create indexes on the fly. I use .cdx indexes.
My .cdx files contain some pretty complex tags (calculated and appended
values in some cases, not just straight field level tags), and I am certain
the .cdx file sizes do cause some bit of initial load delay. But, overall,
the load times are quick. and nobody is griping about performance.
In fact, as an aside, one vendor recently introduced an application using
Firebird SQL, and their load time and overall performance is horrible. From
what the End Users tell me it makes their software unusable. I spoke with
one of their developers, and he told me they are caching all the Firebird
records locally (!) to "help enhance record update and reporting
performance". I had first surmised they may have been running into
performance issues at other sites when trying to run Firebird over a busy
LAN, with Collision Detect-Resend issues. But the developer insisted they
had update performance problems with even small, lightly loaded LANs. The
LAN at my one client location, which has such horrible load times, has NO
Collision Detect-Resend issues as they are running 100vg Ethernet, a totally
collision free network system with a minimum net effective throughput of
96.0mps in even the most heavily loaded LAN with a maximum of 1,024 devices.
I determined that indeed their performance was due to them pulling over all
records upon initial load.
The funny thing is that even with all the Firebird records cached locally,
performance with their app is still really sluggish. Funnier yet was when
they tried to blame the PCs and LAN. I went on-site and showed them how
fast my VFP apps load and run, on the same PCs, over the same LAN, and
pulling data from a much older and slower P-III 700Mhz, 1.2Gb SDRAM HP
Server that is more heavily used than their dedicated P4, 3.2Ghz 4Gb DDR RAM
Server. My tables are significantly larger in both record count and overall
size than any of their Firebird tables. So, not having to say so to these
folks, I attributed the overall performance of the Firebird SQL database to
be due to funky coding. I have never heard anyone say Firebird is slow, in
fact I have heard it is really pretty quick.
Now, a side note, there may be one more thing you can try to boost your ODBC
Remote View performance. While working on a Pervasive database project
using a Remote View via ODBC, I found pulling over all the Pervasive records
was extremely slow. On a whim (luck) while at the Command Window prompt in
VFP I entered GO BOTTOM. WHAM! Those pervasive records FLEW across! I was
quite amazed at that, and tested the results time and again. It was really
nice to have found that little trick as the Pervasive table had about 6
million large records to pull over, and the unattended (non GO BOTTOM) load
time was over 25 minutes as I recall, as the records lolly-gagged their way
across the LAN. With the GO BOTTOM trick they were all across within
moments.
I shared my findings with Ed, who later ascertained that by doing a RECCOUNT
one could achieve the same high performance results without moving the
Record Pointer. If Record Pointer positioning was important, the GO BOTTOM
would have required me to read the current Record Position into
mPreviousRecno, GO BOTTOM, then GOTO mPreviousRecno. So, Ed's way is more
efficient, and is the way I handle ensuring fast Remote View loads now.
I hope that helps.
Gil
Gilbert M. Hale
New Freedom Data Resources
Pittsford, NY
585-359-8085
gil At gilhale DOT com
> -----Original Message-----
> From: profoxtech-bounces@leafe.com
> [mailto:profoxtech-bounces At leafe DOT com]On Behalf Of Dave Crozier
> Sent: Tuesday, May 31, 2005 10:46 AM
> To: profoxtech@leafe.com
> Subject: RE: SQL & ODBC vs. DBF & ODBC vs. DBF
>
>
> Stephen Said:
> "I have to call for a second opinion here. I have a table on my
> network.
> If I do Select * from MyTable where DateSold >=
> date()-(day(date()+1) I get
> a FILTERED Table, or in other words the entire table on my
> machine that has
> a filter applied. This table can be changed Really fast as
> needed but it's
> still the table."
>
> Andy is correct. You certainly DO NOT get the full table on your machine.
> The index (or parts of it) needs to be downloaded to set up the rushmore
> Filter (if it is needed) but only the requested records are kept on the
> machine. In the case where the select cannot use an existing
> index, yes the
> entire table is uploaded but only to a temporary file. The end
> "cursor" only
> contains the requested records and the "full download" is deleted.
>
> Hence the need to supply appropriate indexes to speed up record selection
> whenever possible.
>
> I can verify that this is correct as I have over 150 machines on
> our LAN/WAN
> using native VFP tables and if we were pulling all the data every time the
> LAN would grind to a halt!
>
>
> Dave Crozier
> DaveC At Flexipol DOT co.uk
> DaveC@Replacement-Software.co.uk
>
>
>
>
>
[excessive quoting removed by server]
©2005 Gilbert M. Hale |
|
