What VFP 9's Removal of Array Limits Really Means

Interesting post by John K. about how the limits in VFP 9 have been substantially removed and yet may still rear their ugly head because of machine constraints.

But it explains very well how and why you may want to rethink how your applications may be built.

A great example is the removal of the limitations of an array.

Let's say you have 1,000,000 records in a table named X.

I created this table by listing all of the files in a folder showing two columns: one for the file name and another for the size.
CFIELD1 C(30)
NSIZE N(10)

=ADIR(la,"C:\windows\system32\*.*")
CREATE TABLE x (cField1 C(30), nsize N(10))
lnRow = 1
FOR lni = 1 TO 1000000
IF lnRow+lni>ALEN(la,1)
lnRow = 1
ELSE
lnRow = lnRow+lni
ENDIF
INSERT INTO X VALUES (la(lnRow,1),la(lnRow,2))
ENDFOR

This ensured I had at least some variety of data in my results.

In VFP 7, you couldn't do

SELECT * FROM x INTO ARRAY la

You would run out of memory.

However you CAN do it in VFP 9 which means that even though VFP is super fast with tables, it's even faster with memory.

Examples
Sorting Data
ASORT(la,2) took .698 seconds on a P IV with 512MB RAM.
INDEX ON nSize TAG t took 4.355 seconds in VFP 9

(note: ASORT(la,1) - which is the field name took approximately 2.5 seconds, the Index took 7 seconds)

Interestingly, not doing the index to a tag but to its own IDX took 1.6 seconds

Looking for Data
ASCAN(la,"WRITE.EXE") took .329 seconds
LOCATE FOR cField1="WRITE.EXE" took close to 1 second

(Note: If you do a SELECT * FROM X INTO CURSOR y and try the same things, the locate statement only takes about .600 seconds)

See where this is leading? If you have to process data in memory, it makes far more sense to put it into a cursor or better yet into an array.

And you can even put these arrays into objects for quick retrieval:

loCustomers = CREATEOBJECT("Custom")
loCustomers.AddProperty("myData(1,2)")
SELECT * FROM X INTO ARRAY loCustomers.myData

All the same performance benefits apply.

As database developers, we often think of things as tables for storing temporary data, but now with VFP 9, we can store them as memory variables and wow! the performance gets instantly better.

If performance is your thing, then VFP 9 is definitely king.


The Limits of Architecture vs. The Architecture of Limits (by John Koziol)

Comments

Popular Posts