ODBC default settings (Fetch Array Size)

MiguelBer
Padawan

Hi there,

We are trying to improve the performance of pulling data from Exasol using ODBC drivers since it's slower than we expected. We have already improve the network capacity, but we would like to know if we could also increase the data fetch size somehow. We know that this should be possible using ODBC standard parameters like ArraySize.

Does anybody know what is the Exasol default value for fetching data (rows or bytes) and if it's possible to modify it on the ODBC configuration?

Thanks a lot

1 ACCEPTED SOLUTION

exa-MichaelT
Team Exasol
Team Exasol

Hello all!
The Exasol ODBC driver reads the data from the server in blocks of about 16mb at once. Then it waits for the client application to read the data. Sending to the server can be done in even bigger blocks, up to 64mb. So the client-server communication is actually not a bottle-neck. 
When reading data from the driver, the application must use large data blocks with multiple rows in a block to gain performance. The size of this blocks is specified using SQL_ATTR_ROW_ARRAY_SIZE by the application using the driver. Default is 1. So you can look in the ODBC logs for this attribute. If the application reads in small blocks or maybe single rows, reading will be slow. Logging the process slows it down too. 
The writing of data to the database works similar. The application tells the driver how many rows it will put in a block with SQL_ATTR_PARAMSET_SIZE (default = 1). The driver will send each block directly to the server when SQLExecute() is called. Larger blocks mean faster data transfer.
If you want to tune your application for maximum transfer speed, make sure you adapt the number of rows you read/write in a block so the actual data contained there will be close to the values i wrote at the beginning.
Unfortunately you cannot do so if the application is not written by you. Some application have the option to change the number of rows to fetch/write at once in their settings. 

View solution in original post

9 REPLIES 9

exa-MichaelT
Team Exasol
Team Exasol

Hello all!
The Exasol ODBC driver reads the data from the server in blocks of about 16mb at once. Then it waits for the client application to read the data. Sending to the server can be done in even bigger blocks, up to 64mb. So the client-server communication is actually not a bottle-neck. 
When reading data from the driver, the application must use large data blocks with multiple rows in a block to gain performance. The size of this blocks is specified using SQL_ATTR_ROW_ARRAY_SIZE by the application using the driver. Default is 1. So you can look in the ODBC logs for this attribute. If the application reads in small blocks or maybe single rows, reading will be slow. Logging the process slows it down too. 
The writing of data to the database works similar. The application tells the driver how many rows it will put in a block with SQL_ATTR_PARAMSET_SIZE (default = 1). The driver will send each block directly to the server when SQLExecute() is called. Larger blocks mean faster data transfer.
If you want to tune your application for maximum transfer speed, make sure you adapt the number of rows you read/write in a block so the actual data contained there will be close to the values i wrote at the beginning.
Unfortunately you cannot do so if the application is not written by you. Some application have the option to change the number of rows to fetch/write at once in their settings. 

View solution in original post

mwellbro
Xpert

Hi @MiguelBer , 
been a while, but my case came through so https://docs.exasol.com/connect_exasol/drivers/odbc/using_odbc.htm#ConnectingthroughConnectionString... has been brought "up-to-date".
Still no sign of a way to adjust ArraySize as far as I can tell - did you turn up anything in that department ?

Cheers,
Malte

exa-Franz
Team Exasol
Team Exasol

Hi Miguel,
have a look here:
https://docs.exasol.com/connect_exasol/drivers/odbc/using_odbc.htm
The section "Best Practices" "Reading big data volumes" should help.
If not, let me know!

MiguelBer
Padawan

Hello,

Thanks for your answer, I think those best practices are more oriented to code using the library on C/C++ or probably for ODBC developers. My question was more oriented to ODBC configuration paramenters and the default settings that come with the standard ODBC driver.

It would be something similar to the parameter fetchsize using the JDBC driver.

A bit of background, we are using   BI Tool which connects to Exa using a ODBC driver and we would like to know how are the settings in terms of transferring data between Exasol and the BI tool using the ODBC

mwellbro
Xpert

Hi @MiguelBer , 

while I do enjoy the vote of confidence from @exa-Chris I can´t say that I know the answer to either of your questions ( default value or how to set the fetch size ) - we tuned a bit in the realm of Informatica Powercenter when trying to optimize fetch sizes by enlarging DTM- ( process ) and Buffer Block Sizes so the application "probably" takes care of setting such odbc parameters "on the fly".

I do however have an Exasol Support Case open where I requested a complete list of ODBC-parameters ( no pressure there, @exa-Chris  😉  ), so once they put in an answer I´ll be glad to share it here.

 

Cheers,
Malte

mwellbro
Xpert

Hi @MiguelBer , just to let you know, the mentioned case currently has the status "work-in-progress" - either the documentation was more on the arcane side of things or the virtual dog ate it 😉

If there comes an update with actual infos I´ll post here again, it seems however that as per all infos available today there is no equivalent for the fetch size when it comes to the ODBC driver.

Will keep you posted.

Cheers,
Malte

MiguelBer
Padawan

Thanks for coming back to me. Yeah, let me know if you manage to find something, I really appreciate it

exa-Chris
Community Manager
Community Manager

Aaaargh, let me check...

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

exa-Chris
Community Manager
Community Manager

Hi Miguel,
I think I saw a similar conversation internally. Let me check and get someone to answer this. Unless @mwellbro knows this, as he knows everything 😁
Regards Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...