|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.