b --> c (each 1:n) All join-columns have a single index, some unique, some duplicate. We compared the following two SELECT's which have the same results. Query 1: SELECT a.* FROM a,b,c WHERE ... Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; CREATE INDEX idx01 ON temp01(join_col_to_a); UPDATE STATISTICS FOR TABLE temp01; SELECT a.* FROM a, temp01 WHERE ..... Query 1 takes 15 seconds Query 2 takes 1 second. (all 4 Statements!!) This is really confusing. We executed some UPDATE STAISTICS on these tables, and the optimizer seems to go the right way in both queries. However, Query1 is much more elegant and I expected, that Query1 should not be a problem for the Database-Server. I made the same test under IDS 9.21 and had (nearly) the same effect. Does anyone know the reason for this behaviour? Did I miss any important ONCONFIG-Setting? Thanks in advance! bye Markus [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Markus [ip] => mb@worxbox.com [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> b --> c (each 1:n) > All join-columns have a single index, some unique, some duplicate. > We compared the following two SELECT's which have the same results. > > Query 1: SELECT a.* FROM a,b,c WHERE ... > > Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; > CREATE INDEX idx01 ON temp01(join_col_to_a); > UPDATE STATISTICS FOR TABLE temp01; > SELECT a.* FROM a, temp01 WHERE ..... > > > Query 1 takes 15 seconds > Query 2 takes 1 second. (all 4 Statements!!) > > This is really confusing. We executed some UPDATE STAISTICS on these > tables, > and the optimizer seems to go the right way in both queries. However, > Query1 > is much more elegant and I expected, that Query1 should not be a problem > for > the Database-Server. > > I made the same test under IDS 9.21 and had (nearly) the same effect. > > Does anyone know the reason for this behaviour? Did I miss any important > ONCONFIG-Setting? > > Thanks in advance! > > bye > Markus > > > > >[/ref] sending to informix-list [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Alexey [ip] => alexeis@grandvi [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> b --> c (each 1:n) > > All join-columns have a single index, some unique, some duplicate. > > We compared the following two SELECT's which have the same results. > > > > Query 1: SELECT a.* FROM a,b,c WHERE ... > > > > Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; > > CREATE INDEX idx01 ON temp01(join_col_to_a); > > UPDATE STATISTICS FOR TABLE temp01; > > SELECT a.* FROM a, temp01 WHERE ..... > > > > > > Query 1 takes 15 seconds > > Query 2 takes 1 second. (all 4 Statements!!) > > > > This is really confusing. We executed some UPDATE STAISTICS on these > > tables, > > and the optimizer seems to go the right way in both queries. However, > > Query1 > > is much more elegant and I expected, that Query1 should not be a problem > > for > > the Database-Server. > > > > I made the same test under IDS 9.21 and had (nearly) the same effect. > > > > Does anyone know the reason for this behaviour? Did I miss any important > > ONCONFIG-Setting? > > > > Thanks in advance! > > > > bye > > Markus > > > > > > > > > >[/ref][/ref] sending to informix-list [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Alexey [ip] => alexeis@grandvi [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> b > --> c (each 1:n) > All join-columns have a single index, some unique, some duplicate. We > compared the following two SELECT's which have the same results. > > Query 1: SELECT a.* FROM a,b,c WHERE ... > > Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; > CREATE INDEX idx01 ON temp01(join_col_to_a); UPDATE STATISTICS > FOR TABLE temp01; > SELECT a.* FROM a, temp01 WHERE ..... > > > Query 1 takes 15 seconds > Query 2 takes 1 second. (all 4 Statements!!) > > This is really confusing. We executed some UPDATE STAISTICS on these tables, > and the optimizer seems to go the right way in both queries. However, Query1 > is much more elegant and I expected, that Query1 should not be a problem for > the Database-Server. > > I made the same test under IDS 9.21 and had (nearly) the same effect. > > Does anyone know the reason for this behaviour? Did I miss any important > ONCONFIG-Setting? > > Thanks in advance! > > bye > Markus[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Art [ip] => kagel@bloomberg [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> b --> c (each 1:n) > All join-columns have a single index, some unique, some duplicate. > We compared the following two SELECT's which have the same results. > > Query 1: SELECT a.* FROM a,b,c WHERE ... > > Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; > CREATE INDEX idx01 ON temp01(join_col_to_a); > UPDATE STATISTICS FOR TABLE temp01; > SELECT a.* FROM a, temp01 WHERE ..... > > > Query 1 takes 15 seconds > Query 2 takes 1 second. (all 4 Statements!!) > > This is really confusing. We executed some UPDATE STAISTICS on these tables, > and the optimizer seems to go the right way in both queries. However, Query1 > is much more elegant and I expected, that Query1 should not be a problem for > the Database-Server. > > I made the same test under IDS 9.21 and had (nearly) the same effect. > > Does anyone know the reason for this behaviour? Did I miss any important > ONCONFIG-Setting? > > Thanks in advance! > > bye > Markus[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Andy [ip] => andykent.bristo [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 5 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Strange Performancetuning - Informix

Strange Performancetuning - Informix

IDS 9.4 (WE) on RH9 Linux 2.4.20 Hi all, we found a very strange Performance-Tuning of a simple SELECT-Statement, and now, we fear to have to rewrite a lot of application-code in order to tune our SQL-Statements: We have 3 tables (a,b,c), which are joined each by one join-column: a --> b --> c (each 1:n) All join-columns have a single index, some unique, some duplicate. We compared the following two SELECT's which have the same results. Query 1: SELECT a.* FROM a,b,c WHERE ... Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01; CREATE INDEX idx01 ...

  1. #1

    Default Strange Performancetuning

    IDS 9.4 (WE) on RH9 Linux 2.4.20

    Hi all,

    we found a very strange Performance-Tuning of a simple SELECT-Statement, and
    now, we fear to have to rewrite a lot of application-code in order to tune
    our SQL-Statements:

    We have 3 tables (a,b,c), which are joined each by one join-column:
    a --> b --> c (each 1:n)
    All join-columns have a single index, some unique, some duplicate.
    We compared the following two SELECT's which have the same results.

    Query 1: SELECT a.* FROM a,b,c WHERE ...

    Query 2: SELECT b.join_col_to_a from b, c WHERE ... INTO TEMP temp01;
    CREATE INDEX idx01 ON temp01(join_col_to_a);
    UPDATE STATISTICS FOR TABLE temp01;
    SELECT a.* FROM a, temp01 WHERE .....


    Query 1 takes 15 seconds
    Query 2 takes 1 second. (all 4 Statements!!)

    This is really confusing. We executed some UPDATE STAISTICS on these tables,
    and the optimizer seems to go the right way in both queries. However, Query1
    is much more elegant and I expected, that Query1 should not be a problem for
    the Database-Server.

    I made the same test under IDS 9.21 and had (nearly) the same effect.

    Does anyone know the reason for this behaviour? Did I miss any important
    ONCONFIG-Setting?

    Thanks in advance!

    bye
    Markus







    Markus Guest

  2. #2

    Default RE: Strange Performancetuning


    Markus,

    Yes, this might happen, and it might be not related
    to any optimizer bug.

    Consider the following scenario:
    Join results for tables b and c might return very few
    rows because filters, applied to each table, are not
    restrictive against each table alone but become restrictive
    when they work together.
    In that case, it is much more efficient to join B & C first
    and then join the resulting table to A.

    Database optimizer knows nothing about this possible
    high selectivity of b<->c join, because there are no
    cross-distributions...

    As a result, optimizer might choose a different join order:
    instead of joining b & c first, it takes A as a primary
    table for join and then picks rows from B and C using indexes
    and applying proper filters AFTER fetching rows from B & C.

    This is a case, when clever developer might make a big
    favor to the database server by creating temporary tables
    or by specifying the join order explicitly with optimizer directives.

    I believe that there are scenario's when the database
    server is unable to construct the optimal plan because it doesn't
    have the information about the column cross-distributions
    that the developer might have in his head.

    ------------------------------------------
    Alexey Sonkin

     

    sending to informix-list
    Alexey Guest

  3. #3

    Default FW: Strange Performancetuning


    Marcus,

    I think I have an idea what could happen.

    If C<->B join returns considerable(>10000) number of rows,
    then Your intermediate index build prevents the server
    from sequential scan on the temporary (C <-> B) join result,
    and join A <-> (C <-> B) becomes more efficient.

    In Informix doentation, they mention situations when
    the server might create an automatic index on temporary table.
    I've never seen that. Only hash joins.

    This is why 'join order' directives and explicit 'temporary table'
    creation are not equivalent in Your case.

    Try 'temp table' without index. I hope it will be 15 sec

    ------------------------------------------
    Alexey Sonkin

     
    > tune [/ref]


    sending to informix-list
    Alexey Guest

  4. #4

    Default Re: Strange Performancetuning

    On Thu, 01 Apr 2004 10:35:38 -0500, Markus Bschorer wrote:

    Alexey hit it pretty well. Just want to add the elegant version of your 2nd
    solution:

    select a.*
    from a
    where ...
    and a.join_to_b_col in (
    select b.join_to_a_col
    from b,c
    where ...);

    Or you might try an EXISTS clause instead of the IN to bind the sub-query.
    Might even be sub-second response.

    Art S. Kagel

     
    Art Guest

  5. #5

    Default Re: Strange Performancetuning

    I broadly agree with the other two, but would suggest there must be
    some filter criteria you didn't tell us or some missing join index
    that means it makes such a difference what order the tables are
    processed in. Or maybe your filter or join column data has skewed
    values and should have UPDATE STATISTICS MEDIUM run on it.

    One handy technique is to force the order you believe to be best using
    optimiser directives ("SELECT --+ ORDERED"), run it through EXPLAIN
    and see if it does an autoindex or hash join or something else
    expensive along the way. That'll be where to add an index.

    Andy



    "Markus Bschorer" <com> wrote in message news:<c4hcpo$qed$01$t-online.com>... 
    Andy Guest

Similar Threads

  1. strange?
    By sbitcorp in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: February 16th, 01:18 AM
  2. Strange strange...can't import bmp?
    By design in forum Macromedia Director Basics
    Replies: 3
    Last Post: September 26th, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •