Need help improving lookup speed. 
Author Message
 Need help improving lookup speed.

try to view this in a non-proportional font, like Courier.
===========================================================

The comparison string in your example suggests that ozip2 is always
greater than ozip1, i.e. ozip1 <= 809 <= ozip2, i.e.
=between(809,ozip1,ozip2).  You imply the same for the dzip1 and dzip2,
i.e. dzip1<dzip2 ALWAYS.  If that is the case, I believe the following
will solve your challenges.

locOzip = 809
locDzip = 926

THE RECORD IS    ozip1     ozip2     dzip1     dzip2
                 800       819       900       929

Since I am not exactly sure what ALL your data looks like,
I really don't know if this will work, but perhaps it will
give you some alternatives.  My tests gave results of
less than one second in 8000 record table. You may be satisfied
with your 4 second solution unless for some reason the intermediate
tables are difficult to maintain.  In that case, perhaps this
idea will help.  

I created a data table TEST.DBF with records like this:

ozip1  ozip2  dzip1  dzip2
100    119    120    139
100    119    140    159
.
.
100    119    980    999
120    139    100    119
120    139    140    159
.
.
980    999    100    119
980    999    120    139
.
.
980    999    960    979

This created a data table of 1980 records.

****************** SELECT - SQL COMMAND
SELECT RECNO() ;
        FROM test ;
        WHERE BETWEEN(809,ozip1,ozip2) ;
                AND BETWEEN(926,dzip1,dzip2) ;
        INTO ARRAY aTest

"SELECTED 1 RECORD IN 1.4 SECONDS"  (SUBSEQUENT CALLS .71 SECONDS)
******************************************
But the following is better still....

I created the following index tag

INDEX ON (ozip2*1000000000000)+(ozip1*1000000000) ;
            +(dzip2*1000)+dzip1 TAG mastord

This way, after an initial seek you will only have to
LOCATE through a small number of records, i.e. if there are
only 100 records with ozip1 = 800 and ozip2 = 810, then the
most records you would have to LOCATE for is 100, but sometimes
you would only search 1,4,6, or 10 records.  You may be able to
beat 4 seconds with this.

************* IDEA #1: USING NUMERICAL DATA
(all fields are type "N" 3.0
INDEX ON (ozip2*1000000000000)+(ozip1*1000000000) ;
            +(dzip2*1000)+dzip1 TAG mastord

**
PROCEDURE testsrch
PARAMETER m.nOzip, m.nDzip
PRIVATE m.nOzip, m.nDzip, m.nStartSec, m.nEndSec
SET ORDER TO mastord
SET NEAR ON                        && find closest match
m.nStartSec = SECONDS()
SEEK (m.nOzip*1000000000000)       && get to your record range FAST
LOCATE REST ;
       FOR BETWEEN(m.nDzip,dzip1,dzip2) ;
       WHILE BETWEEN(m.nOzip,ozip1,ozip2)
m.nEndSec = SECONDS()
WAIT WINDOW STR(m.nEndSec-m.nStartSec,6,4) NOWAIT
? Ozip1, ozip2, dzip1, dzip2       && verify found the correct record

** enter the following line in your command window
=testsrch(809,926)
Benchmark on this was .03 seconds

************* IDEA #2: USING ALPHA (CHARACTER) DATA
(made all the database fields type "C")
INDEX ON ozip2+ozip1+dzip2+dzip1 TAG mastord

**
PROCEDURE testsr2
PARAMETER m.nOzip, m.nDzip
PRIVATE m.nOzip, m.nDzip, m.nStartSec, m.nEndSec
SET ORDER TO mastord
SET NEAR ON
m.nStartSec = SECONDS()
SEEK PADR(m.nOzip,12,'0')
LOCATE REST FOR BETWEEN(m.nDzip,dzip1,dzip2) ;
       WHILE BETWEEN(m.nOzip,ozip1,ozip2)
m.nEndSec = SECONDS()
WAIT WINDOW STR(m.nEndSec-m.nStartSec,6,4) NOWAIT
? Ozip1, ozip2, dzip1, dzip2
RETURN

=testsr2('809','926')
-benchmark on this was .08 seconds

********
I then created the same database but in increments of 10 rather than
20.  This resulted in a 7921 record database.

using the numerical method....
=testsrch(809,926)     .0490 seconds
=testsrch(809,126)     .0100 seconds

the first required about 95 records in the LOCATE until found.
the latter required only 2 records in the LOCATE until found.

Granted, my database generator created records in numerical order,
so if you get poorer results, do a one time SORT on your database
by ozip1 ASC, ozip2 ASC, dzip1 ASC, dzip2 ASC.

Lastly, be sure your disk is completely defragmented.

The best news of all: this was done on a 386/40 with only 4MB RAM.

Your question intrigued me and I spent some time on this, so
PLEASE let me know if this works for you and how well.

HAVE FUN!

Is Your Church BATF Approved?



Mon, 30 Jun 1997 15:19:23 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Need help improving lookup speed.

2. slow retrieval speed, need improve!

3. LOOKUP HELP!!! needed

4. increase search speed on large table lookups

5. increase search speed on large table lookups

6. speed issues, need help

7. HELP NEEDED ON SPEED OF QUERY Please!

8. Indexing technique needed for large table lookup

9. Need a real lookup function

10. Help -- matching data in lookup table, writing to target table

11. I NEED HELP I NEED HELP CUSTOMIZING EXCHANGE 5.0

12. The need for speed.

 

 
Powered by phpBB® Forum Software