I see you point about the allocate(0)...I'll see if that makes a
difference.
IIRC specifying ALLOCATE or not specifying it does not matter for varying
length fields shorter than 30 Characters.
For those short fields the complete value gets stored within the column and
the overflow area is not used at all.
For larger fields ALLOCATE(x) should be specified, with a number x should be
specified. X should be defined as large, at least 80% of all data can be
stored within the column and only for 20% of the overflow area must be
accessed.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Charles Wilt
Gesendet: Thursday, 22. July 2010 20:16
An: Midrange Systems Technical Discussion
Betreff: Re: Varying fields and SQL predicate LIKE
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.
midrange.com
