Toad World® Forums

Teradata Connections slow to open and execute in 4.0

I recently upgraded to Data Point 4.0 and am having performance issues.

When I first connect using Teradata connection it takes about 30+ seconds for the connection to open. When I run the first query (select *) to retrieve 5000 rows it takes about 60 seconds. I run the same query a second time and it takes 60 seconds. I run the query a third time and it takes <5 seconds. Every query after that is fine. If I close and reopen the Data Point and have the same problem with establishing the connection, then running the first two queries, but everything is fine after that. All of my automations are running significantly slower now as well.

When I was using the prior version of DP all the connections opened quickly, queries all ran fast, and automations performed comparably.

What version of TDP did you use before?

Could you please check that the check box “Use Non-X view in Object Explorer” is checked?

To do that open Tools | Options | Database | Teradata.

Regards

Aleksey

I was using 3.8.1.677. I did not have the "Use non-X View’ option selected.

I’m now using 4.0.0.624. I selected the ‘Use non-X View’ option and it did speed things up significantly. It’s not quite as fast as 3.8.1.677 but it’s close enough to not drive me nuts anymore.

Will this setting speed up automation tasks as well or does it only affect UI-based queries?

I’m glad that this setting helped you.

The automation performance should be better too but it will depend on what type of queries are used in automation .

The problem is back and it’s worse than before. I still have the Use Non-X view option selected, but it’s become unusable. A simple count(*) on a table with 10,000 records takes about 5 minutes to complete. In TDP 3.8 it takes about 1 second. I have 3.8 and 4.0 installed on the same machine. 3.8 works fine, 4.0 not at all.

Also, in 4.0 when the query completes, it shows Duration: 0:00:01.606. However, it takes 5-10 minutes to display it. It’s like the query is executing fine in Teradata, but the UI then hangs for a long time until it displays the results. This is also impacting my automation jobs which used to run in a few minutes, but now take 5x-10x as long.

What type of Teradata connection do you use - ODBC or .Net provider?

If you use .Net provider you could try to change Response Buffer size. You will find it on the Advanced tab of the Connection Properties form.

I’m using the Teradata provider built into TDP. I presume it’s Teradata.net, but I don’t see anything specifying either way. I also use SQL Assistant (Teradata’s query tool) and its Teradata.net provider and that is blazing fast.

I played around with the Response Buffer Size but that didn’t do anything. The weird thing I just noticed is that the first time I hit a table, it takes about 5-10 minutes to complete the query, but successive queries only on that table take 1-2 seconds. If I try another table on the same database, it will take 5-10 minutes. But then successive queries are fast. I then go back to table 1 and it is still fast.

As an example, a series of queries and the time each one takes:

  1. select * from database1.table111 : 5 minutes

  2. select * from database1.table111 : 10 seconds

  3. select * from database1.table222 : 5 minutes

  4. select * from database1.table222 : 10 seconds

  5. select * from database1.table111 : 10 seconds

  6. select *from database1.table333 : 5 minutes

  7. select * from database1.table333 : 10 seconds

  8. select * from database1.table222 : 10 seconds

  9. select * from database1.table111 : 10 seconds

On the long queries, I hit F5 and the UI freezes for about 5 minutes, then it starts to respond and loads the data into the results pane within a few seconds. On the fast queries, the UI responds immediately to F5 and almost instantly begins to load the data into the results pane.

It doesn’t seem to be a database problem since queries are fine once the connection is established. It’s like TDP is opening a new connection for each table it hits, and that new connection process takes way longer than it should.

TDP opens only one connection per TDP instance and one connection per SQL Editor. It does not open connection per query.

Could you please open a support case ticket? So we will get more information about the issue.

Thanks

Aleksey

P.S. I think you opened a support case and the issue went away when you upgraded to TDP 4.0.1. We are now able to trace the Teradata .net provider. So if you run into any other issues let us know and we will turn on tracing for this provider.

When a query of Teradata is being run for the first time with Toad Data Point, it takes a long time to retrieve a result.

Then, when the same query is being run the second time, the performance increases.

The issue happens with any new query and it affects automated tasks.

Solution

  1. Open Tools - Options - Database - Teradata.
    Check Use Non-X views in Object Explorer

  2. On Tools - Options - Editor - Code Completion.
    Turn off all the options of Code Completion.

Then you will be getting status results as the Issue is fixed in version 4.0.1 and can be downloaded.

1 Like