Home » Archives » February 2004 » VFP SQL verus DO WHILE

[ Previous entry: GEEKS and POEMS ]
[ Next entry: Blogging the RP tech scene ]
02/23/2004:

VFP SQL verus DO WHILE


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])


Disclaimers are for castrated EARTHLINGS.
Powered: GREYMatter | GM-RSS

 

 
 
 
 

 

foxpro.main
foxpro.archives
richardbase.home

articles
downloads
snippets
utilities
knowledgebase.links
website.links

outpost.forum
the.site
the.catalyst
pixelcatalyst.lair

rss.feeds

February 2004
SMTWTFS
   1234
567891011
12131415161718
19202122232425
26272829   
February 2006
January 2006
December 2005
November 2005
October 2005
September 2005
August 2005
July 2005
June 2005
May 2005
April 2005
March 2005
February 2005
January 2005
December 2004
November 2004
October 2004
September 2004
August 2004
July 2004
June 2004
May 2004
April 2004
March 2004
February 2004





GEEK count:
visitors since the aliens rebooted the counter last 02.23.2006 (was around 33,000++ before the alien intrusion | SINCE: 02.26.2004)