Re: Varying fields and SQL predicate LIKE

Totally agree
it took a while to explain to the users why they were getting such extracts
with %MAIN%
and then when we changed it to '% MAIN %' why they were NOT getting
addresses if N.MAIN ST

Ahh - users - you gotta love them



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



Charles Wilt
<charles.wilt@gma
il.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

07/22/2010 03:32 Subject
PM Re: Varying fields and SQL
predicate LIKE

Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






Alan,

True. But I don't see the extra hit as a problem. I'm simply
throwing the matches to the user so they can figure out which ( if any
) is the actual match.

On the other hand, the fact that '% MAIN%' wouldn't match '123 N.MAIN
ST.' could be a problem.

Charles

On Thu, Jul 22, 2010 at 2:42 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Charles
just my two penneth worth as I am also doing something similar
With like of '%MAIN%' you would also get anything like "12 DEMAINVAR

ROAD"

whereas using like of '% MAIN%' , this would NOT be a match



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



            Charles Wilt
            <charles.wilt@gma
            il.com>                                                    To
            Sent by:                  Midrange Systems Technical
            midrange-l-bounce         Discussion
            s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>
                                                                       cc

            07/22/2010 02:27                                      Subject
            PM                        Re: Varying fields and SQL
                                      predicate LIKE

            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>






Chuck,

I'm not following this part.  Why would '% MAIN%' be better than '%MAIN%'

In actually, I tried to be smart....when I built my varying search key
field, I did the following:
replace(translate(addr1
                         ,' '
                         ,';:.,!@#$%¢&*()-_+={}|¬¦\"<>?/~`')
             ,' ','' ) as cmpAdr

So that the original data '123 MAIN ST.' became '123MAINST'

Which lowered the average length of the data from 16 to 13. So I was
really looking for a 48% improvement...

I considered removing numeric characters also, ie. '123 MAIN ST.' -->
'MAINST', but decided that might not work given addresses like '123 SR
4'

In my testing I am running each statement multiple times and looking
at the average of the last few runs.  Though I haven't switch the
order as I'm running the statement over the fixed length field first,
so I'd expect if order mattered, the first statement would be the one
to suffer, not the second.

I see you point about the allocate(0)...I'll see if that makes a
difference.

Also, the table I built for testing had the fixed length column and
the varying one...I'll try setting up a couple of separate test
tables.

Charles


On Thu, Jul 22, 2010 at 1:07 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

  Since a blank is not the prefix of the data being searched, you
could not see the maximum potential improvement you are looking for.
 Try searching on '% MAIN%' instead, to effect a better test; not
that I trust the performance test results for both non-dedicated and
without averaged repeated tests including reversed order of any two
compared scenarios.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.