4

I'm comparing performance on a series of tables involved in insert/updates/deletes.

These tables are about to become the source tables for a Materialised Query Table so I thought getting some statistics on the performance impact of the MQT would be a good idea.

Following like for like loads with and without the MQT I can see that there is a 18% performance hit due to the MQT.

Question is, is 18% a normal figure? Is it very high? I'm not sure as I have no basis for comparison. At the end of the day the business teams will make the final decision if it's acceptable however some feedback on whether this seems normal would be useful.

On the MQT I've put an index on each of the source table primary key columns that have to exist on the MQT, I wasn't sure if that was necessary but figured as the MQT needs the source tables Primary key in its definition then an index on those columns must make sense. I've also added some further indexes to improve queries etc.

Below is my MQT and indexes.

create table CRODSDBA.M_SEARCH_SYNC as (SELECT CRODS.PARTY_GRP_ROLE.PARTY_GRP_ID, CRODS.PARTY_GRP_ROLE.PARTY_LINK_ID, CRODS.PARTY_GRP_ROLE.PARTY_GRP_ROLE_ID, CRODS.PARTY_GRP_ROLE.VERSION PGRV, CRODS.NAME.NAME_ID, CRODS.NAME.VERSION NV, CRODS.NAME.SURNAME, CRODS.NAME.FIRST_NAME, CRODS.NAME.MIDDLE_NAME_1, CRODS.NAME.MIDDLE_NAME_2, CRODS.NAME.MIDDLE_NAME_3, CRODS.NAME.TITLE_CDE, CRODS.NAME.DATE_OF_BIRTH, CRODS.NAME.GENDER_CDE, CRODS.NAME.FLL_NM_SRCH_KEY_TX, CRODS.NAME.SURNM_SRCH_KEY_TX, CRODS.NAME.CMPNY_NM_TYP_CDE, CRODS.ADDRESS.ADDRESS_id, CRODS.ADDRESS.VERSION AV, CRODS.ADDRESS.ADDR_SEARCH_KEY_TX, CRODS.ADDRESS.ADDRESS_FORMAT_CDE, CRODS.ADDRESS.STREET_NO, CRODS.ADDRESS.STREETNAME, CRODS.ADDRESS.STREET_TYPE, CRODS.ADDRESS.STREET_DIRECTION, CRODS.ADDRESS.SUBDWELLING_1_TYPE, CRODS.ADDRESS.SUBDWELLING_1_NO, CRODS.ADDRESS.SUBDWELLING_2_TYPE, CRODS.ADDRESS.SUBDWELLING_2_NO, CRODS.ADDRESS.POSTAL_TYPE, CRODS.ADDRESS.POSTAL_NO, CRODS.ADDRESS.SUBURB, CRODS.ADDRESS.POSTCODE, CRODS.ADDRESS.STATE, CRODS.ADDRESS.COUNTRY_CDE, CRODS.ADDRESS.ADDRESS_DETAIL_1, CRODS.ADDRESS.ADDRESS_DETAIL_2, CRODS.ADDRESS.ADDRESS_DETAIL_3, CRODS.ADDRESS_XREF.ADDRESS_XREF_ID, CRODS.ADDRESS_XREF.VERSION AXV, CRODS.ADDRESS_XREF.ATTENTION, CRODS.PARTY.party_link_id next_plid, CRODS.PARTY.VERSION PV, CRODS.PARTY.SRC_SYS_NAME_CDE, CRODS.PARTY.SRC_SYS_PARTY_ID, CRODS.PARTY.LEGAL_ENTITY_CDE, CRODS.PARTY.PARTY_TYPE_CDE, CRODS.PARTY_GROUP.PARTY_GRP_ID PGPGID, CRODS.PARTY_GROUP.VERSION PGV, CRODS.PARTY_GROUP.PARTY_GRP_TYPE_CDE FROM CRODS.NAME, CRODS.ADDRESS_XREF, CRODS.ADDRESS, CRODS.PARTY, CRODS.PARTY_GRP_ROLE, CRODS.PARTY_GROUP WHERE CRODS.ADDRESS_XREF.REF_TYPE_CDE = 'PARTY' AND CRODS.ADDRESS_XREF.VERSION = 99999999 AND CRODS.ADDRESS_XREF.END_DATE = DATE('9999-12-31') AND CRODS.ADDRESS.ADDRESS_ID = CRODS.ADDRESS_XREF.ADDRESS_ID AND CRODS.ADDRESS.VERSION = 99999999 AND CRODS.ADDRESS.END_DATE = DATE('9999-12-31') AND CRODS.PARTY_GRP_ROLE.PARTY_LINK_ID = CRODS.ADDRESS_XREF.REF_ID AND CRODS.PARTY_GRP_ROLE.VERSION = 99999999 AND CRODS.PARTY_GRP_ROLE.END_DATE = DATE('9999-12-31') AND CRODS.PARTY_GRP_ROLE.PARTY_GRP_ID = CRODS.PARTY_GROUP.PARTY_GRP_ID AND CRODS.PARTY_GROUP.PARTY_GRP_ID = CRODS.PARTY_GRP_ROLE.PARTY_GRP_ID AND CRODS.PARTY_GRP_ROLE.PRIMARY_FLAG_CDE = 'Y' AND CRODS.PARTY_GROUP.PARTY_GRP_TYPE_CDE IN ('CUSTOMER', 'USER', 'AGENT') AND CRODS.PARTY_GROUP.VERSION = 99999999 AND CRODS.PARTY_GROUP.END_DATE = DATE('9999-12-31') AND CRODS.NAME.PARTY_LINK_ID = CRODS.PARTY_GRP_ROLE.PARTY_LINK_ID AND CRODS.NAME.VERSION = 99999999 AND CRODS.NAME.END_DATE = DATE('9999-12-31') AND CRODS.PARTY.PARTY_LINK_ID = CRODS.PARTY_GRP_ROLE.PARTY_LINK_ID AND CRODS.PARTY.VERSION = 99999999 AND CRODS.PARTY.END_DATE = DATE('9999-12-31') AND CRODS.PARTY.PARTY_STATUS_CDE <> 'RESTRICTED' ) data initially deferred refresh immediate; set integrity for CRODSDBA.M_SEARCH_SYNC immediate checked not incremental; --BUILD THE INDEXES DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_1; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_1 ON CRODSDBA.M_SEARCH_SYNC (PARTY_GRP_ROLE_ID ASC, PGRV DESC) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_2; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_2 ON CRODSDBA.M_SEARCH_SYNC (NAME_ID ASC, NV DESC) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_3; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_3 --PROCS USING: SEARCH_FULL_NAME_3_PERF, SEARCH_FULL_NAME_2_PERF, SEARCH_FULL_NAME_4_PERF, SEARCH_FULL_NAME_2_PERF ON CRODSDBA.M_SEARCH_SYNC ( FLL_NM_SRCH_KEY_TX ASC, DATE_OF_BIRTH ASC , SUBURB ASC, POSTCODE ASC, LEGAL_ENTITY_CDE, GENDER_CDE ASC, PARTY_GRP_TYPE_CDE ASC, PARTY_TYPE_CDE ASC ) CLUSTER PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_4; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_4 --PROCS USING: SEARCH_COMP_NAME_PERF.FILTER_EXACT ON CRODSDBA.M_SEARCH_SYNC (SURNAME ASC, POSTCODE, PARTY_GRP_TYPE_CDE, PARTY_TYPE_CDE ) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_5; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_5 ON CRODSDBA.M_SEARCH_SYNC (ADDRESS_ID ASC, AV DESC) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_6; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_6 --PROCS USING: SEARCH_COMP_NAME_PERF.FILTER_STD ON CRODSDBA.M_SEARCH_SYNC (DATE_OF_BIRTH ASC, SURNM_SRCH_KEY_TX ASC, SUBURB ASC , POSTCODE ASC , GENDER_CDE ASC, PARTY_TYPE_CDE ) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_7; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_7 ON CRODSDBA.M_SEARCH_SYNC (ADDRESS_XREF_ID ASC, AXV DESC) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_8; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_8 ON CRODSDBA.M_SEARCH_SYNC (next_plid ASC, PV DESC, LEGAL_ENTITY_CDE ASC, PARTY_TYPE_CDE ASC) PCTFREE 10 ALLOW REVERSE SCANS; DROP INDEX CRODSDBA.IDX_M_SEARCH_SYNC_9; CREATE INDEX CRODSDBA.IDX_M_SEARCH_SYNC_9 ON CRODSDBA.M_SEARCH_SYNC (PARTY_GRP_ID ASC, PGV DESC ) PCTFREE 10 ALLOW REVERSE SCANS; --reorg table reorg table crodsdba.M_SEARCH_SYNC;
Improve this question
3
  • At the end of the day, every extra index means another write to a disk (be it SSD or spinny). Adding a materialised view means another write, and possibly more reads (depending on the MV). The overhead doesn't sound unreasonable. Commented Dec 4, 2013 at 23:13
  • Thanks Phil - are you able to advise on the necessity of the indexes on the columns related to the primary keys on the source tables? I've checked my SQL explain plans and they don't use them, I'd originally added them in case they were needed to speed up the synching process on the MQT with the source tables (MQT is live). Perhaps they're not needed? Commented Dec 5, 2013 at 3:43
  • What version of db2 are you using? Commented Jan 4, 2015 at 15:01

