Results 1 to 8 of 8

Thread: MQT useless?

  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: MQT useless?

    Hello,

    does anyone has some experience with those Materialized Query Tables?
    I set one up in order to speed up access to a pretty large and frequently queried bunch of tables but it seems to have no effect.
    I wrote a program to test the stuff. It fires a set of 1000 Select queries a hundred times but the time measurements do not differ in relevant dimensions. I wonder if the DB2 actually uses the MQT. How can I test/recognize/prove whether it uses the original table(s) or the MQT?

    Thanks in advance.

    //edit: Obligatory Info stuff:
    DB21085I Instanz "DB2" verwendet "32" Bit und DB2-Codefreigabe "SQL09050" mit Aktualitäts-ID "03010107".
    Informationstoken: "DB2 v9.5.0.808", "s071001", "NT3295" und FixPak "0".
    Produkt ist in "C:\Programme\IBM\SQLLIB" mit DB2-Kopienamen "DB2COPY1" installiert.
    Product name: "DB2 Enterprise Server Edition"
    Product identifier: "db2ese"
    Version information: "9.5"
    Last edited by ahaubold; 12-17-09 at 08:38.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How is your MQT defined and what is the query you are using that you expect to use the MQT?

    Andy

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    The MQT Create Statement looks like this:
    CREATE TABLE Schema1.NewMqtTable AS (
    SELECT
    Schema1.BaseTable1.Column1 AS C1,
    Schema1.BaseTable1.Column2 AS C2,
    Schema1.BaseTable1.Column3 AS C3,
    Schema1.BaseTable1.Column4 AS C4,
    Schema1.BaseTable2.Column1 AS C5,
    Schema1.BaseTable3.Column1 AS C6,
    Schema1.BaseTable3.Column2 AS C7,
    Schema1.BaseTable4.Column1 AS C8,
    Schema1.BaseTable4.Column2 AS C9,
    Schema1.BaseTable5.Column1 AS C10,
    Schema1.BaseTable5.Column2 AS C11
    FROM Schema1.BaseTable1, Schema1.BaseTable2, Schema1.BaseTable3, Schema1.BaseTable4, Schema1.BaseTable5
    WHERE Schema1.BaseTable1.Column2 = Schema1.BaseTable2.Column1
    AND Schema1.BaseTable2.Column2 = Schema1.BaseTable3.Column1
    AND Schema1.BaseTable3.Column2 = Schema1.BaseTable4.Column1
    AND Schema1.BaseTable4.Column3 = 'some sample text'
    AND Schema1.BaseTable4.Column2 = Schema1.BaseTable5.Column1
    ) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM;
    Using "REFRESH IMMEDIATE" is not possible because the base tables have different indices and structure.
    The Select statements I use for performance testing are pretty much the same as the one used in the MQT's create- statement except that I add one or more other conditions to the WHERE clause.
    I looked on some other websites related to db2 and I think I made it wrong this way. I will try creating a single MQT for each of the base tables using "REFRESH IMMEDIATE" instead of trying to squeeze the whole lot out of 5 tables into one MQT.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you don't use REFRESH IMMEDIATE, you have to manually refresh the MQT. Did you do that? And what did you set the CURRENT REFRESH AGE special register to?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Oct 2009
    Posts
    6
    Oh, I did that.
    After creating the table I made a refresh and runstats :
    REFRESH TABLE Schema1.NewMqtTable;
    RUNSTATS ON TABLE Schema1.NewMqtTable;

    I did not touch the CURRENT REFRESH AGE. I see now, that I probably should have set it to "ANY"?

    //edit: typo.
    Last edited by ahaubold; 12-18-09 at 04:58.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I will try creating a single MQT for each of the base tables .....
    I thought it would be useless, except greatly reducing MQT's rows by specifying WHERE conditions and/or some kind of grouping.
    Looking into NewMqtTable, you simply joined tables without grouping.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The Select statements I use for performance testing are pretty much the same as the one used in the MQT's create- statement except that I add one or more other conditions to the WHERE clause.
    I want to confirm that you specified all WHERE conditions in the MQT plus one or more other conditions.
    Because, you wrote "pretty much the same" and I afraid there were some differences which profibitted the use of the MQT.

  8. #8
    Join Date
    Oct 2009
    Posts
    6
    I think I got it now.
    I did this after creating the MQT:
    REFRESH TABLE Schema1.NewMqtTable;
    RUNSTATS ON TABLE Schema1.NewMqtTable; (<-- is this of any use actually?)
    SET CURRENT REFRESH AGE = ANY;
    SET CURRENT QUERY OPTIMIZATION = 9;
    When I use a select statement which matches the one given at MQT-creation, the time needed to fullfil the request speeds up from ~5ms to <1ms.

    Thank you all for hints and advice.

    //edit: typo.
    //edit2:
    Correction: I do not know where the effect of the sped up request times came from. It seems impossible to reproduce it. So solution described above does not seem to be the answer to my problem.
    //edit3: Looks like it works in the end. I think I messed up the threading (by using multiple clients) when I wrote edit2.
    Last edited by ahaubold; 12-18-09 at 09:46.

Tags for this Thread

Posting Permissions

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