Results 1 to 4 of 4

Thread: Mqt

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Mqt

    Hi All,

    I am new to DB2, I have a requiment to tune the below mentioned query.


    select a11.SE_ID SE_ID,
    sum(a11.D41A_NET_CHRG_AM) WJXBFS1
    from GMAPS_SE_BOUNDS_QLY a11
    join VMUS00.GMAPS_PRODUCT a12
    on a11.GMAPS_PROD_CD = a12.GMAPS_PROD_CD
    join SE_CHARACTERISTICS_N a13
    on a11.SE_ID = a13.SE_ID and
    a11.SE_NO = a13.SE_NO and
    a11.SOURCE_SYS_ID = a13.SOURCE_SYS_ID
    join M0009_GMAPS_GENSIS_INDUS_SMRY_LVL_15 a14
    on a13.SE_INDUS_DS_CD = a14.SE_INDUS_DESC_CD
    where (a13.CTRY_ID in ('392')
    and a14.GENESIS_ACCT_CD in ('0010-S/E DISCOUNT BUSINESS ', '0020-AMEX DISCOUNT BUSINESS ')
    and a11.RPT_PER_DT in ('2008-03-31')
    and a12.GMAPS_SUPER_GRP_CD in ('0001'))

    group by a11.SE_ID


    I am repeatedly getting to my Database same kind of query with small filter changes marked in Bold.
    I heard that there is MQT, which will improve the preformce of query. but I am not sure how to approach MQT for this query. Please help me to tune the above mentioned query.

    Thanks in advace

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Are you using parameter markers on these queries, so that the SQL and access path are cached? Have you explained the SQL to see what the access path looks like and to confirm you are using proper indexes when accessing these tables, tried index advisor? These are a couple of the many items you should look at prior to deciding on a MQT.
    As far as the MQT goes, you would want to create it with the same SQL, minus the conditions that are apt to change with each execution. In other words leave out the predicates you have marked in bold.
    One last item I wanted to mention was you should really keep the extra items on the joins of the different tables in the ON clause. It won't make much difference in this query due to all tables are INNER JOINed to each other, but if you were to throw in a LEFT OUTER JOIN, you would be negating the use of an OUTER JOIN. As an example, here is your query keeping everything where it belongs ( as I said not very important with inner joins, but very important with outer joins):
    select a11.SE_ID SE_ID
    ,sum(a11.D41A_NET_CHRG_AM) WJXBFS1
    from GMAPS_SE_BOUNDS_QLY a11

    join VMUS00.GMAPS_PRODUCT a12
    on a11.GMAPS_PROD_CD = a12.GMAPS_PROD_CD
    and a12.GMAPS_SUPER_GRP_CD in ('0001')

    join SE_CHARACTERISTICS_N a13
    on a11.SE_ID = a13.SE_ID
    and a11.SE_NO = a13.SE_NO
    and a11.SOURCE_SYS_ID = a13.SOURCE_SYS_ID
    and a13.CTRY_ID in ('392')

    join M0009_GMAPS_GENSIS_INDUS_SMRY_LVL_15 a14
    on a13.SE_INDUS_DS_CD = a14.SE_INDUS_DESC_CD
    and a14.GENESIS_ACCT_CD in ('0010-S/E DISCOUNT BUSINESS ', '0020-AMEX DISCOUNT BUSINESS ')

    where a11.RPT_PER_DT in ('2008-03-31')
    group by a11.SE_ID

    Dave

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Hi Dave,

    Thanks for your valuable suggestions, So as per MQT creation will it work in my case?

    Nag

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I don't know. You'll have to tell us if it works or not.
    Dave

Posting Permissions

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