TDA 2.7 slow compared to TOAD 10.6

Toad Data Point
1

select dci_text_parse_fnc(3,flv.meaning,’-’) email_address

, flv.start_date_active, flv.end_date_active

from fnd_lookup_values flv

where (flv.lookup_type = ‘SHIPPING_NOTIFICATION_CUST_CAR’

and dci_text_parse_fnc(3,flv.meaning,’-’) is not null

Any thoughts?
Thanks
Dave

Wejust upgraded from 9.7 to 10.6 and have just started using TDA. Considering using TDA for end user query tool (to replace Oracle Browser).

I have TDA and TOAD open side-by-side and a SQL*plus session open in Putty (AIX).

When I run the exact same query in all three environments - TOAD and SQL*Plus return values right away. TDA is about 5 times slower. Does not matter the order, i.e., TDA first, then TOAD, then SQL, then TDA. REsults the same - very slow performance for TDA.

Feels more like a bad ODBC query than a good SQL*Net query.

This is the query. The function is a public synonym to a custom function.

       or flv.lookup_type = 'SHIPPING_NOTIFICATION_CUST')
2

How are you connected in TDA. There are three ways to connect:

ODBC
OCI Client
Direct Connect

I would suggest using a OCI client to do comparison.

The other question I have, is how many rows is the query returning? Some applications retrieve the first N rows and then pause and do not retrieve all of the rows until the user scrolls for more data. Stopping at the first fetch will make the retrieval appear to be fast.

TDA has this feature but does not have it on by default. So check Toad for Oracle and TDA to see if all rows are being retrieved when doing comparison.

Debbie

3

Like any good developer I have not read any documentation. So I am not sure if I am using OCI or not.

The connection I am using is in the Oracle grouping in the navigation manager. The properties for the connection show Oracle Client, with a Connection Home pointing to a 10G home on my C drive that is a valid TNS names file. I am reasonably certain it is the same TNS file that 10.6 is using. So I think this is OCI - yes?? The query itself returns 2 rows. Obvioulsy the SQL*plus session on AIX is using a different TNS.

Thanks for taking the time to respond.

Dave

4

Interesting development. I ran the same query this morning and performance is much better. In most I would say that it was just DB traffic or maybe Windows indexing service was running, or ??, and forget it. But the fact that TOAD 10.6 was not having problems at the exact same time causes me some concern.

5

It does seem that you are using an Oracle OCI connection. I am also concerned about the slowness you experienced. However, I need more to go on.

I did think of one other thing. There is a bug in 2.7 where additional SQL statements are being executed to refresh the Navigation manager. This could be what makes it look like a delay.

To get around this, go to the the Tools menu, choose the Configuration Wizard and select the Toad for Oracle layout. This will change the layout to have just a project manager and editor. In this configuration these refresh statements are not being executed.

Give that a try. You may like the layout better as it is more like Toad for Oracle.

Debbie

6

Well the query in question runs better, as does a more lengthy query against a much larger data set. I am going to assume that this is the answer and continue using TDA as my primary development environment (proving it to be suitable for casual users). If it reoccurs I will be back.
Thanks
Dave