Re: [Sqlrelay-discussion] Scrollable cursors?
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Scrollable cursors?
|
From: Cal H. <ca...@fb...> - 2010-04-23 16:00:33
|
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 > > |
