Performance degradation with view and "left outer join" vs. "from x, outer y"
Jeff found that a query joining a table to a view containing expensive user-defined functions ran fast with Informix's old "FROM c, OUTER v" syntax but as slowly as a full view scan with ANSI "LEFT OUTER JOIN ... WHERE custid = ...", a problem because Hibernate always generates the ANSI form. Art Kagel explained that ANSI rules force WHERE filters to be applied post-join, often materialising the view first, and suggested moving the filter into the ON clause; Jeff reported that returned all rows unfiltered and was equally slow. Others suggested updating statistics, trying 11.50.xC4, opening a PMR with a test case, and fixing it on the Hibernate side. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a view based on a simple query that performs some custom
functions.
create view my_view (custid, col1, col2) asselect
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
Jeff 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
>
>
Ideally it should not make a difference...
You could post the query plans and the exact version (fixpack included) or open
a PMR.
If I'm not missing something it looks like a bug..
Could you try to rerun update statistics?
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
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.
This means that to process the query the way you have arranged it the engine
MAY have to perform the entire VIEW query doing what amounts executing two
functions and saving a row for every row in the customer table into a temp
table. Then the engine must read either the customer table or this temp
table and join every row to every row of the customer table (in your trivial
example here, BTW, the OUTER is not needed) and saving the results into yet
another temp table. Then the engine must read back the joined temp results
and apply the custid filter to return data for the one single customer.
The second problem (instantiating views unneccessarily) is solvable much of
the time and IB it was fixed in IDS r11.50xC3 or xC4. The first problem,
post-join filters in the where clause, however, is not something that the
optimizer is permitted to correct.
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";
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)
IIUG Board of Directors (art@iiug.org)
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@gmail.com> 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
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
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@iiug.org <mailto:art@iiug.org>)
>
> 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@gmail.com
> <mailto:jlar310@gmail.com>> 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
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org <mailto:Informix-list@iiug.org>
> http://www.iiug.org/mailman/listinfo/informix-list
>
>
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@iiug.org <mailto:art@iiug.org>)
>>
>> 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@gmail.com
>> <mailto:jlar310@gmail.com>> 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...
I agree, the query I proposed should have returned identical data to the
original. If it is not this should be reported to IBM as a bug and a PMR
opened.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
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 Wed, May 6, 2009 at 5:57 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> 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@iiug.org <mailto:art@iiug.org>)
> >>
> >> 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@gmail.com
> >> <mailto:jlar310@gmail.com>> 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...
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>