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.
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?
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.
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:
select * from database1.table111 : 5 minutes
select * from database1.table111 : 10 seconds
select * from database1.table222 : 5 minutes
select * from database1.table222 : 10 seconds
select * from database1.table111 : 10 seconds
select *from database1.table333 : 5 minutes
select * from database1.table333 : 10 seconds
select * from database1.table222 : 10 seconds
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.
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.