Re: [Sqlrelay-discussion] Scrollable cursors?
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Scrollable cursors?
|
From: David M. <dav...@fi...> - 2010-04-23 15:39:44
|
In the example you used, the SQL Relay connection daemon runs the query,
but doesn't fetch any rows immediately. When the getField() method is
called, the connection daemon calls SQLFetchScroll over and over until
it reaches the requested row. However, since the
SQL_ATTR_ROW_ARRAY_SIZE is set
to 10, it fetches these rows from the db2 server in groups of 10,
reducing the number of round-trips to the DB. The rows are also fetched
into pre-allocated, reused buffers, so there's no memory allocation
penalty. when the requested row is reached, since
setResultSetBufferSize() is set to 100, the next 100 rows are sent in a
single round trip from the SQL Relay server to the client. Like the
skipped rows, those rows are fetched from the DB in groups of 10 as
well. The client side uses an optimized buffer allocation strategy as
well to store the result set.
So that's what's going on. Why that yields such a speed improvement is
another question. I implemented SQL Relay taking advantage of as many
optimizations as I could think of. It could be just a result of that.
It's possible that highly optimized, native DB2 code could perform as well.
My guess would be that the most significant speed improvements are due
to the multi-row fetching (10 at a time) between the DB and SQL Relay
connection daemon, which results in fewer round trips to the DB and the
multi-row fetching (100 at a time) between the SQL Relay server and
client, which results in fewer round trips there. Between the two,
everything runs faster.
There might even be further tuning that could be done. A larger result
set buffer size reduces the number of round trips to fetch rows, which
speeds things up, but requires more memory to be allocated on the client
side, which slows things down. As such, for each query there's a
speed-optimal result set buffer size. You could determine it by writing
a program to run the same query over and over with different result set
buffer sizes, fetch the same row each time and collect speed stats.
David Muse
dav...@fi...
On 04/22/2010 05:55 PM, Cal Heldenbrand wrote:
> Oh, I see now that I don't have to seek through results. Calling
> getField(29999, "id") seems to work just fine, giving me the 30000'th
> record in the result set. Still, this seems pretty magical, I'm
> curious what's happening here!
>
> Thanks,
>
> --Cal
>
> On Thu, Apr 22, 2010 at 4:46 PM, Cal Heldenbrand <ca...@fb...
> <mailto:ca...@fb...>> wrote:
>
> 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
>
>
>
>
> ------------------------------------------------------------------------------
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> http://www.doteasy.com
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> http://www.doteasy.com
_______________________________________________________
Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
http://www.doteasy.com |
