Hi,

we're wondering would it be possible to implement customized prefetch query generation?

Currently there are two flavors: 1) a sub query when the number of items is higher than the ParameterisedPrefetchPathThreshold 2) WHERE IN (: p1 ,: p2 ,: p3 ... ) when the number of items is below ParameterisedPrefetchPathThreshold

What we'd like to do is limit the number of variations because of memory usage on the database.

One way would be to 'stretch' the number of to a specific number of items, lets say: 50, even we only have 10 ... and add a between clause when the items are ordered

e.g.

SELECT id, column1, column2 
FROM db_owner.my_table mt
WHERE mt.id BETWEEN :p1 AND :p5
AND mt.id IN (p1, p2, p3, p4, p5, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6 ... etc)

Another way would be to use a table valued function:

SELECT id, column1, column2 
FROM db_owner.my_table mt
WHERE mt.id BETWEEN :p1 AND :p5
AND mt.id IN TABLE(db_owner.idtable(:p1))

In our case performance is less of a concern than memory usage on the dbms.

Would it be possible to use a custom implementation for prefetching?

SELECT id, column1, column2 
FROM db_owner.my_table mt
WHERE mt.id BETWEEN :p1 AND :p5
AND mt.id IN (p1, p2, p3, p4, p5, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6, p6 ... etc)

In the first case, you are only adding the between predicate to the usual prefetch predicate, correct?

In this case, you can add that custom filter to the prefetchPath construct. There is an overload to the PrefetchPath.Add() method that accepts an additional PredicateExpression.

public IPrefetchPathElement Add(
    IPrefetchPathElement elementToAdd,
    int maxAmountOfItemsToReturn,
    IPredicateExpression additionalFilter
)

Ah thanks, didn't know of the overload.

Option 1 solved.

Now we only need to solve option 2

Because the issue is that because of the huge amount of different parameters ... could be anywhere between 1 and 3000 ... we'd like Oracle to receive only one sql statement with a string parameter that goes into a table valued function that splits the comma separated string. As otherwise every variation would get it's own parse tree and execution plan which results in library cache contention because of the sheer amount of queries.

I'm aware that it might not be the best way to go in all/most cases and normally we should make different design choices. But we're stuck with A LOT of older code that needs to get memory optimized in short period. Rewriting everything is just not an option.

Not sure which version you're using, in v5.x we filter out duplicates in the values passed to the IN clause so that should mitigate things a bit already, but indeed if there are still a lot of values, it could lead to memory churn.

Would it help to lower the threshold to a lower value than 3000? (you probably already tried this, but as you say, performance is less of a concern, it might be a better trade off).

The thing is that we use an internal pipeline to produce the predicate for the fetch which isn't interceptable. So it's not easy to simply grab the predicate produced for the prefetch path sub node. There is a way, but it's low level (assuming you use adapter)

You can create a partial class of your DataAccessAdapter and add a boolean to that to make sure it should perform the action on the passed in data. Then override CreateSelectDQ in your partial class. If the flag you added is false, just call the base method, and you're done (the current behavior). If the flag is true, you should change the QueryParameters object passed in.

the first call is for the outer set, so the root node of the path. Every subsequential call is for a node in the tree. the QueryParameters object is documented here: https://www.llblgen.com/Documentation/5.6/ReferenceManuals/LLBLGenProRTF/html/44A5D55D.htm

You have to traverse the FilterToUse to find the FieldCompareRangePredicate. the easiest way to do that is to create a derived class of QueryApiObjectTraverser, which is a visitor base class. Just call Traverse by passing the object to traverse, and it will traverse all objects and call the handle methods of the type of the element. You're interested in a Predicate so you should override public virtual void Traverse(IPredicate objectToTraverse) and public virtual void TraversePredicateExpression(IPredicateExpression objectToTraverse)

The visitor isn't designed to replace elements, but to find elements, and what you want is to replace the FieldCompareRange predicate with a custom IPredicate implementation you create yourself and which emits the table clause with the string instead. (the best way to do this is to check the sourcecode of the runtime libraries, available on the website, they're all in QueryApiElements). So to replace an element you have to do that in the TraversePredicateExpression and check ahead there.

Thank you for your response. We're using version 4.2 and up ... up to 5.6 (different projects, different versions). We might want to upgrade soon ... as we're upgrading our the version of our database as well.

I will try to do what you suggested and come back to you ... might take a day or two though

which isn't interceptable

Well in theory it isn't ... but there are ways to inject a new method implementation But lets not do that in production code

You might think about changing the runtime code too, if that's the last option, you're allowed to do that. Might be a support burden in the end (although this part of the pipeline is pretty stable)