Archives of the TeradataForum
Message Posted: Tue, 28 Feb 2006 @ 08:35:36 GMT
First I am Sorry for the long mail .
I am facing with the performance problem when users are trying to access data . Initially we dint have any filters to the Users view which has been added recently to filter out records based on the value in Column Data_source_cd
I have got the target EDW table with the following structure .
CREATE SET TABLE account_event ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Event_Id DECIMAL(18,0) NOT NULL, Account_Num VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Account_Modifier_Num CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, Account_Event_Type_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, Start_Dt DATE FORMAT 'YYYY-MM-DD', End_Dt DATE FORMAT 'YYYY-MM-DD', Data_Source_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC, Record_Deleted_Flag BYTEINT NOT NULL DEFAULT 0 , BusinessDate DATE FORMAT 'YYYY-MM-DD', Ins_Txf_BatchID INTEGER, Upd_Txf_BatchID INTEGER) PRIMARY INDEX ( Event_Id );
This table has got 15million records
The table is accessed by a view V_Account_Event which is a one to one on table (Account_Event) without any filters.
REPLACE VIEW V_ACCOUNT_EVENT ( Event_Id , Account_Num , Account_Modifier_Num , Account_Event_Type_Cd , Start_Dt , End_Dt , Data_Source_Cd , Record_Deleted_Flag , BusinessDate , Ins_Txf_BatchID , Upd_Txf_BatchID ) AS LOCKING TABLE ACCOUNT_EVENT FOR ACCESS SELECT Event_Id , Account_Num , Account_Modifier_Num , Account_Event_Type_Cd , Start_Dt , End_Dt , Data_Source_Cd , Record_Deleted_Flag , BusinessDate , Ins_Txf_BatchID , Upd_Txf_BatchID FROM ACCOUNT_EVENT;
For the downstream datamarts to provide only the relevant data from the target EDW we have got a filter table which will hold only the required values
CREATE SET TABLE data_sources_filter ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( S_NO INTEGER, DATA_SOURCE_CD VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, DESCRIPTION VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC, START_DT DATE FORMAT 'YY/MM/DD', END_DT DATE FORMAT 'YY/MM/DD') PRIMARY INDEX ( DATA_SOURCE_CD );
This table has got only 23 records
The users access the data by the following view defn..
REPLACE VIEW VM_ACCOUNT_EVENT ( Event_Id ,Account_Num ,Account_Modifier_Num ,Account_Event_Type_Cd ) AS SELECT TGT.Event_Id ,TGT.Account_Num ,TGT.Account_Modifier_Num ,TGT.Account_Event_Type_Cd FROM V_ACCOUNT_EVENT TGT INNER JOIN DATA_SOURCES_FILTER B ON COALESCE(TRIM(TGT.DATA_SOURCE_CD),'') = COALESCE(TRIM(B.DATA_SOURCE_CD),'' ) AND TGT.End_Dt is null ;
The problem is when I run a query of Count(*) in the Target table directly it gives me the result in 3 mins.
Whereas When I execute the same using the VM_ACCOUNT_EVENT view to query it takes 20 mins to provide me the result.
Can you pls help me to find a way to correct this problem.
Sel count(*) from Account_Event ; Result in 3 mins
Explain plan when I query against the table
Sel count(*) from VM_ACCOUNT_EVENT ; Result in 18mins.
Explain plan when I query using the view
I have tried by creating Secondary Index on the table for Data_source_cd. This is also not working .
Any suggestions or way to solve this problem is very much appreciated.
Thanks in advance
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|