Does anyone use a 3rd party ODBC connection for Teradata that work with WebFocus? Or have you configuring your ODBC connection differently. I know WebFocus has one that you can purchase but it works the same as Teradata's default ODBC We are pulling about 40K rows in about 3 minutes where it should be more like 10 seconds. Any help would be great.

This message has been edited. Last edited by: kerry,

IBI does not provide an ODBC driver for Terradata. IBI's reporting server has what are called data adapters that use the native database clients to access data. In the case of Terradata they use the odbc driver or cli client provided by the database vendor.

We are utilizing Teradatas generic ODBC driver which takes an exoborant amount of time when pulling in 40K rows of data. I was hoping that someone out there knows of a 3rd party adapter that will take seconds instead of minutes.

You might want to take a look at the SQL you are sending to Teradata. We use both CLI and ODBC on a Unix platform and have much better performance than that when the TABLE requests and/or passthru SQL is well-formed. We use the native Teradata client.

Also opening 40K rows in a browser may be what is taking so long. Why don't you hold the rows in a temp file just to see how long the data retrieval actually takes making allowances for the time it takes to write the file to disk.

I am running the query in SQL assistant...which is their SQL query tool. It looks like it takes 4 seconds for Teradata to finish the query however it takes 3 minutes to spool the recordset back to the requester.

Putting 40K records on a browser doesn't make much sense. It might be better to do a summary report and a drilldown. There should be a number of topics on this forum about performance and how to improve it from tweakingthe TABLE/SQL to rearchitecting your solution.

i am using webfocus for the security. I am bringing the recordset (40K at most) depending upon the choices that are selected in combo boxes. The data is then brought back to excel which is drillable.

As mentioned earlier, try putting ON TABLE HOLD in your request and see how long it takes to complete the procedure in WebFOCUS. I strongly suspect that the issue doesn't have anything to do with your data connector. It has to do with how long it takes to return 40,000 rows of data to an Excel spreadsheet. (Hint: it isn't instantaneous)

i know its not instantaneous, but the ODBC connection is the issue. I have the enduser make picks from combo boxes which then pass an SQL statement to Teradata. When I run this in Teradatas SQL assistant it takes 4 seconds to finish the query but another 2 minutes and 52 seconds to display the data. I was hoping that someone else in the WebFocus world is using Teradata as well and would have some insight. Someone on another forum has mentioned to change the buffer size of the ODBC to see if it speeds it up. Another division in my company has actually purchased a 3rd party adapter that has sped the process up drastically. I was hoping that someone knew of another 3rd party adapter that plays well with WebFocus.

thanks for your suggestions!

Both Darin and I have suggested that you HOLD the selected data to see how long it takes WF to access the Teradata data. I suspect that if you do that, you will see that only seconds have elapsed.

You have said yourself that it takes almost 3 seconds to display the data. We have both mentioned that the Teradata piece is finished by then and it is the loading of the spreadsheet that is taking the time. In fact, if you look at the web console while the browser is trying to load, you will probably see that there is no agent running. That means that the Teradata access is finished.

It isn't Teradata that is your problem; it is the architecture of your solution. 40K rows is a lot to load into a spreadsheet and will take a lot of time.

Please, please do the hold test to confirm or deny what we are saying.

quote:

When I run this in Teradatas SQL assistant it takes 4 seconds to finish the query but another 2 minutes and 52 seconds to display the data.

To reiterate everyone's point (because I don't think you are really listening?).

From your quote above, you state that you are

using Teradata's SQL assistant - NOT WebFOCUS.

it takes 4 seconds to finish the query - SQL resultset ready for tranmission to client

another 2 minutes and 52 seconds to display the data - important thing here is to display the data - this is the client end not the ODBC adapter so look at network and client.

It sounds as if you want it to be the ODBC adapter but I am fairly sure that, from your statement, it isn't.

The proof would be to run and HOLD the data (as per Darin and Ginny), because that will cut out the client part as it will be achieved on your server.

The other thing to remember about the adapters sold by IB is that they are written such that they take advantage of any specific SQL for the target RDBMS that the ODBC path might not be able to take.

Good luck

T

Old FOCUS coders never die, they just become functionally stable. (Tony A )

---

Current Client: WebFOCUS 7.6.2 Win XP SP2/IIS 6/Tomcat 5.5 - MRE / BID MS SQL / Oracle - DevStudio 7.6.6 7.1.6 Local: WebFOCUS 7.6.6 7.1.6 on Win XP SP2/Apache/Tomcat 5.5 - Self Service