Array Binding and Array Fetching in ODBC Connects

Posted on August 29, 2011 by Yuwei Li in ObjectStudio, Windows-CentricCincom, Cincom ObjectStudio, Cincom Smalltalk, Cincom VisualWorks, ODBC, SQL

Introduction

Some databases and their ODBC drivers allow clients to control the number of rows that will be physically transferred between the server and the client in one logical bind or fetch. These features are called array binding and array fetching. Using array binding and array fetching can greatly improve the performance of many applications by trading buffer space for time (network traffic).

From Cincom® ObjectStudio® 8.4 and Cincom® VisualWorks® 7.9, Cincom starts to support array binding and array fetching in ODBC connects.

Technical Background

Since not all of the ODBC drivers from the database vendors are implemented in the same, when using array binding and array fetching, you may see different performance gains against different databases.

Also, while doing the development, we’ve found that there are bugs in numerous ODBC drivers. SQL Server’s early ODBC drivers have problems in processing LOBs that are more than 400KB in array binding. Only the SQL Native Client coming from SQL Server 2008—but not the normal ODBC driver—works OK with LOBs that are bigger than 400K. DB2’s early ODBC drivers also have problems when binding arrays of LOBs at execution time, but the ODBC driver coming with DB2 9.7 works fine. Sybase’s ODBC driver doesn’t work with binding array LOBs, but it works when binding non-array LOBs. Therefore, if you plan to use these features, we recommend that you always get the latest ODBC drivers from the database vendors.

There are behavioral differences among the ODBC drivers too, especially when binding arrays of Large Objects (LOBs) at execution time. For example, if binding multiple arrays of LOBs, SQL Server insists that you send the data row by row. For example, if you bind two arrays of LOBs, first is a CLOB array and second is a BLOB array. SQL Server wants you to send the first row, a CLOB and a BLOB, and then the second row, and so on. However, Oracle wants you to send data column by column. So, in the previous example, to feed the LOB data to Oracle, you have to send all of the CLOB data first, and then the BLOB data. DB2 seems to work in the same way as SQL Server. There are also differences when dealing with NULL data. SQL Server and DB2 get the information at binding time, but Oracle wants you send the NULL data at execution time.

In general, ODBC has some limitations on fetching arrays of LOB data.

LOB data is large, so we normally use SQLGetData to get it instead of binding buffers. However, the function SQLGetData we use has some special requirements. First, it can only be used after all of the bound columns are done. Secondly, it can’t be called if the rowset size is greater than 1. This means that if the select list includes LOB columns, we have to always set the blockFactor to 1, and the LOB data columns have to appear after the normal data columns in the select list.

Some Implementation Considerations

Based on what we’ve learned from the array binding feature in our OracleEXDI, we’ve included several features to improve the reliability, flexibility and performance of our ODBC connects.

First, when binding arrays of Strings and ByteArrays, make sure that big enough buffers for holding the largest ones are allocated.

Second is to make smart usage of the allocated memory. For example, when reusing a session, binding an element and then binding an array of one element doesn’t have to force a free and reallocation of the buffer and vice versa. Binding a long array first and then binding a shorter one, the allocated buffer can be reused as well. However, binding a short array first and rebinding a longer array does force a reallocation of the buffer.

Third is that when dealing with binding arrays of different sizes to different host variables, find the longest one and fill the short ones with nils so that they’ll be the same size. This way, we don’t have to require users to only bind arrays with the same size.

In the following sections, we’ll demonstrate the usage of array binding and array fetching in VisualWorks and ObjectStudio.

Array Binding and Array Fetching in VisualWorks ODBCEXDI

Array Binding and Array Fetching Code Examples for VisualWorks

The following are the VisualWorks code examples for using array binding and array fetching in ODBCEXDI (the server is SQL Server 2008):

"Tests against SQL Server 2008."
"Array Binding example."
conn := ODBCConnection new.
conn username: 'username';
password: 'password';
environment: 'SQLServerNativeClientDSN'.
conn connect. 

"Drop the test table if existed."
sess := conn getSession.
sess prepare:  'drop table test_array_binding'.
sess execute.
sess answer. 

"Create a test table with multiple columns of different data types."
sess := conn getSession.
sess prepare:  'create table test_array_binding (cid int, cf float,
cbigInt bigint, cvc varchar(100), cc char(100), cbit bit, cm money,
cdatetime datetime, cbin binary(100), cvb varbinary(100),
cb varbinary(max), ct varchar(max))'.
sess execute.
sess answer. 

"Create lists of binding values.  Please note, they don’t
always have the same size."
listInt := Array with: 1 with: 2 with: 3.
listFloat := Array with: 1.2 with: 2.34 with: 3.456.
listBigInt := Array with: -9223372036854775808 with:
9223372036854775807 with: 1234567890123456789.
listVarchar := Array with: 'test1234567' with: 'test12345678'.
listChar := Array with: 'test1234567' with: 'test12345678'.
listBit := Array with: true with: false.
listMoney := Array with: 2.56 with: 3.42.
ts := Timestamp now.
listDateTime := Array with: ts with: ts.
listBibary := Array with: (ByteArray new: 99 withAll:
1) with: (ByteArray new: 95 withAll: 0).
listVarBibary := Array with: (ByteArray new: 99 withAll:
1) with: (ByteArray new: 95 withAll: 0).
RS := ByteArray new: 1444096 withAll: 1.
RS1 := ByteArray new: 444096 withAll: 0.
listBlob := Array with: RS with:nil with: RS1.
TX := String new: 444096 withAll: $a.
TX1 := String new: 1444096 withAll: $b.
listClob := Array with: TX with: TX1 with: nil. 

