Hi James, Nadav,
I believe I'm the guy you were talking about… The code you have shown begin open :1 for select count(*) from a; open :2 for select count(*) from b; end; is the recommended practice, it is called pl/sql anonymous block. I'm not sure why this has shown worse performance, make sure you do not make the measurements in the first execution because it is expected to perform poorly (This is the first time Oracle parses the query). I have had talks with development in order to look into supporting the "SQL Server Syntax", although I'm not sure if this will be included in the next version. Furthermore in order to optimize the OracleDataReader you can increase the OracleCommand.FetchSize, and that will reduce the number of round trips between application and database. (OracleDataReader.Read() will normally cause a number of trips to the server, in order to retrieve the data). You can find more info in this document http://www.oracle.com/technology/oramag/oracle/06-jul/o46odp.html Under Controlling Fetch Size. Regards, Tomer – Oracle support On Mar 24, 9:42 am, James L <[email protected]> wrote: > I will test the performance of your patch before reaching a > conclusion. In the case of my test, I guessed that the number of > network round trips had increased overall. > > If the code going to make it into the trunk, we would need to think > about a limit on the number of ref cursors per batch, or the user who > makes heavy use of Futures could be in for an unpleasant surprise down > the road! > > It would be good to speak to someone from Oracle about the best way > forward. I'm sure they'd want it to run in NHibernate as well as SQL > Server does. There was a guy from Oracle in Israel who showed an > interest in NH a while back, but I haven't been able to get in touch > with him. > > (The idea with the union select is unrelated really, so perhaps we > should start another thread to discuss it. The idea was that the > batcher would detect similar queries in the batch, union them for > execution, and split them up again into separate results lists. In my > example, the first column of the results represented the result set > number.) > > Anyone else have any thoughts on this? > > On Mar 23, 6:27 pm, nadav s <[email protected]> wrote: > > > > > about performance improvements, it differs > > for a project i've worked on we did just that (without NH :( ) and it > > showed some improvements > > In my current project it showed almost no improvement > > i think this is because of a faster connection between the App and the DB in > > the current project setup > > > about limiting the batch size, in our case, using futures means at most 3-4 > > queries batched > > we've consulted our DBAs and with our poor count of concurrent users we > > didn't have to worry about this at all > > > how come you found it slower? do you have any assumption to why it can slow > > your queries? > > > about oracle having another way to batch queries - i'm pretty sure it > > doesn't currently at least > > > and for your idea on batching queries, its a nice idea, the only problem is > > that you'll get back one cursor with all results > > if you need all the results in one list anyway, just use OR in the where > > clause and do it with one query... > > > in that case you showed using OR instead of union will save you alot of IOs > > and will get you the same results as the union would > > > but the whole idea of multi criteria (as i see it) is using > > criteria.Future() to batch queries on different tables > > > On Tue, Mar 23, 2010 at 11:32 AM, James L <[email protected]> wrote: > > > Nadav > > > > Have you measured any performance improvement? My initial findings > > > showed it'd be slower. The other complication is that these cursors > > > are subject to a quota per Oracle instance, so the max batch size > > > would need to be configurable. Perhaps this is why there is currently > > > no support. In any case, I'm trying to discover whether Oracle > > > supports any other means of returning multiple result sets. > > > > Another potential route is to find sets of similar queries and batch > > > them with unions, e.g. > > > SELECT 1, a, b, c FROM table WHERE ? > > > UNION > > > SELECT 2, a, b, c FROM table WHERE ? > > > UNION > > > SELECT 3, a, b, c FROM table WHERE ? > > > > Clearly, this would only help in certain cases, but it would at least > > > work on databases other than Oracle. > > > > - James > > > > On Mar 17, 5:28 pm, nadav s <[email protected]> wrote: > > > > i've actually implemented this in my project and uploaded to jira the > > > code > > > > that does this and i hope the dev team will have it ready for the next > > > > version > > > > > in the mean time, if you want, i can send you my NH compiled DLLs > > > > (haven't changed anything except the code that creates a multi criteria > > > when > > > > the driver is ODP. also ran the unit tests and they were fine). > > > > > On Tue, Mar 16, 2010 at 11:39 AM, James L <[email protected]> wrote: > > > > > When you attempt to use MultiCriteria or MultiQuery against the > > > > > OracleDataClientDriver, you get the exception message "The driver {0} > > > > > does not support multiple queries." This seems like a wasted > > > > > opportunity. > > > > > > If you attempt to override the SupportsMultipleQueries driver > > > > > property, NH will spit out a number of queries like this: > > > > > " > > > > > select count(*) from a; > > > > > select count(*) from b; > > > > > " > > > > > > Oracle doesn't like this syntax. You can however open a reference > > > > > cursor for each result set, like this: > > > > > begin > > > > > open :1 for select count(*) from a; > > > > > open :2 for select count(*) from b; > > > > > end; > > > > > > In this example, you must pass in two output parameters which are of > > > > > type OracleDbType.RefCursor. The returned value is an > > > > > OracleRefCursor, which has a GetDataReader() operation. Going down > > > > > this road seems to be three to four times slower than using individual > > > > > commands, even for 100 queries. I'm surprised it's that bad, but I > > > > > wouldn't expect it to be much quicker as you're still round-tripping > > > > > for each result set. > > > > > > Does anyone know of a way to have Oracle return a set of results > > > > > immediately? Or am I barking up the wrong tree entirely? > > > > > > Thanks- Hide quoted text - > > > > > - Show quoted text - > > > > To unsubscribe from this group, send email to nhibernate-development+ > > > unsubscribegooglegroups.com or reply to this email with the words "REMOVE > > > ME" as the subject.- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - To unsubscribe from this group, send email to nhibernate-development+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
