Re: Performance degradation with view and "left outer join" vs. "from x, outer y"

Jeff wrote:
Art Kagel wrote:
Jeff, You should have come to the IIUG Conference. In my session, "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. In addition, some of the ANSI JOIN semantics require that views in the FROM clause be instantiated (ie saved to a temp file) before joining under many conditions.

Good to know, but tell it to the people that wrote Hibernate. This is generated SQL.

<snip>

Try this equivalent query which moves the filter in to the ON condition of the OUTER JOIN clause:

select c.custid, v.col1, v.col2
from customer c
left outer join my_view v on c.custid = v.custid and c.custid = "AB1234"
;

Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. And it took just as long as the query with WHERE c.custid = ...

BTW, this is 11.50.UC3 -- I'm downloading UC4 as I write.

If that doesn't do it, I may have to wrap the entire customer table in a view that makes it unnecessary for Hibernate to perform the join.

Jeff



It is VERY rare for me to write any ANSI '92 style query with any WHERE clause at all unless I have to filter on conditions that just can't be put into the ON clause (outer_table_column IS NULL or IS NOT NULL is one such).

Art

Art S. Kagel
Oninit (www.oninit.com <http://www.oninit.com>)
IIUG Board of Directors (art@xxxxxxxx <mailto:art@xxxxxxxx>)

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.



On Tue, May 5, 2009 at 5:26 PM, Jeff <jlar310@xxxxxxxxx <mailto:jlar310@xxxxxxxxx>> wrote:

I have a view based on a simple query that performs some custom
functions.

create view my_view (custid, col1, col2) as
select
c.custid, my_func_1(c.custid), my_func_2(c.custid)
from customer c;

Now if I use the informix extended outer join syntax:

select c.custid, v.col1, v.col2
from customer c, outer my_view v
where c.custid = "AB1234"
and c.custid = v.custid;

then everything is hunky dory -- the query returns a single row
in virtually no time at all. This is how have always written my SQL.

But if you use 'left outer join' syntax:

select c.custid, v.col1, v.col2
from customer c
left outer join my_view v on c.custid = v.custid
where c.custid = "AB1234";

Then the query still returns the same data, but it takes as long
as if I had done "select * from my_view" which has a heavy price
due to the user defined functions.

The problem is that I am trying to use Hibernate which insists on
generating the SQL using 'left outer join'.

Why is the performance dependent on the syntax used? Shouldn't
the query optimizer arrive at the same result regardless the
syntax flavor? Is there some other functional difference between
the two syntax flavors that I am unaware of.

You will notice that an outer join is not necessary and this
should be an inner join, but Hibernate always uses outer for
secondary table data. Again, the SQL is being written for me by
Hibernate. I can't change that. When using "inner join" syntax,
the query is as fast as expected.

IDS v11.5

Jeff

I find this issue very interesting for several reasons:
1- I was not really aware of what Art wrote. And that it very interesting.
2- Given the result you told us about on Art's suggestion, again if I'm not missing something - as I was on first post - , this doesn't look good. A PMR with a test case would be nice.
3- When you say "tell it to the people who wrote Hibernate", may I ask you if you did? Don't get me wrong but in about a week it's the third situation I "see " where people are trying to get workarounds in the database for things that Hibernate does and supposedly shouldn't. Nothing wrong with implementing workarounds, but the solution should be closer to the cause :)

Please tell us about your tests in UC4. And if possible show us the query plans and if possible a simple test case would be nice.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
.

Relevant Pages

  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Better "Join" vs "Where" clause?
    ... There is an extreme case, famous, in MS SQL Server only though, and the ... example is in BOL I think, where it uses a full outer join to make what is ... you can even have a ON clause on a pure constant. ... ever done comparing equivalent implicit and explicit joins with Jet ...
    (microsoft.public.access.queries)
  • Re: SQL Syntax
    ... I am coding SQL statements for some time but I never came across an in depth ... > LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK ... > note that I've put the condition into the ON clause. ... >> LEFT OUTER JOIN TableB etc. ...
    (microsoft.public.sqlserver.datamining)
  • Re: searching for yoda - a developers tale
    ... > include the JOIN clauseand not have any typos. ... not the particular SQL bit in question. ... I'd put Ant lower on the priority list and think about ... learning it when you have Hibernate under ...
    (comp.lang.java.programmer)
Loading