Optimize Data Dictionary Queries

Database

Optimize Data Dictionary Queries

Hi All, on an almost new and empty TD express instance queries against dbc.IndexV view take an unexpected long time, for example:

SELECT IndexName,ColumnName FROM DBC.IndicesX WHERE IndexName is not NULL and TableName='order_fact' and UPPER(DatabaseName)='SASORION' order by ColumnPosition    0:07:05.250000

I've started to add some stats. Any best practices or ideas for this situation?

Thank you!

6 REPLIES
Enthusiast

Re: Optimize Data Dictionary Queries

Normally, queries against dbc views returns result qucikly. 7 mins is a big time. Which version of TD you are using.

How much time it takes when you run query without any WHERE clause.

Re: Optimize Data Dictionary Queries

I think the issue would be with UPPER clause in where , the queries would run slow by using this

try without using upper (databasename). it is not required, because the column is  not case specific.

Re: Optimize Data Dictionary Queries

Thanks all for your responses so far.

  • I'm using TD 13.10.0.02
  • even without a where clause the query takes rather long :-(
SELECT IndexName,ColumnName FROM DBC.IndicesX;    0:08:06.770000

Enthusiast

Re: Optimize Data Dictionary Queries

This is a strange behavior and must be frustrating.

Are you the only one facing this issue or any one else is facing the same issue? Has the DBA applied some TASM settings which might be putting your queries in a low priority workgroup or something?

Re: Optimize Data Dictionary Queries

also without UPPER does not improve the query runtime

SELECT IndexName,ColumnName FROM DBC.IndicesX WHERE IndexName is not NULL and TableName='order_fact' and DatabaseName='SASORION' order by ColumnPosition    0:08:05.710000


Re: Optimize Data Dictionary Queries

I get a similar problem on a Teradata Version 13.10 VM.

Have you managed to find out what the problem is?

My VM is hosted in Win 7 64-bit workstation.

2 Gb Ram and 2 Processors are assigned to the VM.

I can run the query on a Version 14.00 VM and it completes in a couple of seconds.