Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


Speed Comparisons
Compiled by Tony McGuire

Query vs Setrange/Setgentfilter

The two cases cited here show a 600% and 1,100% gain in speed using setrange/setgenfilter vs query
(Note that you can perform a setrange, then a setgenfilter on the 'setranged' tcursor. This is where the enormous speed enhancement derives.)

There has been a lot of discussion about the GUI interface of Paradox®, and especially comparisons of Paradox® and Access. Usability is a major issue. Speed has also been an issue. I think that everyone agrees that each product has some strengths and some weaknesses.

I submit that the biggest difference is for the developer. There may be some things that are easier to do, and some things that are easier to control, in Access. However, you must commit yourself, and your programming, to MicroSoft's way of doing things ("Half-assed is good enough for the masses, they don't know what they are doing anyway").

Programmatic control is, in some ways, controlled by speed. You may be able to do some things, but speed is a large determining factor of whether you add that functionality into your product.

I have stressed the need for 'denormalization' in the Internet environment for several months. Most people on the Paradox® newsgroups have not agreed with my methodologies, since they fly in the face of good structure in the GUI interface in which Paradox excels above other products. There are many situations, however, where my beliefs prove themselves too well to be ignored. These situations include WAN and LAN, as well as Internet- in certain situations. I must also state here that I do very little in the way of development for desktop systems that make use of the GUI environment; nearly everything I do is tcursor or table variable related. My main focus is Internet related, and a lot of table updates from a desktop system to the server. That said, I will present my methodologies and attempt to explain why I believe they are better than 'normalized' methodologies in many cases. I do wish to stress that these methodologies apply to a WAN/Internet/maybe LAN environment. But then these are also the direction most of us must go if we are to provide for our companies' needs of tomorrow.

I also wish to stress that 'my' methodologies just make use of the functions and features that Paradox® provides; they aren't original. Many people, I'm sure, were using them long before I came along. I'm just attempting to promote the concepts.


From Roy Frieband and Paul Funk come two 'real world' experiences. These two tested and recorded the time that their functions took and reported the results comparing queries and setrange/setgenfilter. I wouldn't be surprised to hear that even better results come from optimizing their methods now that they are in place .

First, Roy Frieband's results:  The change in his 'query' was setrange/setgentfilter reduced the time to "final answer" from approx 1 minute 40 seconds to 15 seconds

"Hi All, This is a very interesting thread that is very pertinent to a thread I started a few weeks ago regarding how slow my application ran over a wireless LAN. I have since taken the suggestions of this group and have done multiple testing using a variety of different ways to extract the data I need.
  What I found was that what Tony suggests really does speed things up and considerably more than anything else I tried.
  I did have to "denormalize" my database somewhat in order to use his method. For example, I have a master table with a LongInt field PtID as the primary key. I then have a visit table with a LongInt field VisID as the primary key and PtID as a foreign key. There are multiple other tables related back to the visit table using VisID as a foreign key.
  The program runs a series of small queries relating the master table to the visit table and then the visit table to what ever other tables are needed. Since none of the other detail tables contain the PtID there is no way to use a setRange on the other detail tables. You can however use setGenFilter, which I tried.
  The original queries took around 1 minute 40 sec to run on the wireless LAN. Using a crieteria table approach where I queried out just the PtID I wanted to my local drive and used that as the master reduced the time to around 1 min 15 sec.
  Using setGenFilter on the detail tables and then using the filtered tables reduced the time to around 50 sec. I then "denormalized" the data and added another LongInt field to each detail table that contained the PtID. So this field truly is repetative and unecesary. It does not need to be there.
What it allowed me to do however was use switchIndex and setRange on my data and reduced the time needed to perform the operation to 15 seconds. More than 6 times faster than the original queries ran. WOW!!!!! I couldn't believe it. The only other thing I haven't tried so far is using SQL and I may attempt that soon but I don't know how much faster SQL will be than QBE in this case. Disadvantages to this approach would seem to be that you are storing a lot of uneeded data as each detail table needs to contain the PtID. So the tables would be larger than they really needed to be. Advantages would be increase in speed in low bandwidth situations and the program would be ready to be accessed on the web without rewriting anything further. I plan to explore web access to the data very shortly. Is there any other disadvantages to doing this that I am missing? The program as originally designed runs fine on a 100mbs network so the option would be to just use the hardwired network for now. I'm not sure what we'll end up doing but it was a very informative exercise."


From Paul Funk comes the next. His environment is a 128k remote connection  The change in his 'query' was setrange/setgenfilter reduced the time to "final answer" from approx 20 minutes to 1 min 45 secs

"Although my answer table was only 1,524 records, I didn't realize I was actually transfering all 126,000 records over the 128 KB line in order to get the result. I looked at the size of the answer table and couldn't figure why it took so long to transfer that data.

I then tried using setGenFilter and Scan TCursor to select only records that met the criteria. Apparently that works the same way as the query. Since the field I was selecting on was the second field in the table (second of three key fields), I didn't think I could use setRange, until someone suggested creating a secondary index, then setRange.

So this is the code I used:
if cmplTC.open(":dms:tmtccmpl") then
  cmplTC.SwitchIndex("distcode")
  cmplTC.setRange(distcode, distcode)
  ;msginfo("Info", "No of Records="+ string(cmplTC.nRecords()))
  if pcmplTC.open(":priv:TQMCMPL.DB") then
    pcmplTC.edit()
    scan cmplTC :
      if pcmplTC.insertRecord(cmplTC) then
        ;msginfo("Info", "Unable to insert TMTCCMPL record")
      else
        msginfo("Info", "Unable to insert TMTCCMPL record")
        quitloop
      endif
    endScan
    pcmplTC.close()
  else
    msginfo("Info", "Unable to open TQMCMPL file.")
  endif
  cmplTC.close()
else
  msginfo("Info", "Unable to open TMTCCMPL file.")
endif
The end result was a decrease in the elapsed time (the job included a few other minor functions) from approx 20 minutes to 1 min 45 secs (as little as 1 min 25 sec in one instance). I'll be using SetRange again, wherever possible!! "


Note that neither of these situation include the Internet. They are similar, however, in that both Paul and Roy had the same needs that an Internet site demands: programmatic control (vs GUI environment) with limited access speed to the data.
As # of records in the tables increase, the differences in time to final answer will grow larger.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.