Archives of the TeradataForum

Message Posted: Wed, 22 Feb 2006 @ 23:17:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I'm not exactly sure I know what your question is, but I think the answer is that these utilities will work the way that they are programmed to work.

You indicate that you have been told they work in a particular way because of the ODBC driver. The ODBC driver has been known to cop a lot of flack, but I don't think this behaviour can be blamed on ODBC or the Teradata Driver. ODBC is about submitting SQL Statements from an application to a database and getting results back; all through a standardised interface.

In the case of a select this can be a row at a time (eg. select * from customer where unique_customer_id = 1234) or multiple rows at a time (eg. select * from accounts_held where customer_number = 1234) or full table (eg. select * from customer;). It is up to the application (in your case MS-Access) to determine which is the appropriate query to submit.

On the data loading front, SQL really only defines one mechanism to load data from an external source and that is the INSERT INTO some_table VALUES (...); query. This by definition is single row at a time. Thus Queryman (SQL Assistant) choices are somewhat limited as to row at a time processing for an import. In other words if you have 10,000 records in your file, you will get 10,000 inserts to the database.

The alternative to this is to use a non-sql approach to loading the data. This includes the bulk load utilities such as tpump, fastload or multiload.

It is true that some sophisticated applications (and Access may fall into this category) will interrogate an ODBC driver as to its capabilities and may alter their behaviour to leverage some advanced capabilities. However, the functions that you mention are fundamental operations. It is unlikely that the applications (especially MS-Access) are performing differently because of the ODBC driver.

In other words, if you were attempting to use linked tables or INSERT INTO type of queries, you would see the same behaviour no matter what the database is. That's not to say that some tools might give the impression that they are processing multiple records at a time or whatever but at the end of the day they can only perform the functions of the queries they are capable of generating or the capabilities of the ODBC API (as defined by Microsoft - and implemented by various companies ODBC drivers).

Getting back to MS-Access for a second...

I was under the impression that Access would query the entire content of a linked table with a single select (i.e. select * from customer; type of query). This essentially made a working copy of the table in access which it then joined etc in accordance with the query supplied by the user. Obviously this is not a good approach if you planned to join 2 multi-million row tables as you not only have a huge amount of data transferred across the network, but you would be attempting to process them using serial processing (i.e. you wouldn't benefit from TD's parallel processing).

If MS-Access does query single row at a time, you may have difficulty controlling it.

On the other hand, if Access selects the entire table at a time, or large chunks of it, you may be able to use TD Dynamic Workload Manager (V2R6) or TD Dynamic Query Manager (pre V2R6) to set up constraints - eg a query that is going to return more than X rows could be "banned" for certain user groups (in this case an error would be returned to Access). Obviously this will only be viable if MS-Access works the way I thought it did (extracting the whole table at a time).

I hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>