Re: Varying fields and SQL predicate LIKE

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.