Re: sql joins with or logic - performance

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.