Tod means Fox

Supporting decisions through sound data management

Archive for May, 2007

Take the Survey!

May 16

Posted by Tod McKenna in Business & IT Issues

i took the 2007 survey Take the Survey! Take a moment to fill out this quick survey (takes about 5 minutes) published by A List Apart. The purpose of this survey is to create some metrics that can be used to gauge the vitality of Web Development as a whole. From A List Apart’s website:

Designers, developers, project managers. Writers and editors. Information architects and usability specialists. People who make websites have been at it for more than a dozen years, yet almost nothing is known, statistically, about our profession. Who are we? Where do we live? What are our titles, our skills, our educational backgrounds? Where and with whom do we work? What do we earn? What do we value?

Take the Survey here: http://alistapart.com/articles/webdesignsurvey

Tags: ,

No Comments

Microsoft and Open Source

May 15

Posted by Tod McKenna in In The News

As you know, Microsoft and Open Source have had an oil/water type of relationship. Here’s an article that gives us 10 things that MS loves and hates about Open Source. I post this here because FoxPro made the list:

The year-old open source project hosting Web site started by Microsoft lets users share open source development projects. The big news is that portions of Visual FoxPro will be posted as open source on Codeplex. A new version of the Web site is released every three weeks adding additional features and updates. As of early March, there were 1,029 projects on the site.

Tags: , ,

No Comments

Central Kentucky Computer Society offers VFP class

May 14

Posted by Tod McKenna in Events and Trainings