"Add the lists of binding values to a single binding list."
bindList := Array new: 12.
bindList at: 1 put: listInt.
bindList at: 2 put: listFloat.
bindList at: 3 put: listBigInt.
bindList at: 4 put: listVarchar.
bindList at: 5 put: listChar.
bindList at: 6 put: listBit.
bindList at: 7 put: listMoney.
bindList at: 8 put: listDateTime.
bindList at: 9 put: listBibary.
bindList at: 10 put: listVarBibary.
bindList at: 11 put: listBlob.
bindList at: 12 put: listClob.

"Insert the binding list into the SQL Server database."
sess := conn getSession.
sess prepare:  'INSERT INTO test_array_binding
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'.
sess bindInput: bindList.
sess execute.
sess answer.

"Array Fetching examples."
"Example1: Array fetching works since the select list
does not contain LOB columns."
sess := conn getSession.

"The default blockFactor is 1."
sess blockFactor: 3.
sess prepare: 'select cid, cf, cbigInt, cvc varchar, cc char,
cbit, cm, cdatetime, cbin, cvb from test_array_binding'.
sess execute.
ansStrm := sess answer upToEnd. 

"Example 2: blockFactor will be set back to 1 since the
select list contains LOB columns."
sess := conn getSession.
sess blockFactor: 3.
sess prepare: 'select * from test_array_binding'.
sess execute.
ansStrm := sess answer upToEnd.

Array Binding and Array Fetching in ObjectStudio’s ODBC Wrapper

Before the change, ObjectStudio’s ODBC wrapper lacks the buffer management required by array fetching. It also doesn’t bind columns when fetching data; it simply uses a single piece of buffer (using SQLGetData) to fetch data for all data types.

The first step when adding array fetching is to change how the retrieval works. Now it retrieves data through binding instead of getting them one by one. In the long run, this change is necessary and well worth the effort because it makes adding array fetching possible and provides us with what we definitely need to improve performance.

Array Binding and Array Fetching Code Examples for ObjectStudio

The following are the code example of using array-binding and array-fetching against SQL Server 2008.

"Tests against SQL Server 2008."
"Array Binding example."
"Logon to the database server."
ODBCDatabase logOnServer: #SQLServerNativeClientDSN
user: #username password: #password.

"Get access to the database needed."
db := ODBCDatabase accessName: # SQLServerNativeClientDSN.

"Drop the test table if existed."
db execSql: 'DROP TABLE test_array_binding'.

"Create a test table with multiple columns of different data types."
res := db execSql: 'create table test_array_binding (cid int, cf float,
cbigInt bigint, cvc varchar(100), cc char(100), cbit bit, cm money,
cdatetime datetime, cbin binary(100), cvb varbinary(100), cb varbinary(max),
ct varchar(max))'.

"Create lists of binding values.  Please note, they don’t always have the same size."
listInt := Array with: 1 with: 2 with: 3.
listFloat := Array with: 1.2 with: 2.34 with: 3.456.
listBigInt := Array with: -9223372036854775808 with: 9223372036854775807 with: 1234567890123456789.
listVarchar := Array with: 'test1234567' with: 'test12345678'.
listChar := Array with: 'test1234567' with: 'test12345678'.
listBit := Array with: true with: false.
listMoney := Array with: 2.56 with: 3.42.
ts := Timestamp now.
listDateTime := Array with: ts with: ts.
listBibary := Array with: (ByteArray new: 99 withAll: 1) with: (ByteArray new: 95 withAll: 0).
listVarBibary := Array with: (ByteArray new: 99 withAll: 1) with: (ByteArray new: 95 withAll: 0).
RS := ByteArray new: 1444096 withAll: 1.
RS1 := ByteArray new: 4096 withAll: 0.
listBlob := Array with: RS with:nil with: RS1.
TX := String new: 1444096 withAll: $a.
TX1 := String new: 4096 withAll: $b.
listClob := Array with: TX with: TX1 with: nil.

"Add the lists of binding values to a single binding list."
bindList := Array new: 12.
bindList at: 1 put: listInt.
bindList at: 2 put: listFloat.
bindList at: 3 put: listBigInt.
bindList at: 4 put: listVarchar.
bindList at: 5 put: listChar.
bindList at: 6 put: listBit.
bindList at: 7 put: listMoney.
bindList at: 8 put: listDateTime.
bindList at: 9 put: listBibary.
bindList at: 10 put: listVarBibary.
bindList at: 11 put: listBlob.
bindList at: 12 put: listClob.

"SQL string used to do the insertion."
sqlString :=  'INSERT INTO test_array_binding
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'.

"Insert the binding list into the SQL Server database."
db execSql: sqlString vars: bindList.

"Array Fetching examples."
"The default arrayFetchSize is 1."
db arrayFetchSize: 1.

"SQL string used to fetch records."
sqlString :=  'select * from test_array_binding '.

"Do the fetch."
recs := db execSql: sqlString.

"Set  arrayFetchSize to 3."
db arrayFetchSize: 3.

"Do the fetch."
recs := db execSql: sqlString.

Performance Measurements

We’ve done quite a few performance measurements against different databases and against local and remote servers.

As we mentioned earlier, not all ODBC drivers from different database vendors are implemented in the same way, and depending on how you use the array binding and array fetching features (e.g., array size, blockFactor, and so on), performance gains against different databases seem to vary—sometimes a lot.

Based upon our tests, if used appropriately, using array binding and array fetching can improve performance significantly—sometimes a few hundred times faster than without using them. The performance gains against some databases are obvious while others are not. However, in general, array binding and array fetching does help improve performance.