CodeDump-20040825.153618 (Brute Lee)

Filed Under (Visual FoxPro, work.BLOG) by WildFire on 26-08-2004

Code Dump: the time where you take a break from your programming related tasks to talk about your programming related tasks.

In PROJECT::SOPHIEAI (IUMS: Internet Usage Monitoring System), USAGE.dbf holds the records that stores the log-in/log-off information of the users of a certain internet lab/cafe at a given time and space. Along with the IDNO number of the user of course and some other fields.

Violating the second rule of normalization (or was it the third rule), I also included the TotalNumberofHoursUsed per usage and the TotalFee paid.

If you follow strictly the normalization rules, you know that you don't have to include in the database fields that can be derived from two or more fields that are already in the database.

For example if your database has fields named Field01 and Field02 with sample values 10 and 618 respectively, you don't need a Field03 which will store 628, the total of Field01 + Field02. Normalization rule will then call you moronic and consider you an outcast of the database programming world.

You deserve to be eaten alive by vultures.

You deserve a punishment such as watching Barney re-runs again and again.

But in some cases, yes... believe me... there are times when you have to violate and override this rule. These are the cases when speed-related principles are far more important. Plus... considering that these days hard disk space is getting cheaper and time (since Mida's era) is constantly gold, it justifies the normalization override process.

So back to PROJECT::SOPHIEAI.

Now because of some 'outside factors' (again), I need a patch that would make a different database, CRED_NET.dbf, which holds the fields TimeLeft and Credit be synchronized with the data USAGE.dbf holds.

Let's say USAGE.dbf records for student 0001 are (let's just take the important fields for discussion):

DATE IDNO TotalTime Fee
20040812 0001 01:00:00 25.00
20040815 0001 02:00:00 50.00

Now CRED_NET on the other hand holds how much is left for a student 0001. There are instances when the values on both databases don't synchronize. Yes the existence of these databases violate the normalization rule once again but you have read the reasons above already, right?

And can you stop reminding me about normalization rules so we can go on?

So to correct things you execute this untweaked patch (I'll call it the 'brute force approach'... 'Brute'... like 'Brute Lee')


   select CRED_NET
   set order to CCRENAME
   do while not eof()
      cIDNO = alltrim(CRED_NET.IDNO)

      **' ···································· 
      **' SECTOR.007.BEGIN

      select USAGE
      set order to CUSAIDNO
      go TOP
      do while not eof()
         if alltrim(USAGE.IDNO) == cIDNO
            cTimeCount = TimeCalc(cTimeCount, USAGE.TIMETOTAL, 1)
            nCreditsTotal = nCreditsTotal + USAGE.FEENET
         endif
         skip
      enddo

      **' ···································· 
      **' SECTOR.007.END

      select CRED_NET
      cTimeReflect = TimeCalc(cTimeCount, '40:00:00', 2)
      nCreditsReflect = 800.00 - nCreditsTotal
 
      rlock()
         replace CRED_NET.TIMELEFT with cTimeReflect
         replace CRED_NET.CREDIT with nCreditsReflect
      unlock

      skip
   enddo

The above code works... of course, but... like any process in this world that involves brute force, it is slow.

Now replace the SECTOR.007 part of the code above with this:

   select USAGE
set order to CUSAIDNO
set filter to alltrim(USAGE.IDNO) == cIDNO
go top
do while not eof()
cTimeCount = TimeCalc(cTimeCount, USAGE.TIMETOTAL, 1)
nCreditsTotal = nCreditsTotal + USAGE.FEENET
skip
enddo

You'll see a HUGE difference in terms of execution speed.

TimeCalc is a function by the way that adds, substracts, multiplies and divides two time variables. Something which I'll post later. (You can find the basic/raw version inside the Microsoft Help and Support Site though),

Of course when you're doing these kinds of patches, don't forget the progress bars. It is a good hypnotic-mechanism for the users to develop this affinity for your software.

CodeDump-20040825.153618.End

Now back to work.