The Lexington Herald Reader out of Kentucky (http://www.kentucky.com) is reporting FoxPro database classes are available. The class looks to be a bit of an introduction to VFP databases, but I’m not sure. If anyone is in the area and would like to check it out, the info is provided below:

The Central Kentucky Computer Society offers workshops in many basic, intermediate and advanced subjects at 160 Moore Drive, Suite 107. All workshops are at 7 p.m. unless otherwise noted. Workshops are for members, but the public is invited. Call (859) 373-1000 for more
information. Small Business, Wednesday; MS Access, Thursday; Investments, Saturday; Database FoxPro, May 21; Digital Photography, May 22; CADD, May 23; Unix/Linux Network, May 24; Computer Clinic, May 26.

Tags: ,

No Comments

What’s faster? SQL or looping to return a set of records?

May 10

Posted by Tod McKenna in Software Development

When you need to get a set of records (for example, to return a list of items from a lookup table, to prepare a report, or to fill a read-only grid), which method should you use?

Of course, the answer is ‘it depends’ and many factors are at play. It is part of our job as developers (or programmers, if you’d prefer) to pick the solution that best fits the scenario. As much has been written on this topic already, I won’t be revealing anything new here: Just a re-package, if you will.

First, the basics. There are several ways to go through a table with the intention of producing a set of resulting records. You can SCAN, DO WHILE, use a FOR..NEXT loop, or use SQL syntax. The first three options are procedural and process one record at a time. The last (SQL) is set-based. To get an idea on what looping mechanism to use, take a look at Andy Kramek’s blog entry from March 2006 called “Writing Better Code (Part 2)”. In this entry, Andy does a great job of showing you the effects of indexes on the three looping mechanisms, and should give you a good idea which one to use and when.

Using SQL syntax, you can return a result of records as well. You can replace your looping syntax with SQL syntax and get the same results (using much less code). When gathering data for a report, for example, I have often used the loop method to populate a special report cursor. I was inclined to stick with the procedural approach in the past because (a) it was easy enough to write, (b) easy to debug, (c) allowed me to easily insert a custom progress bar if needed, and (d) because I thought it was best. As my SQL skills improved, I found that SQL syntax is actually (a) easier to write, (b) just as easy to debug, (c) capable of supporting a custom progress bar, and (d) may in fact be best under many circumstances. This is especially true if you are trying to write portable code (duh!).

To demonstrate my point, I first took a table with 125,000 records with no index (CDX) file. This first case will process each record in the table. My benchmarking methodology here would make anyone who has ever done benchmarking in a scientific way before blood boil, but hey, I never claimed to be a scientist (I don’t even play one on TV)!

First, are we all on the same page?

SET ANSI OFF
SET EXACT OFF
SET TALK OFF
SET DELETED ON
CLEAR

Carry on…

USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
LOCATE
DO WHILE NOT EOF()
    INSERT INTO crName VALUES (name_list.name)
    SKIP
ENDDO
? SECONDS() - nSecs && results: 0.313 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    INSERT INTO crName VALUES (name_list.name)
NEXT
? SECONDS() - nSecs && results: 0.281 seconds (10 runs)
USE IN name_list
 
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list INTO CURSOR crName
? SECONDS() - nSecs && results: 0.000 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    INSERT INTO crName VALUES (name_list.name)
NEXT
? SECONDS() - nSecs && results: 0.281 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list INTO CURSOR crName
? SECONDS() - nSecs && results: 0.000 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list INTO CURSOR crName
? SECONDS() - nSecs && results: 0.000 seconds (10 runs)
USE IN name_list

So what on earth is going on? The SQL Statement processes that entire result in (literally) no time at all! The answer can be found in a comment made in a recent blog posting of mine. Sergey explained that VFP doesn’t actually run any SQL. All it does is create a filtered cursor on the underlying dataset. This is why RECCOUNT could be “wrong”, as discussed in my blog entry. Check out that link to Sergey’s site to read more about this. You can check what you got simply by issuing JUSTEXT(DBF()) (as Sergey does): “DBF” means a filtered cursor was created, while “TMP” means that a new cursor was created.

Additional Notes:

When there is a CDX file (with no index set), the FOR loop seems to suffer a little: On average, the FOR loop takes an additional .020 seconds to run. The existence of a CDX file does not seem to bother the DO WHILE or SQL method in the above case.

Now, let’s add a condition that the name was added to the table on or after 01/01/2005. The first run of this test is with no CDX file at all. If a CDX file exists, but no index set, then the results are a bit different (see notes after the code):

 
USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
LOCATE
DO WHILE NOT EOF()
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
    SKIP
ENDDO
? SECONDS() - nSecs  && results: 0.234 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
NEXT
? SECONDS() - nSecs  && results: 0.235 seconds (10 runs)
USE IN name_list
 
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.125 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
NEXT
? SECONDS() - nSecs  && results: 0.235 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.125 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.125 seconds (10 runs)
USE IN name_list

Here, with no CDX file, VFP seems to actually process the SQL (it doesn’t create a filtered cursor), and does so rather fast. If you add an INDEX on created date, FoxPro goes ahead and creates the filtered cursor (which again is processed in zero seconds).

OK, so now what if I actually use the INDEX, and SET ORDER TO before all of this?

USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
LOCATE
DO WHILE NOT EOF()
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
    SKIP
ENDDO
? SECONDS() - nSecs  && results: 1.047 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
NEXT
? SECONDS() - nSecs  && results: 0.235 seconds (10 runs)
USE IN name_list
 
*-----
 
USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.000 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
CREATE CURSOR crName (name c(20))
SELECT name_list
FOR nRec = 1 TO RECCOUNT()
    GOTO nRec
    IF created>={^2005-01-01}
        INSERT INTO crName VALUES (name_list.name)
    ENDIF
NEXT
? SECONDS() - nSecs  && results: 0.235 seconds (10 runs)
USE IN name_list

*-----
 
USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.000 seconds (10 runs)
USE IN name_li
*-----
 
USE name_list IN 1 EXCL ORDER created
nSecs = SECONDS()
SELECT name FROM name_list WHERE created>={^2005-01-01} INTO CURSOR crName
? SECONDS() - nSecs  && results: 0.000 seconds (10 runs)
USE IN name_list

The Do WHILE really gets hit hard, as you can see. The FOR LOOP works as
well as before, and the SQL once again gives us a filtered cursor.

As you can see, you can really take advantage of this behavior to create simple cursors with simple predicates. Even if VFP can’t create the filtered cursor (for example, applying an ORDER BY clause on the SQL statements), the results are still usually faster.

In a subsequent posting, I’ll increase the complexity of these tests. In the meantime, I would be interested in any feedback to these cases.

Tags: , , ,

4 Comments

VFP Conference Attendance

May 9

Posted by Tod McKenna in Events and Trainings

Keep your eye this year on how well VFP conferences are attended. Although not a fully scientific representation of how the community is keeping up on VFP, it certainly can be used as an “interest gauge”.http://fox.wikis.com/wc.dll?Wiki~VFPConferenceAttendance

It is worth mentioning (which falls in line with the theme from my last post) the fact that conferences seem pretty well attended around the world. The Adviser conferences have been steadily declining for quite a while. It would be nice to see some mention of South America, which has a large Fox community.

Tags: ,

No Comments

Not So Big Anymore

May 4

Posted by Tod McKenna in Personal

Now that we have our new car (a VW Rabbit, no less), I’m starting to notice something: People don’t seem to respect me on the road as much as they did when I had the Jeep. I get cut off more, produce less of an impact when I tail-gate the slower folk, and have also noticed that I get less ‘looks’ — almost like I’m not even there.

My wife thinks I’m an aggressive driver (and based on my tail-gate comment, I bet you think that now too). I’ve always maintained that I am a defensive driver who has a little offense in him as well. Apparently those days are over, though. Looks like I have to shape up, calm down, and stay in my lane. I can’t even see over the feller in front of me anymore.

vw rabbit1975 Not So Big Anymore

On the bright side, this car is FAST and fun to drive. It has this “S” mode (which stands for Sport) and can accelerate much faster than the Jeep. Plus it feels good not to worry about tipping over so much. I still need to play with the tiptronic. Lastly, this car is much, much more fuel efficient. Which is good for everyone except Mobile and Shell.

Tags: ,

No Comments

Take That, Microsoft!

May 4

Posted by Tod McKenna in In The News

We just picked up 5 new licenses for VFP9, with the more on the way. I am very happy about this decision (it was in management’s hands, certainly not mine). We are in the process of migrating a VFP7 application, which has a few hundred installations around the country, to VFP9. The move was almost squashed thanks to MS’s latest announcement. VFP9 SP1 will make our application run better, give the engineers here more features and tools, open more doors into the XML world, and allow us to bother MS for support a little while longer.

Tags: , ,

1 Comment