Hi have a TPT job that connects to SQL Server 2005 to select rows from and load Teradata target table via UPDATE. I'm using the MIcrosoft SQL Server ODBC driver to connect (Yes, I know its not certified).
Here's the log file:
It is hard to read the output because the information from the operators is interspersed with each other.
However, right now, I would say that the Update operator is waiting in the acquisition phase for data from the ODBC operator.
It is possible that the ODBC operator is waiting for data from the source.
That's what I suspect as well. especially since select top 200000 from table ... returns rows immediately...
going to attempt to run an ODBC trace. I'm seeing an S1092 invalid attribute/option warning as well.
More info: we ran traces on SQL server to determine what TPT may be doing or not doing. Here's what we found:
When doing a “Select Top 100”, we get some pretty straight forward results:
CPU: 16, Reads: 4759, Duration: 84 – It finished so quickly, I didn’t have to see what was happening on SQL Server Side.
When doing a “Select *”, we let this run for about 5 minutes and got the following:
CPU: 3469, Reads: 22495, Duration: 312500 – You can see that for the duration, SQL Server isn’t really doing much. I also saw a wait type of ASYNC_NETWORK_IO. In every case where we see this wait type, it means SQL Server is waiting on something external to return results to (typically the app, but could be driver related or network related).
Our testing so far shows us that we can load tables with roughly less than a million rows without incident. However larger tables > 1 million appear to be problematic.
Not sure where to go from here....
Have you tried the same query from an adhoc ODBC test program, to see what (and how fast) it retrieves?
I would say right now, the only thing I can suggest is to download an evaluation copy of the DataDirect ODBC drivers and try them out and see if you get different results.
I have switched to using Ole Db (OLE Load Utility) connection and it appears to work fine now. I think ODBC was chocking on the volume of data. The same issue exists with Data Direct ODBC driver as well.