I have an Excel sheet with a range of data containing 14 columns and 5000 rows of data. I am using the INSERT INTO TABLE VALUES() sql statement after making a connection to the Teradata database and looping through each INSERT statement 5000 times using VBA and ADO. Unfortunately it takes about 3-5 minutes to upload the data and I need to get that down to seconds. Even when I use SQL Assistant and use it import feature it take even longer. Does the Windows Teradata Driver support BTEQ Load or Fastload? Is there a solution or a driver setting(buffers?) to make this process faster? My VBA code resides in an Excel file that is used by many users that have the Teradata Driver installed on their machine and I cant really installed TPT or TUtilities on their machines.
Plus I dont know how to call TPT or TUtilities from VBA.
You can consider doing following things:
1. Save the excel sheet in csv format.
2. Use csv file as input data file to load to target table using either TPT or Fastload/Multiload.
1. Use Teradata OLE DB Access Module that has a windows GUI tool named "OLELoad".
2. Using this tool, setup connection to excel and target Teradata database, select content, launch utility to load the table quickly.
3. You can save the job and run it through commandline later again.
Thank you for the quick reply and the suggestions I very much appreciate it.
But I can use your suggestions. My users JUST have the Teradata driver installed on their computers and do not have TPT or Fastload or Multiload and I cant require them to have it.
As far as the OLE DB Access Module GUI I am not sure what this is and it seems it would require an install on the users PC's.
I need to upload the data using VBA and the Teradata driver. It needs to be seemless when the users click the "Update" button on their template.
I just cant believe it takes 4-5 minutes to upload the data from a users computer using the Teradata driver or even using SQL Assistant for testing.
I do have a Linux batch server that I use for scripts that I schedule and that server has TPT, Fast Load, Multiload, etc. But only I can access it.
When SQL Assistant is loading slowly it's usually due to a very low number in
Tools > Options > Import > Maximum batch size for simple imports
It defaults to 20 and should be set as high as possible (i.e. 999), then loading 5000 rows should be a few seconds.
I don't know how VBA/ADO processes the Inserts, but if it's not batched it will be sending one row at a time and then waits for response before sending the next row.
Thus the speed to load a row is mainly based on your network speed, a bit above the time needed to PING your TD system.
Maybe there's a way to use parameter arrays in VBA/ADO.
Some possible alternatives, using only ADODB with MSDASQL provider and TD ODBC:
Thanks Dieter for the suggestion and I changed my setting to 999 in sql assistant just to test it and it still took over 5 minutes.
I would be surprised if my network is that slow in the company I work for. It is a big company.
In VBA ADO there is a batch command when working with recordsets and I use that instead of 5000 insert statements.
Is is an .UpdateBatch command. And it actually is a little faster than SQL Assistant.
Thank You very much
I am using the AddNew method with the UpdateBatch command and it still take 4 minutes to load. I can even supply you with the code and a sample data file.
And as I stated before it would be crazy if it is my network that is the issue.
Do you know what the connection string setting would be for the driver to set the maximum batch size for importing would be? I want to try that with the driver in VBA.
Fred and Dieter I was thinking of maybe not using inserts or a batch update but using and UPDATE TABLE FROM (SELECT *) SET = command. But in VBA and ADO I have to figure out how to execute an command "UPDATE TABLE (using the Teradata connection) and a Select * FROM [Sheet1$]" but I can't figure out have to get the code to understand the data in the Select * from [Sheet$]. If you guys have any thoughts let me know. My only concern is that if all the data on sheet1 has to be uploaded first over the network connection and if that is the slowness issue.
The INSERT/SELECT from a sheet/range or other data source is supported by a very few Microsoft database providers, but not by MSDASQL (interface to ODBC). And there is no connection string parameter for "batch size" or to specify using "array insert". So UpdateBatch still sends the data one row at a time under the covers - though it does use a parameterized request, if I recall correctly.
You would need to create a parameterized multi-statement request (repeat the parameterized INSERT statement N times in the request text) and bind N sets of parameters. Use that request (setting different parameter values) for each set of N rows. You'll also need logic to handle the last few rows when there are less than N left (e.g. build a different request or just do those few rows one at a time).