Re: fast search or scan for words in selected field

You would want an index where that one field is the first key field.
I was thinking of multiple selection criteria:
But if I want to search by name, and/or state, and/or phone#, and/or
contact name, then you would want multiple indexes. One of the
slower things to do is have the optimizer build a temp index.
In the above search, which ever search is for an exact match
(STATE = 'FL') should always be before a LIKE selection, so
select custid from cusmas where state = 'FL' and name like '%MARTIN%'
is much faster than
select custid from cusmas where name like '%MARTIN%' and state = 'FL'
(i'm not aware sql will reorder your select string, although they talked
about
big improvements in v5r3 sql performance).
btw- need to properly do name selects when apostrophe in name like O'HENRY.
C* Add an extra single quote (BJ'S  =  BJ''S)

Speed - I think it's very fast, but based on cpw. The system understands
repetetive
searching, so your calling a test once may be slower than 5 people each
calling the
search multiple times.
jim

----- Original Message ----- 
From: "Booth Martin" <booth@xxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, September 10, 2004 11:50 AM
Subject: RE: fast search or scan for words in selected field


How would various indices have any impact when the key words to be searched
are all within one field?

---------------------------------
Booth Martin
http://www.martinvt.com
---------------------------------
-------Original Message-------

From: Midrange Systems Technical Discussion
Date: 09/10/04 10:30:58
To: 'Midrange Systems Technical Discussion'
Subject: RE: fast search or scan for words in selected field

You know my position on SQL vs. native I/O, Booth, and this is one of
those places where I highly recommend SQL.  Pay heed to Jim's points
about making sure you have good indices in place, and even then the
performance issues are definitely going to depend on the size of your
file and the type of query.  The LIKE query in particular has
performance implications, but it's also very powerful.

The nice thing about this sort of technique is that you can add a lot of
additional query capabilities with the same basic approach.  Your users
can order by selected fields, select/omit on multiple criteria, and all
that good stuff.  The more complex the requirements, the better SQL is
as a tool with the caveat always being that a bad query can make
performance suffer.

The other issue is whether or not a bunch of these queries at the same
time will hurt your machine.  I THINK that multiple queries run
interactively may adversely affect machines with a governed interactive
CPW, although I haven't run the tests that prove this.

Joe

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