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