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

I have an application where a lookup is performed in a table that contains
72k records.  It contains two zip code ranges and some data connected with
each pair of zip code ranges.  Right, now it takes approximately 30 seconds
to look up a record given two zip codes (one zip code for each range).  The
zip codes in the table and the ones being sought are represented within 3
digits.  I've tried to speed it up using different indexes and doing a
seek.  I've also built different kinds of queries and have not had a whole lot
of success.  

Does anyone have any ideas on how to increase the performance to where the
lookup can be done in less than 5 seconds?

Thanks for any and all replies.

scott

--
=============================================================================
  Scott Johnson                       |  Whitty quote...

=============================================================================



Mon, 23 Jun 1997 23:39:25 GMT  
 Need help improving lookup speed.

%f>I have an application where a lookup is performed in a table that
%f>contains 72k records.  It contains two zip code ranges and some data
%f>connected with each pair of zip code ranges.  Right, now it takes
%f>approximately 30 seconds  to look up a record given two zip codes
%f>(one zip code for each range).  The zip codes in the table and the
%f>ones being sought are represented within 3  digits.  I've tried to
%f>speed it up using different indexes and doing a  seek.  I've also
%f>built different kinds of queries and have not had a whole lot  of
%f>success.  
%f>Does anyone have any ideas on how to increase the performance to
%f>where the lookup can be done in less than 5 seconds?

%f>Thanks for any and all replies.

%f>scott

%f>  
%f>--
I'm not sure what you mean by represented with in 3 digits.

With Exact set to off the SEEK command will find a record based on a
partial match. eg: SEEK 'COPE' would find the first record in the where
the key field in the index starts with 'COPE'.

You migh try indexing on the two fields and then use SEEK with exact off
to locate the first target and the LOCATE REST WHILE to find a match for
the second target.

If you can give me more details about the 3 digit question I may be able
to come up with another approach.


---
* CmpQwk #UNREG* UNREGISTERED EVALUATION COPY



Tue, 24 Jun 1997 12:08:00 GMT  
 Need help improving lookup speed.

Quote:
>Does anyone have any ideas on how to increase the performance to where the
>lookup can be done in less than 5 seconds?

Enter your response to the quote. Control-Z when Complete.  /HELP for Help.
You could work from a RAM (virtual) drive if your DBF will fit in whatever
RAM you can make available.  A SEEK in a RAM drive will be much faster than
from an actual physical disk.

Also, are you doing this on a single PC or a network.  The Developer's
guide has some hints for optimizing when a network is involved, which
relate to the fact that you will get faster speed by putting copies
of critical files on the local PC instead of doing a seek all the way
across the LAN.

Hope this helps.

Regards,
David Nason



Wed, 25 Jun 1997 10:01:11 GMT  
 Need help improving lookup speed.

Thanks for everyone's replies.  I ended up increasing the performance by
building an index for the origin zip and the dest. zip.  Using these, I
was able to decrease the lookup time to where it was tolerable.  Reply to
this, if anyone is interested in more details.

scott  
--
=============================================================================
  Scott Johnson                       |  Whitty quote...

=============================================================================



Fri, 27 Jun 1997 23:36:42 GMT  
 Need help improving lookup speed.

Quote:

>I have an application where a lookup is performed in a table that contains
>72k records.  It contains two zip code ranges and some data connected with
>each pair of zip code ranges.  Right, now it takes approximately 30 seconds
>to look up a record given two zip codes (one zip code for each range).  The
>zip codes in the table and the ones being sought are represented within 3
>digits.  I've tried to speed it up using different indexes and doing a
>seek.  I've also built different kinds of queries and have not had a whole lot
>of success.  

>Does anyone have any ideas on how to increase the performance to where the
>lookup can be done in less than 5 seconds?

It's not clear how you are indexing your table nor how you are
performing your query, but I find it hard to believe that it takes
30 seconds to search 72,000 records on an indexed field.  That sounds
an awful lot like a full table scan - no index being used.  Either
your index is not built correctly, is not in use via a SET ORDER
command or you are querying on an expression which does not match
your index expression.  If none of these problems exist, perhaps you
could be a little more specific in the description of your table(s)
and index including the sructure of your table and a couple of example
rows.

-- Tim
--
\_\_\_\_\_   \_\_\_\_   \_    \_    \_
     \_       \_    \_    \_   \_\_  \_
      \_       \_\_\_       \_  \_ \_ \_
       \_       \_    \_      \_     \_   ><_>



Sun, 29 Jun 1997 05:05:09 GMT  
 Need help improving lookup speed.

Quote:

> >Does anyone have any ideas on how to increase the performance to where the
> >lookup can be done in less than 5 seconds?

In FP2.6 there is a new F() -- KEYMATCH() -- doesn't move the record
pointer, only uses the indices, much faster


Tue, 01 Jul 1997 12:59:48 GMT  
 
 [ 8 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