Upload Excel Range using Windows Teradata Driver and VBA Slow

General
Junior Supporter

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

JC,

 

You are inserting 5,000 rows * 5,000 loop = 25 million rows.

You are setting a very high goal of being able to do that in 'a few seconds'.

 

You should be preparing the 25 M rows and use a load utility. Even then it will take more than 'a few seconds'.

 

Better alternative is to load the 5,000 rows, then use SQL logic to multiply each row 5,000 times.

Depending on the size of the Teradata system, and efficiancy of your SQL, you may get to your 'a few seconds'.

Teradata Frank, Certified Master
JC
Enthusiast

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

Fred apologies if I mislead you. i am loading one row at a time looping 5000 times. So just a total of 5000 rows. i use the load utilities all the time when I am loading millions of rows.

thank you
Junior Contributor

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

I changed my setting to 999 in sql assistant just to test it and it still took over 5 minutes. 

That's strange, seems like it's not using batches or there's much more processing in the background.

You're running a single Insert Values?

What's the size of the data send to Teradata per row?

How many columns, any large VarChars?

What does a SHOW INSERT ... return besides the target table?

JC
Enthusiast

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

Thanks Dieter for the response.

In Excel VBA I am currently using an UpdateBatch method and before that I used the SQL statement INSERT VALUES().

In SQL assistant just to test the speed and the setting suggested by you I just used the Insert into table (?,?,....).

Here is my table structure.


CREATE MULTISET TABLE PRFMRGN_T.TESTUPLOAD ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CATEGORY_LEVEL VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
INIT_ID VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
FORMAT_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
STORE_NBR INTEGER,
SCENARIO_NAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
FORMAT_SUBID VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
CATEGORY_LEVEL1 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
FISCAL_YR VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
HALF_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
QTR_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
MTH_NM VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
MTH_NBR INTEGER,
AMOUNT01 DECIMAL(22,4),
AMOUNT02 DECIMAL(22,4))

NO PRIMARY INDEX ;

 

And here is one row of the data with the column headings.

 

CATEGORY_LEVELINIT_IDFORMATSTORE_NBRSCENARIO_NAMEFORMAT_SUBIDCATEGORY_LEVEL1FISCAL_YRHALF_NMQTR_NMMTH_NMMTH_NBRAMOUNT01AMOUNT02
CATEGORY001

AD1

STORE A3248SCENARIO1ACATEGORY001AFY1ST HALFQ1MAR201702473458.67197231.65

 

You can just create a text file with that row 5000 times. And test upload the data to the table and it takes over 4 minutes for me.

 

Thank You

JC
Enthusiast

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

Dieter and Fred what is also strange is that I thought maybe if I minimize the update data size the process would run quicker.
Here is a new test table.

 

CREATE MULTISET TABLE xxxx.TESTUPLOADSMALL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CATEGORY_LEVEL VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
FORMAT_ID SMALLINT,
STORE_NBR INTEGER,
SCENARIO_NAME SMALLINT,
CATEGORY_LEVEL1 SMALLINT,
MTH_NM VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,
AMOUNT01 DECIMAL(22,4),
AMOUNT02 DECIMAL(22,4))
NO PRIMARY INDEX ;

And here is one row of new values.

 

CATEGORY_LEVEL FORMAT_ID STORE_NBR  SCENARIO_NAME CATEGORY_LEVEL1 MTH_NMAMOUNT01AMOUNT02
B411148121MAY100000100000

 

You can see that I convert many of the text values to integers and eliminated 6 columns but the upload still takes over 4 minutes.

 

Thank You

JC
Enthusiast

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

Fred and Dieter,

So I have continued to test other options to solve this issue and narrow down the bottleneck. Well it seems that the issue is not the amount of data I am loading but the amount of rows.

I took my 5000 records and 14 columns and pivoted it to 12 rows and 1149 columns and just did a test upload and POOF less than 10 secs. Completely crazy. 

I know for a fact when you are retrieving data from a Teradata table to Excel the amount of columns and the data in those columns along with the BUFFER setting in the connections string has a tremendous amount of affect on the speed. But the upload process seems backwards. I am still determined to figure out why 5000 rows with 14 columns takes over 4 minutes to upload but the same data pivoted to 12 rows and 1149 columns takes 10 seconds. This mystery has to be solved out there for us users who love Teradata and integrate it into Excel using the ODBC driver.

Thank You

Highlighted
Teradata Employee

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

The round trip for a request takes time. The row needs to be packed up, sent through ODBC, over the network, received by the database, parsed, planned, sent to dispatcher, sent to AMP(s) for execution, end transaction processing, response to the dispatcher, response across the network, received by ODBC, respond to the application. When inserts/updates are done one row at a time, all of this overhead has to be done for each row. For typical networks and clients, 20 round trips per second is not unreasonable. All the comments about using batch, bulk, multi-statement requests,... all have the same goal - pack more rows into each request. As long as it is taking the same amount of time, that is a good indicator that the rows are being sent serially as single row requests.

JC
Enthusiast

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

Thank you Todd for you response. I completely understand the complexities of putting data into a database table as apposed to just dumping data out. But how can we cut out all those middle men? :) lol

I wish I knew how to pack more rows. If all the fastload is doing is just packing more rows then I wish we had that ability in the Teradata ODBC Driver. We love Teradata and love to write multi-user Excel templates to utilize it. And dumping data out of Teradata into Excel is easy and fast. I just wish the driver could have a fastload capability built in so I could simply invoke the command within Excel VBA and then it would be perfect. Fast download and Fast uploads for our users of our Excel tools. The driver distribution in our large company is a straight forward process but asking them to distribute fastload to all our users is a little more complicated. Thank you for your time.

 

JC

 

Teradata Employee

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

The Teradata ODBC driver does support using parameter arrays to pack data for many rows into a single request. But you're right, the challenge is using that feature with the generic Microsoft MSDASQL Provider for ODBC in the middle.

 

Could you consider using ADO.NET and the Teradata .NET Data Provider, instead of ODBC? I know "batch update" works as expected in TdDataAdapter.

 

Otherwise, a parameterized multi-statement request isn't quite as good as using parameter arrays, but can be substantially better than what you are seeing now. The down-side is that your VBA code has to deal with the details. You would need to create a Command object where the CommandText has N copies of the INSERT table VALUES(?,...,?); statement concatenated, set the Prepared property to True, and bind N sets of positional parameters to the command (.CreateParameter with the appropriate data type and .Parameters.Append). Then in your loop you would copy N sets of values to the .Value properties of the respective parameter objects and invoke the .Execute method of the Command object. When there are less than N values left, you would exit the loop, change the CommandText to have only the correct number of INSERT statements and set the Prepared property to False. (Hint: Start with N=1, a single parameterized INSERT command with Prepared set to True, for testing purposes; then you don't need special handling for a "partial" batch at the end.)