Re: Varying fields and SQL predicate LIKE

Birgitta,

Thanks for the info about short varying fields...I did not know that.

I understand what you're saying about setting ALLOCATE so that 80% of
the fields fit. That is indeed how I normally use varying fields.

In this particular case, the table in question is specifically created
for searches in which the varying field is search using LIKE
'%something%'

Which will require a full table/index scan.

Chuck is suggestion that if we use ALLOCATE(0) to force all the data
into the overflow area, the system may find it easier (less i/o) to
just scan the overflow area as opposed to having the data stored in
the regular row storage.

I'll report back on the results :)

Charles

On Fri, Jul 23, 2010 at 1:29 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

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.

--
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.