Upload Performance MATables to MS SQL Server

[FK1]

Hi Everyone,

I was wondering, whether there exist any benchmark values regarding upload performance of a MATable-File to MS SQL DAtabase?

Here is, what I do:

I am only refreshing the metadata counts (i.e. MaTables-File) of four nodes:

What CI does in the background ist initializing a Stored Process Session and among other things executing a macro called "MAUPLOAD". 

This macrio, in turn, uploads the locally stored MAtable.sas7bdat-file of the MATables-Libary "MATables.THAAEVZZSKRHHRUZT{SASusername}"to the database.

In my case, we are talking of a file that has only one column, but roughly 6.4 million rows.

Even though I am already pimping the sql-query by setting READ- and WRITEBUFFER to 32767 it still takes about 3 minutes:

Shouldn't this be faster?

We are using the ODBC Driver  17 for SQL Server, which apparently is not supporting Bulkload

, because every time I am trying to Add BULKLOAD = YES as a option within the porperties of the business context,

what CI in the background does, is running a STP and executing a macro called "MAVALUPD". There the error happens:

"BCP=/BULKLOAD= option not supported by this datasource"

 

Any help would be very much appreciated.

Thx,

FK

[infomevijay]

I would recommend you to get rid of the cell nodes after each selection which will create an implict AND between the two selection nodes. The MATables are generated due to the cell nodes. The first cell node is generating the MATable which is uploaded into the database. If you are using cell nodes, MATables will be created and stored in the MATables library. To optimize the query performance, SAS loads the MATable in to the CI_TEMP (dbtmplib) schema on the database and they runs the query generated by the second select node. It is always recommened to have bulk load enabled for dbtmplib library. You are using ODBC which is lacking bulk load. I can see that you have SAS Access to SQL server, you should be using SAS/ACCESS to SQL server instead of ODBC for bulk load.

The best way to improve the performance is to get rid of the cell node which would eliminate the upload.

/Vijay

[JamesAnderson]

HI,
Can you post your version of SAS Marketing Automation, the operating system you are using for the SAS Servers, and which SAS/Access engine you are using (SAS/Access to SQL Server or SAS/Access to ODBC).
Thanks
James

[FK1]

Hi @JamesAnderson ,

here, the info, you requested:

SAS-Version: SAS 9.4 (9.04.01M6P11152018)

MA-Version : SAS Marketing Automation, Version 6.5,  Build Version: 9.04.01M0P060816

OS: WIN (X64_DSRV16) - Windows 2016 Server, 64 bit

SAS-Access:

SAS/ACCESS Interface to PC Files
SAS/ACCESS Interface to Microsoft SQL Server

[infomevijay]

The SAS/ACCESS engine used for dbtmplib is ODBC engine. You can check the datasource for dbtmplib library, which is SAS_MS_SQL_DB.

The data source SAS_MS_SQL_DB is using ODBC Driver 17 for SQL Server.

[FK1]

Yes, the datasource is a ODBC engine. This is because the SAS CI Solution Architect advising us told me, to rather use ODBC engine, than SAS ACCESS to MS SQL. I never understood why ,beause among other things ODBC is not supporting Bulkload!

[SASKiwi]

I have always found using the INSERTBUFF option works well for tuning row inserts for SQL Server. I'm also using ODBC. I suggest you start with INSERTBUFF = 10000 then try other values to compare performance.

[JamesAnderson · Accepted solution]

Hi FK1,
Using bulk load with the SAS/Access to SQL Server is a little different to other access engines - your libname statement says SQLSVR so you are using this not SAS/Access to ODBC.
Firstly you should use the ODBC driver that is provided by SAS, which includes bulk loading capabilities (it looks like you are using the Microsoft driver):
[A screenshot of a social media post Description automatically generated]
Secondly in the ODBC DSN configuration, using the SAS provided driver, you have the ability to configure bulk load:
[A screenshot of a cell phone Description automatically generated]
And lastly, even though you have configured bulk load in the driver, you don't reference BULKLOAD in the library/dataset options or in your case in the Business Context Options, you reference INSERTBUFF and DBCOMMIT.
I did a test using a combination of Driver Bulk load, INSERTBUFF and DBCOMMIT. In the test I loaded something resembling an MATables (single numeric column) with 6 million records, using Enterprise Guide and PROC APPEND (which is what maupload stored process uses):
[INSERTBUF=32767] : 3 min 4 seconds
[INSERTBUF=32767 + DBCOMMIT=0] : 2 min 40 seconds
[Driver Bulk load + INSERTBUF=32767] : 1 min 10 seconds
[Driver Bulk load + INSERTBUF=32767 + DBCOMMIT=0] : 9 seconds
I recommend you switch drivers and turn on bulk load within the driver.
Cheers
James

[JamesAnderson]

Looks like the screenshots of the drivers got dropped...

Driver typeBulk load config

[FK1]

Hello James,

this is exactly, what I was looking for!
I applied all the recommendations from you and this is the result 🙂

Summary Statistics for SQLSVR are:
Total SQL execution seconds were: 7.408242
Total SQL prepare seconds were: 0.000476
Total seconds used by the SQLSVR ACCESS engine were 9.406028

It really puzzles me, that the CI Solution Architect advised me to use the slow Microsoft Driver rather than the fast SAS ACCESS for SQL Server Driver....

Thank you very much for your quick and very helpful help!