We are facing one strange Behaviour for SQL which is generating two different plan for same SQL. When we use SQL ASSIT. then same query take few seconds to execute but same query going into product join and taking more resource on MSTR tool.
We also performed below steps to find out the root cause:
Did anyone faced such issue, If yes, please suggest how to fix this issue ?
As well as checking transaction mode (as suggested by @Fred) you should also check:
- default database for both queries
- check API being used (this is less likely to be the problem). MSTR is almost certainly going through ODBC whereas SQLA might be ODBC or .NET. Certainly ODBC can 'convert' some sql before sending it to the dbms.
I'm assuming here that the two sql requests were run within a fairly short time span of each other?
Can you post the SQL and the explain plans for both? Preferably retrieved from DBQL tables as those will contain 'reality' - i.e. what SQL was actually run and what plan was used.
Is the same DSN being used for both queries? Many BI tools use 64 bit drivers, SQLA uses 32bit drivers or .Net Providers.
If they are using different DSNs what are the differences? i.e. disable parsing being enabled on one but not the other.
From SQLA are you using Teradata.net or ODBC for the connection?
We are using same DSN name and ODBC for SQL Assist & MSTR tool.
Depending on how your system is setup, you can have 32 and 64 bit dsns named the same. you can try to change the name of the DSN via the control panel and see it still shows up in both tools just to verify. are you using the same user id from both tools? different cost profiles can be assoicated with different ids, you DBA team would need to help verify that in your environment.
Is the actual SQL statement text being submitted to the database the same?
You mentioned Specific plan. Does the statement include parameters?
We are using different user but cost profiles (TD 15) are same. Also DSN are same
Yes, We are submitting same query and Query has parameters. We also checked the cacheFlag which is NULL in both cases