Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
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. –  FreshPhilOfSO Dec 4 '13 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? –  sapatos Dec 5 '13 at 3:43
add comment

1 Answer

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.

share|improve this answer
    
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 :) –  sapatos Dec 18 '13 at 23:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.