Vertica, ResultBufferSize has not effect

Question

I'm trying to test the field: ResultBufferSize when working with Vertica 7.2.3 using ODBC. From my understanding this field should effect the result set. ResultBufferSize

but even with value 1 I get 20K results.

Anyway to make it work?

Answer 1

ResultBufferSize is the size of the result buffer configured at the ODBC data source. Not at runtime. You get the actual size of a fetched buffer by preparing the SQL statement - SQLPrepare(), counting the result columns - SQLNumResultCols(), and then, for each found column, running SQLDescribe() . Good luck - Marco

Answer 2

I need to add a whole other answer to your comment, Tsahi. I'm not completely sure if I still misunderstand you, though. Maybe clarifying how I do it in an ODBC based SQL interpreter sheds some light on the matter.

1. SQLPrepare() on a string containing, say, "SELECT * FROM foo", returns SQL_SUCCESS, and the passed statement handle becomes valid.

2. SQLNumResultCols(&stmt,&colcount) on that statement handle returns the number of columns in its second parameter.

3. In a for loop from 0 to (colcount-1), I call SQLDescribeCol(), to get, among other things, the size of the column - that's how many bytes I'd have to allocate to fetch the biggest possible occurrence for that column.

4. I allocate enough memory to be able to fetch a block of rows instead of just one row in a subsequent SQLFetchScroll() call. For example, a block of 10,000 rows. For this, I need to allocate, for each column in colcount, 10,000 times the maximum possible fetchable size. Plus a two-byte integer for the Null indicator for each column. These two : data area allocated and null indicator area allocated, for 10,000 rows in my example, make the fetch buffer size, in other words, the result buffer size.

5. For the prepared statement handle, I call a SQLSetStmtAttr() to set SQL_ATTR_ROW_ARRAY_SIZE to 10,000 rows.

6. SQLFetchScroll() will return either 10,000 rows in one call, or, if the table foo contains fewer rows, all rows in foo.

This is how I understand it to work.

You can do the maths the other way round:

You set the max fetch buffer.

You prepare and describe the statement and columns as explained above.

For each column, you count two bytes for the null indicator, and the maximum possible fetch size as from SQLDescribeCol(), to get the sum of bytes for one row that need to be allocated.

You integer divide the max fetch buffer by the sum of bytes for one row.

And you use that integer divide result for the call of SQLSetStmtAttr() to set SQL_ATTR_ROW_ARRAY_SIZE.

Hope it makes some sense ...