On Monday, February 20, 2012 1:46 PM franz400 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
I suspect the use of the variables is causing implementation for a
/cross join/ to resolve matching rows across any value of "loc" or
"type" in a reusable query. A rewrite of the query as a UNION may be
desirable to help to show why:
Select aa,bb,cc,dd
from parts inner join secmast
on secuser = ?
and (secmast.loc = parts.loc )
and (secmast.type = parts.type)
where parts.desc like ?
union
Select aa,bb,cc,dd
from parts inner join secmast
on secuser = ?
and secmast.loc = ?
and secmast.type = ?
where parts.desc like ?
The second query in the union is [still] a cross join; i.e. an inner
join with no correlation betwixt.
Is "or" logic in a join an issue? Their app allowing user to
select a single loc or "all" locs, same for type.
The above UNION query should help to show why the described-as
"allowing user to select ..." logic for the query is probably not what
is really going to be the effect; i.e. the query does not reflect the
logic alluded as desired for the app. best I can infer.
The following query enables the NULL value to adjust the join logic
to better reflect the stated intent of the app, but if neither value in
the IFNULL functions is NULL, then the cross join is again in effect.
Select aa,bb,cc,dd
from parts inner join secmast
on secuser = ?
and secmast.loc = ifnull(?, parts.loc )
and secmast.type = ifnull(?, parts.type)
where parts.desc like ?
However, I wonder if that is even really what is desired. I wonder
if instead the intent is to effect the INNER JOIN for rows matching
across the "loc" and "type", but to optionally include only specific
"loc" and\or "type" values when either or both are specified:
Select aa,bb,cc,dd
from parts inner join secmast
on secuser = ?
and secmast.loc = parts.loc
and secmast.type = parts.type
where secmast.loc = ifnull(?, secmast.loc )
and secmast.type = ifnull(?, secmast.type)
and parts.desc like ?
Notes: The parameter markers in the IFNULL must be placed in a CAST
function to establish the type. Seems the query could easily be
embedded using host variables instead?
Regards, Chuck
midrange.com
