Re: [Sqlrelay-discussion] Scrollable cursors?
Brought to you by:
mused
|
From: Cal H. <ca...@fb...> - 2010-04-23 17:28:54
|
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 > > |