Teradata Upload

[Paul H]

Hi,

This is somewhat of an off topic question, but I've been asked to post this here. My current employer used a lot of pass through queries and stored procedures. One of the databases it interacts with is a proprietary data warehouse called Teradata. One of my tasks is to upload data to an ODBC linked table via an append query. First I delete all data using a delete query. It seems very simple except that it takes anywhere from 20 to 30 minutes to delete 15,000 rows and then another 20 or 30 minutes to append the new data. I tried using DAO to write one row at a time, but this proved even slower. I am the only person using this table. The main suspect is the server which runs jobs for a large corporate user base. I don't think there is any way around it, but I'm putting it out there in case you have any ideas. We've tried adding and removing various indexes with no success.

Thanks,

Paul

[rpeare]

What is your response time like once the data is in the table?
What is the linked table you're appending data to? is it SQL, oracle, something else?
Are you simply running two queries in succession or is there other code involved?
If there's more code can you post the code?
How much data are we talking about, you said 15000 records, but how many fields does each record have?
When you run a Select query for the same data set what is your response time like?

[Paul H]

Unfortunately I can't tell you what the back end is (i.e. SQL, Oracle, etc), except that it is a data warehouse, but here goes on the rest.

Yes, I am simply running two queries, Delete and Append.
I am appending only 3 field, the only 3 fields in the table.
If I run this query as a select query it runs in seconds.

Once the table is populated, a stored procedure is run against it. This runs in 5 to 10 minutes which is acceptable.

[ranman256]

Create a 'stored procedure' to delete. THAT shouldnt take the 30 minutes to delete.
If using ORACLE or SQL srvr, just use the TRUNCATE command. It should be instantaneous.

[rpeare]

if you are appending summarzied information to a table and using an ODBC connection you have to know what driver it's using. I'm assuming you have the database table linked into your access application and you're just running a delete/append against that table, if you have the table linked you can right click it and show the LINKED TABLE MANAGER which should show you the ODBC driver it's using, and/or go to your control panel > administrative tools> ODBC drivers and look at what's there.

You can run some other tests too like creating a duplicate file structure in the same access database and run your delete/append against that table and see how long it takes.

What does your code look like that's running this process?

just

docmd.openquery (<Delete query name>)
docmd.openquery (<append query name>)?

[Paul H]

rpeare:

The driver comes from Teradata and is simply called TData32.dll.

Yes, my code is just that simple.

    DoCmd.OpenQuery "qryRouteSheet_Delete_Invoices"
    DoCmd.OpenQuery "qryRouteSheet_Append_Invoices"

ranman256:
That is a great idea and simple to implement. I should find out in due course what kind of database it is.

Paul

[Paul H]

ranman, you get the prize.

Our new stored procedure deleted 25,000 rows in about 2 seconds. Now the question is, can we write a stored procedure to upload from Access that will work almost as well. I'm looking into it.


Paul

[Paul H]

ranman's solution is working great. Assuming again that Teradata is SQL Server or Oracle, could this process work in reverse, i.e. upload a local Access table, query or an external file into a Teradata table using a stored Procedure?