Re: [Sqlrelay-discussion] Scrollable cursors?

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Excellent, thank you!

On Fri, Apr 23, 2010 at 12:05 PM, David Muse <dav...@fi...>wrote:

>  There's an item on the todo list for making the server-side fetch-at-once
> size configurable in the sqlrelay.conf file, but not from a client call.
> Not a bad idea though.  I'll add that to the list.
>
> Dave
> dav...@fi...
>
>
> On 04/23/2010 12:00 PM, Cal Heldenbrand wrote:
>
> Hi David, thanks for the info!  That's what I was thinking as well, that
> the prefetch size of 10 was helping here, but I was really surprised at the
> difference.  I'm definitely impressed with your code!
>
> My application is a mix of DB2 CLI calls and SQLAPI<http://www.sqlapi.com>.
> So far, some benchmarking between SQL Relay and SQLAPI is similar for fetch
> speeds, with SQL Relay being just a bit slower for larger queries.  (About
> 10ms slower than SQLAPI, both set to 100 row prefetch with an 8000 row
> query)   I'm guessing the extra network hop is in play there, but the added
> benefit of connection pooling saves me about 30 - 40ms in connecting and
> logging into DB2.
>
> It's difficult to determine what the best setting for FETCH_AT_ONCE is.
> The types of queries across our application vary quite a bit, from single
> row fetches, to massive statistical queries spanning millions of rows.  I've
> found that 10 is a happy medium, but it would be nice to be able to change
> that value from the client API.  (Rather than recompiling/restarting the
> sqlr server)  Is that on your TODO list at all?
>
> Thank you,
>
> --Cal
>
>
> On Fri, Apr 23, 2010 at 10:39 AM, David Muse <dav...@fi...>wrote:
>
>>  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...> 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 lis...@li...://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
>>
>>
>> ------------------------------------------------------------------------------
>>
>> _______________________________________________
>> 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
>
>
> _______________________________________________
> Sqlrelay-discussion mailing lis...@li...://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
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>