Re: [Sqlrelay-discussion] Scrollable cursors?
Brought to you by:
mused
Menu
▾
▴
Re: [Sqlrelay-discussion] Scrollable cursors?
|
From: David M. <dav...@fi...> - 2010-04-23 17:21:52
|
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... <mailto: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... <mailto: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... <mailto: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 > > ------------------------------------------------------------------------------ > > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > <mailto: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 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 |
