MQT useless?

ahaubold · #1 (**permalink**) 12-17-09, 07:31

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"

ARWinner · #2 (**permalink**) 12-17-09, 08:44

How is your MQT defined and what is the query you are using that you expect to use the MQT?

Andy

ahaubold · #3 (**permalink**) 12-18-09, 02:10

The MQT Create Statement looks like this:

Quote:

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.

stolze · #4 (**permalink**) 12-18-09, 03:09

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?

ahaubold · #5 (**permalink**) 12-18-09, 03:48

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.

tonkuma · #6 (**permalink**) 12-18-09, 05:11

Quote:

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.

tonkuma · #7 (**permalink**) 12-18-09, 05:16

Quote:

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.

ahaubold · #8 (**permalink**) 12-18-09, 05:28

I think I got it now.
I did this after creating the MQT:

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech