VFP SQL verus DO WHILE

Filed Under (Visual FoxPro, work.BLOG) by WildFire on 30-09-2004

172 bugs-to-squash+features-to-code down... 1,161,017 to go. I'll break from the task for a while and blog a little.

I visited a client yesterday confident that I had tweaked the ILS program to make things faster. I ended up realizing 'on site' that things got slower by 1560 folds.

Bad. Too bad really.

Because of some server folder/file-security-related issues, I have to separate the directories that are used for the ILS module (the main library system) and SASM (the book+author+subject+callnum+isbn searching) module that is being used by the students/faculty members in a given library.

Because the tables are normalized (UGH I still have an unfinished discussion blog about normalization), I have to use some VFP SQL queries to gather all the data needed from three separate tables. VFP SQL ((sans the JOIN.... yup using that makes the querying of data turtle-fast (no offense to the turtles)) is still slow.

(More than 30 seconds of query-ing is slow by Foxpro standards (we're so used to Rushmore's legendary lightning speed technology)).

For those who are not used to Visual Foxpro, VFP has it's own 'native' SQL functions which can be used without relying on ADODB, OLEDB, ODBC and whatever ODODODODO-database-related-technologies proliferating around.

So to tighten up things a little bit I decided to make a separate 'gatherer module' which will be executed on the server/admin part and will be thrown to the student/users PC used for searching instead of slowing down things every time the user 'SASMs'.

I tried using the VIEWS approach on this one (which apparently uses VFP SQL commands too) but have to withdraw at the last minute since there are some incompatibility issues with some table formats that I'm using and I don't want to completely dive in without testing things further.

So I decided to just create a different 'buffer table' that will hold the fields gathered from these three databases... using the native VFP SQL command. Here's the code:

cDATABASE = 'BOOKSASM'
set safety OFF
select BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, BOOKS.ISBN, ;
BOOKS.BORROWED, min(AUTHORS.NAME) as [NAME], ;
LOCATION.LOCATION ;
from BOOKS, AUTHORS, LOCATION ;
into dbf &cDATABASE ;
group by BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, ;
BOOKS.ISBN, BOOKS.BORROWED, LOCATION.LOCATION ;
where ((BOOKS.ACCESSNO == AUTHORS.ACCESSNO) or ;
BOOKS.NUM_AUTHOR == 0) ;
and BOOKS.LIBSECTION == LOCATION.ID
set safety ON

select BOOKSASM
if !file('BOOKSASM.cdx')
index on alltrim(ACCESSNO) tag CBOOIDNO
index on upper(alltrim(CALLNUM)) tag CBOOCALL
index on upper(alltrim(TITLE)) tag CBOOTITL
index on alltrim(LOCATION) tag CBOOLIBS
index on alltrim(ISBN) tag CBOOISBN
index on upper(alltrim(NAME)) tag CBOONAME
endif

close databases
close all

Guess how long it took for that piece of code to process 2224 records in BOOKS.dbf, 2856 records in AUTHORS.dbf and 15 records in LOCATION.dbf?

Well long enough for me to visit another office and install a newly updated AVRCOM module, discuss that module with the user, return and discuss (with the librarians this time) another set of topics from their lovelife to the difference between computer science and other courses with the 'IT' letters in it to topics which made me retrieve the baby pictures I have in my wallet.

52 minutes... for that code... for that number of records.

And I did not even include the instances where a conflict with the screensaver crashed the query process that I have to start things all over again, and that I copied the files outside the arms of a burdened Novell Netware 6 server and transferred it to the local drive for faster processing.

52 whoopin' minutes!

So when I got home. I re-configured the code using a reliable old school approach... DO WHILE/FOR LOOPS + SET FILTER TO.

Here...

select BOOKS	
nCountTotal = reccount()
nCounter = 0
set filter to
set order to CBOOIDNO
go top
do while not eof()
nCounter = nCounter + 1

cAccessNo = BOOKS.ACCESSNO
cCallNum = BOOKS.CALLNUM
cBookTitle = BOOKS.TITLE
cISBN = BOOKS.ISBN
lBorrowed = BOOKS.BORROWED
cLibSection = BOOKS.LIBSECTION

select AUTHORS
set filter to (AUTHORS.ACCESSNO == cAccessNo)
go top
cAuthor = AUTHORS.NAME
set filter to
cLocation = ''

select LOCATION
set filter to
set order to CLOCIDNO
go top
seek cLibSection
if found()
cLocation = LOCATION.LOCATION
endif

select BOOKSASM
append blank
replace BOOKSASM.ACCESSNO with cAccessNo
replace BOOKSASM.CALLNUM with cCallNum
replace BOOKSASM.TITLE with cBookTitle
replace BOOKSASM.ISBN with cISBN
replace BOOKSASM.BORROWED with lBorrowed
replace BOOKSASM.NAME with cAuthor
replace BOOKSASM.LOCATION with cLocation

.cStatusCounter.value = str(nCounter) + '/' + str(nCountTotal)

select BOOKS
skip
enddo

Now guess how long this code processed those 2224*2856*15 (Yes * not + : figure that out).

Less than 15 seconds.

I was even tempted to write an exact time counter after an initial test run but things were so fast that writing that part was not needed anymore.

OK... let me write a code to grab the exact time... wait.

TimeStart: 20:48:46
TimeEnd: 20:48:54

It is even less than 10 seconds!

So tell me... what am I missing here?

(Well aside from the fact that VisualFoxpro is fast and it rocks... everyone knows that already... including VB-lovin' humans [GRIN])