[Sqlrelay-discussion] Scrollable cursors?
Brought to you by:
mused
|
From: Cal H. <ca...@fb...> - 2010-04-22 21:47:19
|
Hello everyone,
In experimenting with porting my DB2 CLI application over to SQL Relay's
API, I haven't found how to implement scrollable cursors... but I believe I
found a workaround, and I'm curious why it appears to be faster than
cursors.
Here's a little snippet of what I'm doing with DB2:
// set cursor type to static.
SQLSetStmtAttr( *stmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC,
0 );
SQLExecute( *stmt );
SQLFetchScroll( *stmt, SQL_FETCH_ABSOLUTE, firstfetchoffset );
// after the first fetch, I call:
SQLFetch( *stmt );
Selecting any given 5 rows out of a 40,000 row result set takes around 300 -
400ms with this method.
To implement a similar behavior, I hacked this test up in about 5 minutes.
This throws out result set buffers until it reaches row 30000, then fetches
5 rows and breaks out.
cur->setResultSetBufferSize(100);
cur->sendQuery("select id from logon");
int zoom = 30000 / 100;
for ( unsigned int i = 0 ; i < 30000 ; i += zoom )
{
if ( (tmp = (char *)cur->getField(i, (uint32_t)0)) != NULL )
printf("skip %d: %s\n", i, tmp);
else
break;
}
for ( unsigned int i = 29999 ; i < 30005 ; i++ )
{
if ( tmp = (char *)cur->getField(i, "id") )
printf("skip %d: %s\n", i, tmp);
}
This process seems to work fine, and happens in about 50ms! I guess I'm
just curious how this is happening internally, and how seeking through the
result set in this manner is so much faster than a static cursor. (And
also if there is a better method to accomplish this)
Thanks for your time!
--Cal
|