When using MS Access and submitting a Select query that uses a join between a Teradata linked table and an MS Access table, the query is repeated for each row within the MS Access table. In other words, submit an MS Access query to join an MS Access table with 10,0000 rows to any given Teradata table and the result is you get 10,000 indivindual Select statements submitted to the Teradata system. Does anyone know of any way to prevent this. It is my understanding that the query repetition is the result of the ODBC driver. This command repetition also occurs with Sql Assistant when one submits an "imported" query (Select, Insert, etc.). The query is generated for each record in the imported file. The pass through MS Access is not an alternative option as it does not "link" in pass-through Sql with and MS Access table. Has anyone found a way to prevent the query repetition I describe?
In general, running queries through Access is not going to be very efficient, especially over an ODBC connection. Can you move your Access table into Teradata and run the query on the server? You will have much better performance if you remove access from the equation and convert to a visual basic application connecting to teradata directly through the OLE DB driver.
It is probably a bigger project to convert your application, but in the long run it will work better for you.
My first reaction was to forbid the use of MS Access tables in queries going against Teradata. However, we have not a few, but hundreds of users doing this (and it's spreading). So, it would be far to great an effort to attempt MS Access alternatives. In thinking about the ODBC driver replicating one Select statement for each MS Access table row going against Teradata, I can only think in terms of; this is so primitive. I mean, what were these ODBC driver developers thinking!!!
When submitting an MS Access query, the RDBMS engine used is Access. Which would be the advantage of having a powerful Teradata system, if you let your users use the Access engine to run queries? Wouldn't it be more practical to move the data from Access to Teradata and have views on Teradata to do the "join with an Acces table" work? And what about the "one fact, one place" philosophy? Teradata wouldn't be anymore used as a Datawarehouse system...
Easier said than done. Non-savvy Teradata users have no clue about creating DDL, then loading a Teradata table (even in a "special" database). MS Access has an established niche. Of course one cannot compare the power of MS Access to Teradata. On the other hand, one cannot compare the user friendliness (ease of use)of Teradata to MS Access. There, too, lies the practicality. The time and resources necessary to move hundreds of MS Access tables to Teradata is a daunting task, especially when the users have no Teradata savvy. Cost is another consideration, not only in the manual effort to move the tables but in dollars. We all know what the cost of a gigabyte of space is on Teradata versus the cost of a gigabyte on one's desktop PC.
"One fact, one place" philosophy in Teradata? Teradata is hardly the sole source of information in any company. There are network servers, MVS mainframes and PC's, all of which hold information. A true data warehouse cannot be limited to just one platform because (though many would like to think otherwise) information is stored (albeit redundantly) on many platforms. A true data warehouse encompasses all platforms and all information. Hopefully, that's where "hub" technology will eventually take us.
In oracle, I created an application that allowed the users to upload their tables into their schema using access and ODBC, then in a web application they could pick their tables from a drop down box. The query would run against any of the tables they uploaded.
That might be an option. If the queries are expecting the same data each time, you might be able to allow users to upload tables via access.
I got some of the non-technical people to be able to export their tables over ODBC and now they are running pretty well. It got us off Access, and the performance is much better. We had people using Access to build queries and access really doesn't have any idea how to run a query effectively on another database. Some of the SQL it generated would have taken days to run.