Re: SQL - Slow Like or Between

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

The main thing is, you don't want a table scan anywhere, if possible. 
Anything else, bring some details here, maybe someone of us can help 
interpret things.

On 10/24/2013 12:16 PM, Michael Ryan wrote:
> I'm going to do that, but I find that it's difficult to interpret (for me
> obviously). I can tell if an index is needed - it tells me. But other than
> that, it's tough for me to figure out what it's telling me.
>
>
> On Thu, Oct 24, 2013 at 1:09 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:
>
> > Have you run your queries through Visual Explain? I find it to be
> > essential to do this when doing this kind of work.
> >
> > For the ones with parameter markers or host variables, you can put in
> > actual values in the Navigator tool.
> >
> > Vern
> >
> > On 10/24/2013 11:59 AM, Michael Ryan wrote:
> > > Note: Cross posted because it's SQL in RPG, but I think it's just an SQL
> > > issue.
> > >
> > > A continuation of an email that I sent a couple of days ago, and got some
> > > good feedback. I'm returning a result set to RPG or PHP via a stored
> > > procedure.
> > >
> > > On further analysis, I need to provide a starting point for record
> > access.
> > > I was using static SQL and a Between clause for each of 10 fields between
> > > *LOVAL and *HIVAL. Takes too long to run...like 60 seconds over an 800K
> > > record file.
> > >
> > > I created individual indexes (EVI's) over each of the 10 possible
> > columns.
> > > Tried static SQL with a technique like this: Where :xxvnda1 in (' ',
> > > IMVNDA)  And.... Still too slow.
> > >
> > > Tried dynamic SQL by creating the statement using equal, like this:
> > >    SQLStmt = SQLStmt + cBlank + 'IMVNDA =' + cBlank + cQuote + xxvnda1 +
> > > cQuote; Much faster when looking for an equal, but doesn't return records
> > > that don't match exactly.
> > >
> > > Tried dynamic SQL using Like, and it's as slow as the initial static
> > > technique.
> > >
> > > I'm going to try dynamic SQL using a Between clause, but not as far as
> > > *LOVAL and *HIVAL, and only including the columns I want to search in the
> > > Where clause. I'm not anticipating good results though.
> > >
> > > Question (finally): What's the best technique for an SQL-based search
> > where
> > > I return a result set from a search of multiple fields?
> > >
> > > Thanks for any ideas.
> > --
> > 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.