I'm very new to teradata, have only couple of days of experience. We have a situaiton. Reports and queries are running in seconds in Dev DB but taking minutes in production. Data volume is almost same, about 5-10% less is dev from prod. Considering resources, prod server is better.
Can someone please help suggesting some probable area from where I can start my analysis, as I found many posts and pdf on web but not sure where to start.
Any information will be very helpful.
Check the DDL of the tables in DEV and Prod, check whether the primary index is same on both the tables.
Check any collect stats applied on Dev tables and not in Prod.
ex: help stats databasename.tablename;
One more suggestion from me is we couldn’t expect same performance in both dev and prod. Because in prod lot of batch jobs also will run in parallel.
But we can improve performance based on stats & primary index like wat karthik told.
DBQL is the query log that captures CPU and IO consumption for queries. These numbers depend only on the query itself (DDL, stats, data), and do not depend on the batch jobs that run in parallel.
DDL and primary index is same in both environment and both the servers are new and not sure if there are much batch jobs in prod. Will start with DBQL and compare the output between environments.
Like others have said, could you check if you are also running other batch jobs on while executing these queries on Production. In that case, check the resource utilization ( CPU, Memory, I/O).
Also are you refreshing the stats on the tables in Production. If you are using stale stats, it will hit the performance.
As said before - first check DBQL to see if resource consumption is different. If yes, stats, index choice etc. should be checked.
If no - check the TSAM settings for the workload this reports are running in.
Try running the reports in user window i.e out of critical batch window in production..Also check for the workload this reports are running in....