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.
