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
> 

Oops - I meant to say "ST likethiscity" above with the space in 
between.

Bruce

Bruce Easton
STN, Inc.