Slow query execution from a view
A user on IDS 9.4 found that selecting from a view (built with ANSI CROSS JOIN plus two LEFT OUTER JOINs over ~100k-row and ~10k-row tables) took about 12 seconds, while running the identical SELECT text directly returned immediately — apparently because the view was materialised into an implicit temp table instead of being folded into the query. Suggestions were to set IFX_FOLDVIEW 1 in onconfig (with a warning that it is unreliable/absent before IDS 10.00.xC5) and to avoid ANSI join syntax in the view. The Informix-style outer join syntax performed even worse, and the poster could not upgrade, so no working fix was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 9.4, Suse
I have view like that:
CREATE VIEW v_move_log (....
) AS
SELECT ....
FROM
(
(
trn t CROSS JOIN trn_kind k
) LEFT OUTER JOIN account d ON t.debit = d.key
) LEFT OUTER JOIN account c ON t.credit = c.key
trn ~100000 rows
trn_kind = 2 rows
account ~10000 rows
If I execute Select * From v_move_log, it takes ~12 secs
If I execute select string as it defined in the view creation sql:
select .... from
(
(
trn t CROSS JOIN trn_kind k
) LEFT OUTER JOIN account d ON t.debit = d.key
) LEFT OUTER JOIN account c ON t.credit = c.key
it gives response immediately.
Can I do something to decrease response time for query from a view?
Have you tried ,
IFX_FOLDVIEW 1
in onconfig?
Frank
On 4/5/07, ANDREW SVIKHNOUSHIN <san@inist.ru> wrote:
>
> IDS 9.4, Suse
>
> I have view like that:
>
> CREATE VIEW v_move_log (> .....
> ) AS
> SELECT ....
> FROM
> (
> (>
> trn t CROSS JOIN trn_kind k
> ) LEFT OUTER JOIN account d ON t.debit = d.key
> ) LEFT OUTER JOIN account c ON t.credit = c.key
>
> trn ~100000 rows
> trn_kind = 2 rows
> account ~10000 rows
>
> If I execute Select * From v_move_log, it takes ~12 secs
>
> If I execute select string as it defined in the view creation sql:
> select .... from
> (
> (>
> trn t CROSS JOIN trn_kind k
> ) LEFT OUTER JOIN account d ON t.debit = d.key
> ) LEFT OUTER JOIN account c ON t.credit = c.key
> it gives response immediately.
> Can I do something to decrease response time for query from a view?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Be careful with IFX_FOLDVIEW. I think it's not event in code of IDS 9.4.
With version 10.00.xC4 we got big problems with this parameter. Some
statements lead to
100% CPU and no further reaction of the IDS.
You should use this parameter starting with IDS 10.00.xC5, here it works fine.
Best regards, gerd
> Have you tried ,
>
> IFX_FOLDVIEW 1>
> in onconfig?
>
> Frank
>
> On 4/5/07, ANDREW SVIKHNOUSHIN <san@inist.ru> wrote:
> >
> > IDS 9.4, Suse
> >
> > I have view like that:
> >
> > CREATE VIEW v_move_log (> > .....
> > ) AS
> > SELECT ....
> > FROM
> > (
> > (> >
> > trn t CROSS JOIN trn_kind k
> > ) LEFT OUTER JOIN account d ON t.debit = d.key
> > ) LEFT OUTER JOIN account c ON t.credit = c.key
> >
> > trn ~100000 rows
> > trn_kind = 2 rows
> > account ~10000 rows
> >
> > If I execute Select * From v_move_log, it takes ~12 secs
> >
> > If I execute select string as it defined in the view creation sql:
> > select .... from
> > (
> > (> >
> > trn t CROSS JOIN trn_kind k
> > ) LEFT OUTER JOIN account d ON t.debit = d.key
> > ) LEFT OUTER JOIN account c ON t.credit = c.key
> > it gives response immediately.
> > Can I do something to decrease response time for query from a view?
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192
Thanks, I seem it can help me but I have very stupid customers, they don't want to upgrade to IDS 10
Andrew, How about NOT using ANSI(database) syntax if your customer does not go to IDS10? It should be fine if you just use generic join in your view definition, NOT ANSI in your case. Frank On 4/10/07, ANDREW SVIKHNOUSHIN <san@inist.ru> wrote: > > Thanks, I seem it can help me but I have very stupid customers, they don't > want to upgrade to IDS 10 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
When I used Informix extension outer syntax the result was even worse. I seem, when I used view, the implicit temp table created regadless of syntax (join). Without view the output rows are ready without any delay. And with view I have rows with other ordering rather than without view. Thank you for knowledge about IFX_FOLDVIEW, maybe I can convince customers of a need of upgrade.