|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/27/2005 11:17:00 AM
Posts: 3,
Visits: 1
|
|
Hi,
Following is the background of the problem and my questions:
Problem Background:
I have a SELECT query that takes 16sec to return the answer set using Queryman or BTEQ. The optimizer chooses a plan that has 5 or 6 steps.
When I run the same SELECT query through Ascential Data Stage tool, the optimizer chooses a plan that has 9 steps, which is different from the one when EXPLAINed using Queryman. The query takes forever to return the answer set.
I collected stats on the join columns and the query run using Queryman took 10min to return the answer set.
Questions:
1. Did anybody experiened similar problem when using Teradata with ETL tools like Ascential Data Stage, Informatica?
2. Is there a way to make the query to perform at its best(16sec elapse time to return answer set) in Data Stage tool?
Thanks,
Raj
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 5/2/2008 2:28:55 AM
Posts: 15,
Visits: 2
|
|
|
My suggestion is to check the query submitted to the database. We may not see the exact query that is submitted via BTEQ. Some times these tools generate data mismatch conditions. Due to this optimizer might generate different plans.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 9/17/2007 2:33:00 AM
Posts: 17,
Visits: 1
|
|
I know that the Teradata API stage connects sessions in ANSI mode, so I like to do all of my SQL Assistant vs DataStage performance testing in ANSI mode sessions.
Do you have Query Logging enabled at the request level for the user that is submitting the query? If you can briefly turn on SQL & STEPINFO logging for the user, you can verify with accuracy that Teradata is executing different plans for exactly the same query.
- Chris
|
|
|
|