c++ - How can I improve the performance of SQLFetchSQLFetchScroll using ODBC Driver 18 for SQL Server in high latency scenarios?

admin管理员组

文章数量:1389293

We have a C++ application that uses ODBC to connect to MS SQL Server and Oracle. We noticed that fetching large amounts of data becomes really slow when the latency between the client where the program is run and the database is high.

Disclaimer: If I don't say so otherwise, I'm talking about ODBC Driver 18 for SQL Server.

So far, we are fetching row by row, not using any bulk cursors. Measuring the time every fetch takes lead me to believe that the ODBC driver has an internal fetch buffer and whenever that buffer is used up, the driver will get the next batch of data from the database. So for example for one table, i measured that roughly every 30th fetch took a long time (latency + some more), while the fetches in between were near instant. So I'm looking for a way to speed up fetching large amounts of data.

Thus I implemented fetching using bulk cursors. For testing purposes, I used a table with just 3 columns, 1 int and 2 strings of length 100, and added 100'000 rows to it. In this table, roughly every 650th fetch caused the latency delay when using single fetches.

But whether I did single row fetches or bulk fetches of 10'000 or even all 100'000 rows at once makes no difference in how long fetching all 100k rows takes (this applies to the Oracle ODBC driver too). The bulk fetches are done using SQLFetchScroll with SQL_FETCH_NEXT, since the driver doesn't follow the specification and doesn't do bulk fetches with SQLFetch even if SQL_ATTR_ROW_BIND_TYPE and SQL_ATTR_ROW_ARRAY_SIZE are set (the Oracle driver properly fetches multiple rows with SQLFetch).

This leads me to believe that bulk fetching still uses the very same internal fetch buffer and doesn't change its size for bulk fetching. Thus bulk fetching with this driver is just some syntactic sugar to fill multiple variables at once but doesn't change anything about how fetching works under the hood.

So the problem now is: what can I do to improve the performance of fetching? Is there a way to increase the size of the fetch buffer and thus reduce the number of times the driver has to contact the database over the network? Or is there anything else I can do?

On a side note: In the Oracle driver you can set the fetch buffer size when you configure the ODBC data source. And while there are diminishing returns, increasing the size from 64k to 640k or 6.4M lowers the time to fetch those 100k records from 28 seconds to 4 resp. 2 seconds in a scenario with roughly 50ms of lag.

本文标签：