1 Answer 1

Reset to default
-1

We used MQTs in our application, but decided to throw them out. They are all trouble. They are a nightmare to maintain, to maintain underlying tables, they are slow to refresh, endless limits on SQL that you use to refresh them ,... At the end we wrote a stored procedure that uses temporary tables instead on MQTs and write the data to a regular table. We used MQt so maintain data for out web page. Using MQTs it took us 30-40 minutes to refresh, using temp tables it takes us 40 - 100 sec for the same task. In both cases resulting data is the same.

Improve this answer
2
  • thanks for the advice Kovica - I found that the MQT was very slow until I put indexes on all the source table primary keys that are required in the select statement of the definition. Once the indexes were on it was pretty quick. For transactions that were hitting the source tables I was seeing between 5-18% degradation. I worked out our performance savings vs update degrades was still in our benefit so we're sticking with them for the time being. (especially as my boss says we have to :) Commented Dec 18, 2013 at 23:45
  • @kovika - at the risk of a flame war, I would say that MQTs are not trouble. They just need to be used in the right place. The right tool for the right job. I wouldn't use them just anywhere. But they have their uses. I have used an MQT in combination with a view to successfully assist with moving data back and forth between two systems with federation. It was very handy there. So, again, the right place for it is handy. But there are downsides to them and in the wrong spot they can be trouble. Commented Dec 6, 2016 at 13:40

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.