I need to build a predicate expression with a range of values using CompareRange, but my list of values may have more than 1000 items. I know that LLBLGen will create an "IN" WHERE clause, but Oracle has a limitation on the maximum number of expressions in a list (ORA-01795 error). The list cannot be bigger than 1000 items. Does LLBLGen handle this?

Thanks.

That's not a good idea. Each value in the range is passed as a parameter. That query will have 1000 parameters in your case, which is incredibly slow.

I think you should use a different approach. The set compared with the IN operator, is that data also available in the database? If so, could you specify criteria to define that set? If that's the case you can specify a FieldCompareSetPredicate and perform the query using that. Passing 1000 values in an IN range is not a good idea for performance.

Here is what I need to accomplish and maybe you could help me to find a more efficient way of doing this in LLBLGen:

I have an Animal entity that is associated with a Location entity. Each animal resides in a certain location. The Location is a hierarchical structure - each location may have a parent location (Site - Building - Floor – Room – Sub-room). The users should be able to filter animals not just by a specific location, but also by a branch, for ex. they want to get all animals located in all rooms and sub-rooms of the Floor1.

I was going to build the ID list of all locations that are sub-locations of the specified location and set this list in the predicate expression.

Could you come up with a more efficient way of doing this?

Thanks.

alex wrote:

Here is what I need to accomplish and maybe you could help me to find a more efficient way of doing this in LLBLGen:

I have an Animal entity that is associated with a Location entity. Each animal resides in a certain location. The Location is a hierarchical structure - each location may have a parent location (Site - Building - Floor – Room – Sub-room). The users should be able to filter animals not just by a specific location, but also by a branch, for ex. they want to get all animals located in all rooms and sub-rooms of the Floor1.

I was going to build the ID list of all locations that are sub-locations of the specified location and set this list in the predicate expression.

Ah, the classic hierarchy in one table problem (the original is the all famous Employees table setup everybody remembers from the books which has a 'Supervisor' field pointing to self.

There are a couple of solutions.

One is the easiest to apply to your current setup. You have a fixed hierarchy and you can then use several subquery (fieldcompareset) predicates to retrieve the data. This is though very awful, as it will break the minute you add a layer to your hierarchy. Example: SELECT * FROM Animal WHERE LocationID IN (select LocationID from Location WHERE Type='Floor' AND name ='Floor1') OR (select LocationID from Location WHERE Type='Room' AND ParentID IN (SELECT LocationID FROM Location WHERE name Type='Floor' AND name='Floor1') OR etc...

This is very slow. Not recommended

Another solution is to use a 'precalc' table. This solution is what I use in our CMS which drives llblgen.com. That CMS has 'categories', to which you can add an item. To find all parents of a given category, I made a table which for each category contains all parents in the path to the root. This means that if I have a given category, I can fetch with 1 select all childs and childs of childs etc. and sort them by hierarchy as well.

The table is filled by a trigger, which updates the table when the categories table is modified (a row is modified/deleted/added)

This then could greatly enhance your performance as the query to fetch all animals from a given floor is very simple.

A 3rd way to do this is by using the Joe Celko method: click here This 3rd way is the most performant and doesn't require a second precalc table, however it requires you to re-store the data in a different way, and inserting a row can be a bit slower than others.

Option #1 will not work for us. I used Location with a fixed number of levels just as an example. We have other hierarchical structures with unlimited number of levels. I understand option #2, but we will have to maintain an additional table for each hierarchical table. We're trying to avoid triggers, because we need to support multiple databases, not just Oracle, and we don't want to write triggers for each database. With option #3 I need to spend more time and try to understand it.

But it's good to have options Thank you very much for your help.

Indeed, you need an extra routine for maintenance in option 2. Option 3 is really the best option if you ask me. Option 2 works really well though, but indeed it's some extra maintenance.