{"id":332,"date":"2004-09-30T21:02:19","date_gmt":"2004-09-30T13:02:19","guid":{"rendered":"http:\/\/foxpro.ntsl119.com\/scr\/?p=332"},"modified":"2007-10-29T11:49:35","modified_gmt":"2007-10-29T03:49:35","slug":"vfp-sql-verus-do-while","status":"publish","type":"post","link":"https:\/\/foxpro.ntsl119.com\/scr\/archives\/332","title":{"rendered":"VFP SQL verus DO WHILE"},"content":{"rendered":"

172<\/font> bugs-to-squash+features-to-code down… 1,161,017<\/font> to go. I’ll break from the task for a while and blog a little.<\/p>\n

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<\/font> folds. <\/p>\n

Bad. Too bad really.<\/p>\n

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.<\/p>\n

Because the tables are normalized (UGH I still have an unfinished discussion blog about normalization<\/a>), 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. <\/p>\n

(More than 30<\/font> seconds of query-ing is slow by Foxpro standards (we’re so used to Rushmore’s legendary lightning speed technology)).<\/p>\n

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.<\/p>\n

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’.<\/p>\n

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.<\/p>\n

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:<\/p>\n

cDATABASE = 'BOOKSASM'
\r\nset safety OFF
\r\n<\/font>select<\/font> BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, BOOKS.ISBN, ;
\r\n      BOOKS.BORROWED, min(AUTHORS.NAME) as [NAME], ;
\r\n      LOCATION.LOCATION ; 
\r\n   from<\/font> BOOKS, AUTHORS, LOCATION ;
\r\n   into dbf<\/font> &cDATABASE ;
\r\n   group by<\/font> BOOKS.ACCESSNO, BOOKS.CALLNUM, BOOKS.TITLE, ;
\r\n      BOOKS.ISBN, BOOKS.BORROWED, LOCATION.LOCATION ;
\r\n   where<\/font> ((BOOKS.ACCESSNO == AUTHORS.ACCESSNO) or ;
\r\n      BOOKS.NUM_AUTHOR == 0) ;
\r\n      and BOOKS.LIBSECTION == LOCATION.ID 
\r\nset safety ON
\r\n   
\r\nselect BOOKSASM
\r\n   if !file('BOOKSASM.cdx')
\r\n      index on alltrim(ACCESSNO) tag CBOOIDNO
\r\n      index on upper(alltrim(CALLNUM)) tag CBOOCALL
\r\n      index on upper(alltrim(TITLE)) tag CBOOTITL
\r\n      index on alltrim(LOCATION) tag CBOOLIBS
\r\n      index on alltrim(ISBN) tag CBOOISBN
\r\n      index on upper(alltrim(NAME)) tag CBOONAME 
\r\n   endif
\r\n   
\r\nclose databases
\r\nclose all<\/pre>\n
\nGuess how long it took for that piece of code to process 2224<\/font> records in BOOKS.dbf, 2856<\/font> records in AUTHORS.dbf and 15<\/font> records in LOCATION.dbf?<\/p>\n
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.<\/p>\n
52<\/font> minutes… for that code… for that number of records.<\/p>\n
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<\/font> server and transferred it to the local drive for faster processing.<\/p>\n
52<\/font> whoopin’ minutes!<\/p>\n
So when I got home. I re-configured the code using a reliable old school approach… DO WHILE\/FOR LOOPS + SET FILTER TO. <\/p>\n
Here…<\/p>\n
select BOOKS\t
\r\nnCountTotal = reccount()
\r\nnCounter = 0
\r\nset filter to 
\r\nset order to CBOOIDNO
\r\ngo top
\r\ndo while not eof()\t\t
\r\n   nCounter = nCounter + 1
\r\n\t\t
\r\n   cAccessNo = BOOKS.ACCESSNO
\r\n   cCallNum = BOOKS.CALLNUM
\r\n   cBookTitle = BOOKS.TITLE
\r\n   cISBN = BOOKS.ISBN
\r\n   lBorrowed = BOOKS.BORROWED
\r\n   cLibSection = BOOKS.LIBSECTION
\r\n\t\t
\r\n   select AUTHORS
\r\n   set filter to (AUTHORS.ACCESSNO == cAccessNo)
\r\n   go top
\r\n   cAuthor = AUTHORS.NAME
\r\n   set filter to
\r\n   cLocation = ''
\r\n   
\r\n   select LOCATION
\r\n   set filter to 
\r\n   set order to CLOCIDNO
\r\n   go top
\r\n   seek cLibSection
\r\n   if found()
\r\n      cLocation = LOCATION.LOCATION
\r\n   endif
\r\n             
\r\n   select BOOKSASM 
\r\n   append blank
\r\n      replace BOOKSASM.ACCESSNO with cAccessNo
\r\n      replace BOOKSASM.CALLNUM with cCallNum
\r\n      replace BOOKSASM.TITLE with cBookTitle
\r\n      replace BOOKSASM.ISBN with cISBN
\r\n      replace BOOKSASM.BORROWED with lBorrowed
\r\n      replace BOOKSASM.NAME with cAuthor
\r\n      replace BOOKSASM.LOCATION with cLocation
\r\n             
\r\n   .cStatusCounter.value = str(nCounter) + '\/' + str(nCountTotal)
\r\n             
\r\n   select BOOKS 
\r\n   skip
\r\nenddo<\/font><\/pre>\n
\nNow guess how long this code processed those 2224*2856*15<\/font> (Yes * not + : figure that out).<\/p>\n
Less than 15<\/font> seconds. <\/p>\n
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.<\/p>\n
OK… let me write a code to grab the exact time… wait.<\/p>\n\n\n\n
TimeStart:<\/td>\n
<\/p>\n
20:48:46<\/font><\/td>\n<\/tr>\n
TimeEnd:<\/td>\n20:48:54<\/font><\/td>\n<\/tr>\n<\/table>\n
It is even less than 10<\/font> seconds!<\/p>\n
So tell me… what am I missing here?<\/p>\n
(Well aside from the fact that VisualFoxpro is fast and it rocks… everyone knows that already… including VB-lovin’ humans [GRIN])<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,10],"tags":[],"class_list":["post-332","post","type-post","status-publish","format-standard","hentry","category-visual-foxpro","category-workblog"],"_links":{"self":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts\/332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/comments?post=332"}],"version-history":[{"count":0,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/posts\/332\/revisions"}],"wp:attachment":[{"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/media?parent=332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/categories?post=332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/foxpro.ntsl119.com\/scr\/wp-json\/wp\/v2\/tags?post=332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}