Fuzzy Browse Speed

[Bruce Easton]

> Howard Wolowitz wrote Friday, February 16, 2007 4:24 PM:
> 
> > Nancy Palmquist wrote February 16, 2007 3:20 PM
> >
> >I was wondering if anyone had a trick to offer to make the Browse Lookup
> > that is using a Fuzzy Search finish any faster.
> >
> > I am searching a file that fundementally has a STATE & City field.  I
> > want to find a city  that matches my input, in the state I indicated.
> >
> > So I enter a CSTATE code, then I enter a CITY.
> >
> > I run a lookup  like this:
> >
> > kk=CITY
> >
> > 
> >   -------   -   -   -   -   -   -   -   -   -   -   -   -   -   
> -   -   -
> >       ◄ If:
> >       Then: lookup card = cardfile  Z=49   K=kk   -n b="(brw=12,9,-1
> > fuzzy=20 xkey=X show=pkeep pop=2 prc=matchst fill=asc)[City/Ent
> > 53  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -    -
> >       ◄ If:
> >       Then: ity                     Payto                OLD CITY]*49
> >                            *3                   *1"
> > 54  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -    -
> >    [These lines do what happens if I pick something or don't pick
> > something]
> >
> >
> >  58  -------   -   -   -   -   -   -
> > matst  ◄ If: cstate ne card(48)
> >        Then: drop
> >  59  -------   -   -   -   -   -   -
> >        ◄ If:
> >        Then: end
> >  60  -------   -   -   -   -   -   -
> >
> >
> > I tried DROP ALL and DROP ALL AFTER - but a fuzzy search works by record
> > and I think it does not understand these options.
> >
> > Anyone got an idea.
> >
> > Nancy
> 
> Remember Nancy, a fuzzy search has to read every record to find the best 
> matches.  That said, it may be faster if you create a new field 
> and populate 
> it with the city, a space and the state.  Build an index on the new field 
> and then use that index in the search.
> 
> Just a guess.
> 
> Howie
> 

Nancy, I'm not sure from your example whether or not you already 
have a field that is a concatenation of city and state.  If you did, 
then maybe it would help to change your key to use that same 
concatenation instead of just city, and then still drop unwanted 
states. I don't use these much - I'm not sure how an index figures 
into it, as Howie and Richard are indicating.  Maybe index scan 
affects these types of browses?   Also, Nancy, I'm assuming that 
you really want to keep using a fuzzy browse for some reason.

Also, if you did wind up with a concatenated field of city and state, 
I'm not sure if fuzzy would do differently for:

state city
vs.
city state

in terms of speed.  I'd go with state city, so that I could 
use "STlikethiscity" as the key where "ST" is the state 
you are currently interested in (but then maybe it makes no 
difference to Fuzzy - I wonder how Fuzzy might perform 
differently if you were using full state names instead of 
two-letter codes - seems like it would be longer to compare 
longer strings, but maybe you would wind up performing less 
dropping - OK now I have a headache).

Bruce

Bruce Easton
STN, Inc.