Re: sql joins with or logic - performance

I don't believe the SQL is going to give you what you think it is...

I'd do something like what you describe like so...

Select aa,bb,cc,dd
from parts inner join secmast
on secmast.loc = parts.loc
and secmast.type = parts.type
where
parts.desc like ?
and secuser= ?
and secmast.loc in (?,' ' )
and secmast.type in (?,' ' )

This assumes that "all locs" and "all types" are passed as blanks

In most cases, it's better to keep your WHERE criteria separate from
the JOIN criteria.

Also if PARTS is a smaller table than SECMAST, you might be better
served by having the WHERE look like:
and parts.loc in (?,' ' )
and parts.type in (?,' ' )

HTH,
Charles


On Mon, Feb 20, 2012 at 4:46 PM, <franz400@xxxxxxxxxxxx> wrote:

SQL requests from .net app where joins have "or" logic

Select aa,bb,cc,dd from parts inner join secmast on secuser= ?
and (secmast.loc = parts.loc or secmast.loc = ?)
and (secmast.type = parts.type or secmast.type = ?)
where parts.desc like ?

Visual Explain is showing over 1,000,000 ms in Final Select
v6r1

Is "or" logic in a join an issue? Their app allowing
user to select a single loc or "all" locs, same for type.
Jim Franz


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