Re: sql joins with or logic - performance

I agree the join with OR is beyond what I've ever coded or seen(I'm only the
performance advisor for this proj). Will see if can get the alternatives
tested to prove the point.
Jim Franz

----- Original Message ----- From: "Vern Hamberg" <vhamberg@xxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Monday, February 20, 2012 6:29 PM
Subject: Re: sql joins with or logic - performance

I think the issues come from doing record selection in JOIN criteria -
that can be a mess - not exactly sure what goes on, but it's bit me
badly at times.

I wouldn't go so far as Tom, to eliminate the JOIN syntax entirely,
however.

I do find the OR in JOIN criteria as done here to be just a little weird.

There IS a real possible performance killer - the LIKE predicate - if
your host value has a leading wildcard, you are doing a table scan -
period. Horrible!! Danger, danger, Will Robinson!!

But see what this does - it separates things by their function, either
for JOINing or for WHEREing - I made that up!

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

That's a really quick attempt - I would probably not use the 1 = 1 thing
= that is there to get "all locations" and "all types" - maybe a better
way would be to build 4 different statements, depending on whether a
location or type is specified - that would not be so ugly as I just
presented, and might actually work!

So the WHERE clause would be one of these -

where secuser = ? and parts.desc like?
where secuser = ? and secmast.loc = ? and parts.desc like ?
where secuser = ? and secmast.type = ? and parts.desc like ?
where secuser = ? and secmast.loc = ? and secmast.type = ? and
parts.desc like ?

Eh?
Vern

On 2/20/2012 4:25 PM, Tom E Stieger wrote:

I'm not sure if you can run the following test, but does visual explain
have different results for the following?

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



-Tom Stieger
IT Manager
California Fine Wire

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
franz400@xxxxxxxxxxxx
Sent: Monday, February 20, 2012 1:46 PM
To: midrange-l@xxxxxxxxxxxx
Subject: sql joins with or logic - performance

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