addIn() differences between Oracle and Sybase

[Danilo Tommasina <dt...@risksys.com>]

Hi all,

I noticed a problem with the IN clause...

As shown in the 'Query' documentation if the SqlInLimit property is 
exceeded a Criteria will be split in a OR concatenated list of INs. 
That's really cool, however this works in Oracle (however with a 
limitiation of 1000 ORs :) ), but in the (@#?!!@***#) Sybase this will 
not work, because independently if the INs are split or not, the total 
limit will remain 250.

I got a look to the code (RC4) and it is really done so for every db.

Prefetched ralationships also split IN criterias, however they generate 
  different Queries and put all results together before returning them.

Now the first (Criteria) variant offers better performance, however is 
useless on Sybase.
The second one (prefetched relationship) offers less performance but 
would also allow Sybase to handle more than 250 IN elements, however 
managing the StartAtIndex and EndAtIndex will need some more effort, 
involving the use of COUNT() operations on each query that has been 
split furtherly reducing performance :(

It could be useful to introduce a property flag like 
'SplitQueriesOnSqlInLimit' to force OJB splitting IN Criterias in 
several queries for databases that need it.

Or do you have a better idea?

I'd like to patch it myself, however I am already under heavy time 
pressure on our current project, so it would be great if somebody of the 
good guys could get a look at it. ;)

let me know, bye
danilo



---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org

[update] addIn() differences between Oracle and Sybase

[Danilo Tommasina <dt...@risksys.com>]

Hi everybody,

uh, uh, little updtate to my previous post.

I tried to patch OJB to support Sybase IN limitations, the problem is 
that sybase considers an IN statement as a sequence of ORs and there is 
a maximum of 250 ORs/ANDs for a single statement. This means that the 
sum of the elements in all INs plus all ORs/ANDs must be lower or equal 
than 250. So my idea was to split the main query in several queries so 
that the total number of logical operators per query will remain lower 
than (SQLInLimit) 250, however if there is only one IN statement or 
several OR 'connected' INs this is not a problem. But what about AND 
'connected' INs or several INs in joined tables? I see no solution...

Maybe it is not a bad idea to put a little commentary in the doc about 
this issue...

Has somebody an idea if it is possible to increment this limit in Sybase 
(how?)? Or a workaround other than using temporary tables?

thx
bye danilo


> Hi all,
> 
> I noticed a problem with the IN clause...
> 
> As shown in the 'Query' documentation if the SqlInLimit property is 
> exceeded a Criteria will be split in a OR concatenated list of INs. 
> That's really cool, however this works in Oracle (however with a 
> limitiation of 1000 ORs :) ), but in the (@#?!!@***#) Sybase this will 
> not work, because independently if the INs are split or not, the total 
> limit will remain 250.
> 
> I got a look to the code (RC4) and it is really done so for every db.
> 
> Prefetched ralationships also split IN criterias, however they generate 
>  different Queries and put all results together before returning them.
> 
> Now the first (Criteria) variant offers better performance, however is 
> useless on Sybase.
> The second one (prefetched relationship) offers less performance but 
> would also allow Sybase to handle more than 250 IN elements, however 
> managing the StartAtIndex and EndAtIndex will need some more effort, 
> involving the use of COUNT() operations on each query that has been 
> split furtherly reducing performance :(
> 
> It could be useful to introduce a property flag like 
> 'SplitQueriesOnSqlInLimit' to force OJB splitting IN Criterias in 
> several queries for databases that need it.
> 
> Or do you have a better idea?
> 
> I'd like to patch it myself, however I am already under heavy time 
> pressure on our current project, so it would be great if somebody of the 
> good guys could get a look at it. ;)
> 
> let me know, bye
> danilo
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org