Archives of the TeradataForum

Message Posted: Mon, 03 Nov 2003 @ 12:43:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Zheng

We have been using Bobj (Business Objects) for quite some time. Here are some things that we have done to improve performance.

First and foremost... Indexing, Indexing, Indexing... The Primary Index is the most important thing in Teradata. If you go about a Teradata warehouse using and Oracle thought process you will not achieve the performance that you require...

The entire Primary Index must be covered in order for it to be used. I am not sure if you are doing thing this way so please excuse me if I am talking out of turn... I have worked with many Oracle implementers on Teradata installations and see this time and time again...

Bobj does work well with Teradata and you can achieve sub second response times... Check your current Primary Indexing for your Bobj Application...

I use this script to give me a summary for all the indexes that I am reviewing.

select  di.databasename, di.tablename, di.indexname, di.indextype,
di.uniqueflag, max(di.columnposition) nbr_cols, SUM(cast(t.currentperm
as decimal(18,0))/(1024*1024)) perm_mb,
        100*((cast(max(t.currentperm ) as decimal(18,3)) -
        cast(ave(t.currentperm ) as
decimal(18,3))))/cast(max(t.currentperm)
as decimal(18,3)) as tableskew,
max (case when columnposition = 1 then columnname else '' end)  col1,
max (case when columnposition = 2 then columnname else '' end)  col2,
max (case when columnposition = 3 then columnname else '' end)  col3,
max (case when columnposition = 4 then columnname else '' end)  col4,
max (case when columnposition = 5 then columnname else '' end)  col5,
max (case when columnposition = 6 then columnname else '' end)  col6,
max (case when columnposition = 7 then columnname else '' end)  col7,
max (case when columnposition = 8 then columnname else '' end)  col8,
max (case when columnposition = 9 then columnname else '' end)  col9
from dbc.indices di, dbc.tablesize t  where  --  di.databasename in
('live_data','temp_data') and  -- generally want to see particular
databases, not everything on the server di.databasename = t.databasename
and di.tablename = t.tablename
group by   di.databasename, di.tablename, di.indextype, di.indexname,
di.uniqueflag
order by    di.databasename, di.tablename, di.indextype, di.indexname

( Thanks Terry Stover for this script.)

Then check your BobJ Joins.

Below is a sample query to get the join information from a Bobj Oracle Repository.

SELECT
  bobjadmin.UNV_UNIVERSE.UNI_LONGNAME,
  Table1Tables.TAB_NAME,
  Table2Tables.TAB_NAME,
  bobjadmin.UNV_JOIN_DATA.JN_DATAVALUE  || UNV_JOIN_DATA2.JN_DATAVALUE
FROM
  bobjadmin.UNV_UNIVERSE,
  bobjadmin.UNV_TABLE  Table1Tables,
  bobjadmin.UNV_TABLE  Table2Tables,
  bobjadmin.UNV_JOIN_DATA,
  bobjadmin.UNV_JOIN_DATA  UNV_JOIN_DATA2,
  bobjadmin.UNV_JOINCONTENT,
  bobjadmin.UNV_JOIN
WHERE
  ( bobjadmin.UNV_UNIVERSE.UNIVERSE_ID=bobjadmin.UNV_JOIN.UNIVERSE_ID(+))
  AND  (bobjadmin.UNV_JOINCONTENT.UNIVERSE_ID=bobjadmin.UNV_JOIN.UNIVERSE_ID  )
  AND  (bobjadmin.UNV_JOINCONTENT.UNIVERSE_ID=bobjadmin.UNV_JOIN_DATA.UNIVERSE_ID(+)  )
  AND  (bobjadmin.UNV_JOINCONTENT.JOIN_ID=bobjadmin.UNV_JOIN_DATA.JOIN_ID(+)  )
  AND  ( bobjadmin.UNV_JOIN.JOIN_ID=bobjadmin.UNV_JOINCONTENT.JOIN_ID  )
  AND  ( Table1Tables.UNIVERSE_ID=bobjadmin.UNV_JOINCONTENT.UNIVERSE_ID)
  AND  ( bobjadmin.UNV_JOINCONTENT.UNIVERSE_ID=Table2Tables.UNIVERSE_ID)
  AND  ( Table1Tables.TABLE_ID=bobjadmin.UNV_JOINCONTENT.JN_TABLE1_ID  )
  AND  ( bobjadmin.UNV_JOINCONTENT.JN_TABLE2_ID=Table2Tables.TABLE_ID  )
  AND  (bobjadmin.UNV_JOIN_DATA.UNIVERSE_ID=UNV_JOIN_DATA2.UNIVERSE_ID(+)  )
  AND  ( bobjadmin.UNV_JOIN_DATA.JOIN_ID=UNV_JOIN_DATA2.JOIN_ID(+)  )
  AND  ( bobjadmin.UNV_JOIN_DATA.JN_DATATYPE(+)='E'  )
  AND  ( UNV_JOIN_DATA2.JN_DATATYPE(+)='E'  )
  AND  ( bobjadmin.UNV_JOIN_DATA.JN_SLICE(+)=1  )
  AND  ( UNV_JOIN_DATA2.JN_SLICE(+)=2  )

I you find that your BobJ joins are not covering the entire Primary Index, then your Bobj application is doing full table scans and not taking advantage of any part of the Primary Index.

Secondary Indexes use the Primary Index and are not a useful in Joins as you might think. The secondary index is great for getting data from the table but when the secondary relies on the Primary Index and the Primary Index is not completely covered in the query, the none of the indexing is being used.

Try taking some of the reports that are a problem in BobJ and play around with the indexing to determine if you can get this to lightning speed. I have experiences remarkable performance differences with a simple indexing change.

Please feel free to send more information about your woes. I believe we can assist you on this list.

Chris Coffing
Senior Vice President
Coffing Data Warehousing
www.coffingdw.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>