Upload Excel Range using Windows Teradata Driver and VBA Slow

General
JC
Enthusiast

Upload Excel Range using Windows Teradata Driver and VBA Slow

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.

 

Thank You

JC

Tags (3)
18 REPLIES
Teradata Employee

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

You can consider doing following things:

option 1:

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.

 

option 2:

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.

 

-Sudhansu

JC
Enthusiast

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

Sudhansu,

 

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.

 

Thank You

 

Junior Contributor

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

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.

 

Highlighted
Teradata Employee

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

Some possible alternatives, using only ADODB with MSDASQL provider and TD ODBC:

  • Create a parameterized INSERT (with ? placeholders in the SQL and cmd.parameters.Append / CreateParameter) command; within the loop set parameter values and execute that same command
  • Create an empty RecordSet object based on the table (e..g. SELECT * FROM table WHERE 1=0) and use the AddNew method to copy the data to that RecordSet, then use UpdateBatch method to insert the rows
JC
Enthusiast

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

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

JC
Enthusiast

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

Thanks Fred,

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.

 

Thank You

JC
Enthusiast

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

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.

 

Thank You

JC

Teradata Employee

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

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).

JC
Enthusiast

Re: Upload Excel Range using Windows Teradata Driver and VBA Slow

Thank You Fred, I very much appreciate the help on this!
Previously you have 2 suggestion. The first was the AddNew UpdateBatch Method which is what I tried after I tried the 5000 separate INSERTS using a loop. Are you saying the the UpdateBatch is as bad as the 5000 Inserts?
Also in your saying in your latest post suggestion... "You would need to create a parameterized multi-statement request"
that this method is better and faster than the UpdateBatch method? Is this method the same as you second suggestion from your first post?
I very much want to try the "parameterized multi-statement request" but i am just a little unfamiliar with it in VBA code and I would have to do some research on the correct syntax.

Thank you again!