Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

On Teradata Queries are been fired twice. Once for Parsing and once for execution.

Posted by sranka on September 9, 2009

Hi All

Recently I been working with Enterprise Architect group to tune the performance of application on Tera Data platform. While working on varies issues, came across very interesting fact, a query which was taking only 15 sec on database , but was taking more that 20 sec to return the data onto the browser. While digging into the problem, Enterprise Architect found that query is been executing twice, once for parsing and next time the actual execution. This was very evident for all the request.We spoke with Oracle support and suggested following options ::

In windows:
1. Please check the setting on Teradata ODBC under Options, then check box for ‘Disable Parsing’.

This option is provided to the user to enable/disable parsing of SQL statements by the ODBC driver. If the user is using the Teradata Database specific SQL, then the SQL statements need not be parsed by the driver. If this option is checked, the driver does not parse the SQL statements.
Default: unchecked

2. Please ‘check’ on this option on Teradata ODBC driver and retest.

For UNIX:

Options Configurable in the .odbc.ini File,
NoScan=[ Yes | No ]
Default = No

After trying above option, we could not get solution to the problem, after talking with Oracle Product management they suggested to set the system variable to


   NQUIRE_EXECDIRECT=1

After setting the above system variable, queries were not executing twice.

What it does :

After setting the variable, OBIEE server engine doesn’t parses the query and send the exact the same query to database, hence only one query is been sent to database.

Where you can not use this solution :

One word of caution, if you are using any bind variables than you can not use this option.

Hope this helps

Sunil S Ranka

Advertisements

One Response to “On Teradata Queries are been fired twice. Once for Parsing and once for execution.”

  1. Shekar007 said

    Hi sunil,

    Where do I provide the NQUIRE_EXECDIRECT=1 system variable param??? Is it in odbc.ini file or as an enviroment variable

    Thanks,
    Sekhar

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: