MSSQL failing on large "in" clause

What can we do when prgCtx.fDbConnect.GetRightsList() returns a large number of values and we need to use the result in a query (using an "in" clause)? I seem to be hitting a limit in MSSQL when the number of items is around 13'000 or greater:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

I haven't tested this on Oracle.

Is there a way to query direct and indirect group memberships without having to fetch all the ids?

Here is a script that simulates the problem with a query of a similar format:

function Void LargeQueryTest()    Integer numberOfItemsInClause = 13000    Object prgCtx = $WebProspector.Utils.GetPrgCtx()    String columnName = 'DataID'    List l    Integer i    for i = 1 to numberOfItemsInClause        l = {@l, i}    end    String clause = $LLIApi.FormatPkg.GetIDsClauseFromList(l, columnName)    String stmt = Str.Format("select * from DTree where %1 and ParentID=:A0", clause)    RecArray recs = CAPI.Exec(prgCtx.fDbConnect.fConnection, stmt, {2000})    if IsError(recs)        Assoc check = prgCtx.fDBConnect.CheckError(recs, false)        echo(check)    endend

Find more posts tagged with

Comments

Hans Stoop

Hi Chris,

You can use ExecInClause instead of Exec.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Donnerstag, 31. August 2017 08:23
To: eLink Recipient
Subject: MSSQL failing on large "in" clause

MSSQL failing on large "in" clause

Posted by Meyer, Chris On 08/31/2017 02:20 AM

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

I haven't test this on Oracle.

Is there a way to query direct and indirect group memberships without having to fetch all the ids?

Here is a script that simulates the problem with a query of a similar format:

function Void LargeQueryTest()

    Integer numberOfItemsInClause = 13000

    Object prgCtx = $WebProspector.Utils.GetPrgCtx()

    String columnName = 'DataID'

    List l

    Integer i

    for i = 1 to numberOfItemsInClause

        l = {@l, i}

end

    String clause = $LLIApi.FormatPkg.GetIDsClauseFromList(l, columnName)

    String stmt = Str.Format("select * from DTree where %1 and ParentID=:A0", clause)

    RecArray recs = CAPI.Exec(prgCtx.fDbConnect.fConnection, stmt, {2000})

    if IsError(recs)

        Assoc check = prgCtx.fDBConnect.CheckError(recs, false)

        echo(check)

end

end

[To post a comment, use the normal reply function]
Forum:	Content Server Development Forum
Content Server:	Knowledge Center CS16

Chris Meyer

Hi Hans,

You can use ExecInClause instead of Exec.

Do you mean prgCtx.fDbConnect.ExecInClauses()? Unfortunately, this doesn't work either. Behind the scenes it calls CAPI.Exec in the same manner as my example.

Here's an example with ExecInClauses:

function Void LargeQueryTest()    Integer numberOfItemsInClause = 13000    Object prgCtx = $WebProspector.Utils.GetPrgCtx()    Assoc a    Integer i    for i = 1 to numberOfItemsInClause        a.(i) = 1    end    String stmt = "select * from DTree where %1 and ParentID=:A0"    Assoc results = prgCtx.fDbConnect.ExecInClauses(stmt, a, 'DataID', {2000})    echo(results)end

Hans Stoop

Hi Chris,

Interesting.

One relevant limitation I found for SQL server:

Length SQL statement:65,536 * Network packet size (default 4Kb)

Didn’t find limitations on the size of the in-clause.

Maybe you run into a OScript string size problem.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Donnerstag, 31. August 2017 14:38
To: eLink Recipient
Subject: RE MSSQL failing on large "in" clause

RE MSSQL failing on large "in" clause

Posted by Meyer, Chris On 08/31/2017 08:36 AM

Hi Hans,

You can use ExecInClause instead of Exec.

Do you mean prgCtx.fDbConnect.ExecInClauses()? Unfortunately, this doesn't work either. Behind the scenes it calls CAPI.Exec in the same manner as my example.

Here's an example with ExecInClauses:

function Void LargeQueryTest()

    Integer numberOfItemsInClause = 13000

    Object prgCtx = $WebProspector.Utils.GetPrgCtx()

    Assoc a

    Integer i

    for i = 1 to numberOfItemsInClause

        a.(i) = 1

end

    String stmt = "select * from DTree where %1 and ParentID=:A0"

    Assoc results = prgCtx.fDbConnect.ExecInClauses(stmt, a, 'DataID', {2000})

end

[To post a comment, use the normal reply function]
Topic:	MSSQL failing on large "in" clause
Forum:	Content Server Development Forum
Content Server:	Knowledge Center CS16

Chris Meyer

I found that replacing the parameter with a hardcoded value fixes the problem.

I.e., replace this:

select * from DTree where %1 and ParentID=:A0

With this:

select * from DTree where %1 and ParentID=2000

This doesn't solve the general problem so I'm still looking for a solution.

I guess my other question stands: Is there a way to query direct and indirect group memberships without having to fetch all the ids? Maybe a "DTreeAncestors"-type equivalency for users and groups?

Hans Stoop

To your other question:

I thought it was implemented, but the tables (KUAFRightsList[New]) I found for that didn’t work for me.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Donnerstag, 31. August 2017 16:57
To: eLink Recipient
Subject: RE RE MSSQL failing on large "in" clause

RE RE MSSQL failing on large "in" clause

Posted by Meyer, Chris On 08/31/2017 10:52 AM

I found that replacing the parameter with a hardcoded value fixes the problem.

I.e., replace this:

select * from DTree where %1 and ParentID=:A0

With this:

select * from DTree where %1 and ParentID=2000

This doesn't solve the general problem so I'm still looking for a solution.

I guess my other question stands: Is there a way to query direct and indirect group memberships without having to fetch all the ids? Maybe a "DTreeAncestors"-type equivalency for users and groups?

[To post a comment, use the normal reply function]
Topic:	MSSQL failing on large "in" clause
Forum:	Content Server Development Forum
Content Server:	Knowledge Center CS16

David Templeton

Chris

I am trying to understand what you are trying to achieve. In general, trying to retrieve large sets from the DB is simply bad, there is no way to make it scale and can lead to DoS vectors. This why things like pagination were added to the system for browse for instance.

If you are wanting to permission check a set of items then call the utility function $LLIAPI.NodeUtil.AppendPermissionsToRec that will perform the correct operation including all permission callbacks to ensure the proper security behavior. In turn you are abstracted from the database which we will not guarantee any external contract on the implementation since the system is evolving and schema is subject to change

/**

* Add the 'Permissions' column to a recarray. The permissons

* values will be filled in based on the DataID and PermID columns.

* This method drops node records that the specified user (userID) does not have permission (mask) to.

* Hence this method can be used to check whether a user has permission to specific nodes.

* @param {Object} dapiCtx The DapiSession object.

* @param {RecArray} recs The Node recs to expand.

* @param {Integer} mask The permissions mask. The node recs are filtered by this mask.

* @param {Integer} userID A specific user if it is defined, instead of the current user.

* @return {Assoc}

* @returnFeature {Boolean} OK TRUE if ok.

* @returnFeature {String} ErrMsg Application error message if not ok.

* @returnFeature {Dynamic} ApiError Error returned by API if not ok.

* @returnFeature {RecArray} Recs A recarray with the added permissions column.

It should be noted that it is not unusual to have 100K+ rights for a user

Regards

David

sıɹɥɔ

Hi Chris,

There’s an additional parameter toExecInClauses; if you pass FALSE for it then I expect this will resolve the problem you encountered.

(As I’m sure you’d discover, the underlying issue is deep in a db library – perhaps ODBC? -- but the above workaround is likely more than sufficient.)

-chris

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Thursday, August 31, 2017 5:38 AM
To: eLink Recipient
Subject: RE MSSQL failing on large "in" clause

RE MSSQL failing on large "in" clause

Posted by Meyer, Chris On 08/31/2017 08:36 AM

Hi Hans,

You can use ExecInClause instead of Exec.

Do you mean prgCtx.fDbConnect.ExecInClauses()? Unfortunately, this doesn't work either. Behind the scenes it callsCAPI.Exec in the same manner as my example.

Here's an example with ExecInClauses:

function Void LargeQueryTest()

    Integer numberOfItemsInClause = 13000

    Object prgCtx = $WebProspector.Utils.GetPrgCtx()

    Assoc a

    Integer i

    for i = 1 to numberOfItemsInClause

        a.(i) = 1

end

    String stmt = "select * from DTree where %1 and ParentID=:A0"

    Assoc results = prgCtx.fDbConnect.ExecInClauses(stmt, a, 'DataID', {2000})

    echo(results)

end

Chris Meyer

Hi David,

This is for a custom module. An analogous query is as follows:

Find all documents where I'm a member of the default group irrespective of permissions.

The query looks like this:

select * from DTree where SubType=:A0 and GroupID in (<all group memberships>)

At the moment <all group memberships> is resolved using the GetRightsList() function, which returns a list of group ids.

In general, trying to retrieve large sets from the DB is simply bad, there is no way to make it scale and can lead to DoS vectors.

I agree. This is why I ask if there is another way to write this (with a subquery or join?) akin to using DTreeAncestors for querying a DTree hierarchy.

If you are wanting to permission check a set of items then call the utility function $LLIAPI.NodeUtil.AppendPermissionsToRec that will perform the correct operation including all permission callbacks to ensure the proper security behavior.

This isn't related to my problem per se, but I'll highlight that AppendPermissionsToRec() calls dbConnect.GetRightsString() in the same manner to what I describe above. This generates a where clause with all group memberships. e.g., (RightID in (-1,394435,1001,...)).

Chris Meyer

Hi Chris,

There’s an additional parameter to ExecInClauses; if you pass FALSE for it then I expect this will resolve the problem you encountered.

This works! Thanks. Once I traced the function it became clear this is the way to do it. The problem I have now is automating it such that I don't have to treat such queries as an exception.

As I’m sure you’d discover, the underlying issue is deep in a db library – perhaps ODBC?

Indeed it isn't a CS error per se, so I was hoping to find an alternative approach (e.g., something akin to DTreeAncestors). I have some more work to do...

Hans Stoop

Hi Chris,

The recommended way is to create a temporary table, store the data there and then make a join over the temporary table.

Never done this.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Freitag, 1. September 2017 11:54
To: eLink Recipient
Subject: RE RE MSSQL failing on large "in" clause 2

RE RE MSSQL failing on large "in" clause 2

Posted by Meyer, Chris On 09/01/2017 05:53 AM

Hi Chris,

There’s an additional parameter to ExecInClauses; if you pass FALSE for it then I expect this will resolve the problem you encountered.

This works! Thanks. Once I traced the function it became clear this is the way to do it. The problem I have now is automating it such that I don't have to treat such queries as an exception.

As I’m sure you’d discover, the underlying issue is deep in a db library – perhaps ODBC?

Indeed it isn't a CS error per se, so I was hoping to find an alternative approach (e.g., something akin toDTreeAncestors). I have some more work to do...

[To post a comment, use the normal reply function]
Topic:	MSSQL failing on large "in" clause
Forum:	Content Server Development Forum
Content Server:	Knowledge Center CS16

Chris Meyer

Hi Hans,

The recommended way is to create a temporary table, store the data there and then make a join over the temporary table.
Never done this.

Me neither.

I'm thinking about creating a custom table for managing this. Then use it like this:

call GetRightsList() (which I believe is cached in memory)
hash the results
if the hash has changed since last updating the table then:
- purge the stale data and refresh with the new data
run the query by joining with the table

I ran Security.Hash() on a list with 15'000 items and it was more or less instant.

The other solution is to do something like ExecInClauses, but this comes with a loss of generality in the way I do things.

Chris Meyer

Hi David,

I reread this thread and believe we were talking about slightly different things. Let me recap.

In general, trying to retrieve large sets from the DB is simply bad, there is no way to make it scale and can lead to DoS vectors. This why things like pagination were added to the system for browse for instance.

I completely agree. I have written some classes to abstract querying the database, which includes the three primary actions of filtering, paging, and sorting. This can be used with any table or view in the system. So I write stuff like this:

Frame nodes = $MyModule.TableQuery.New(prgCtx, 'DTree') \                    .filter('subtype', '==', $TypeDocument) \                    .filter('dataid', 'ancestor', 2000) \                    .sort('-modifydate') \                    .setPageSize(25) \                    .setPageNumber(1)

At this point nodes is just a representation. When I call .items() it assembles the underlying query, executes it, and returns the records:

RecArray recs = nodes.items()

The nodes object has some additional functions for paging:

nodes.count() // total number of itemsnodes.hasNext() // true if there is a next pagenodes.pageCount // total number of pages// etc.

I realise now my original question also applies to permission filtering. I have avoided $LLIAPI.NodeUtil.AppendPermissionsToRec due to concerns with scaling (and I have uses outside of permission checking). For example, if a user is on page 2000 then they still need to permission check all items on the previous 1999 pages to accurately render its contents. An informal test shows this to be slow.

For this reason I've wanted to delegate the permission check to the database in the original query. LiveReports does this by injecting the following into the query:

(exists (select DataID from DTreeACL b where b.DataID=ISNULL(DTree.PermID,DTree.DataID) and %1 and See>1))

The %1 parameter is replaced by CAPI.RightsString(), which is that large clause of the form (RightID in (-1,394435,828284,828283, ...). This fails on MSSQL if the number of group memberships is too large. This goes back to my original question.

I solved this by using the approach outlined in my last post. In instances where I want to inject (RightID in (-1,394435,828284,828283, ...) into the query I replace it with a subquery:

(RightID in (select id from mytable where ... ))

Behind the scenes mytable is automatically populated and kept up to date. This scales nicely and has gotten around the MSSQL limitation I was hitting. But to your other concern:

that will perform the correct operation including all permission callbacks to ensure the proper security behavior. In turn you are abstracted from the database which we will not guarantee any external contract on the implementation since the system is evolving and schema is subject to change

Do you mean FilterNodes and whatever else comes down the pike? I understand what you're saying and will keep an eye on it. I do a lot of API abstraction and have found it relatively easy to upgrade modules to accomodate changes in the underlying CS APIs. Hopefully this will also apply to permission handling in the future.

David Templeton

Chris

I realise now my original question also applies to permission filtering. I have avoided$LLIAPI.NodeUtil.AppendPermissionsToRec due to concerns with scaling (and I have uses outside of permission checking). For example, if a user is on page 2000 then they still need to permission check all items on the previous 1999 pages to accurately render its contents. An informal test shows this to be slow.

Yes it will be slower to page 2000!!! Then they should be filtering the set down by other means. I have had this conversation with many customers and in reality no one would do that except once to try it. We are reviewing improvements for such sets but filtering and pivoting on metadata is the way to go for large sets.

David

Chris Meyer

Sure, this was just an example. Consider a similar scenario if the user didn't have permission on the first 1999 pages. This would effectively make page 2000 the first page. Have you seen issues with this and facet browsing?

Ferdinand Prantl

You diverged from the problem of a reasonable count of SQL statement parameters to the problem of a reasonable pagination implementation. Also, I am not sure, if you can find a solution when discussing only technical limits of such low level. Using different architecture on a higher level might offer better usability avoid this looking for workarounds.

Too many parameters

If there are thousands of parameters in IN or other where clauses, it seems to be a problem somewhere else, than in limits of the database driver.

1. How the customer set up their users, groups and permissions? If only this customer forces the software to use such extreme statemenets, I would guess, that they does something, which the software was not designed for. They should follow OT best practices.

2. Does this problem occur, regularly? It would show a design problem in the software, which should execute statements according to the database vendor recommendations, like Hans wrote.

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql

Replacing parameter placeholders with values in the SQL statement might be a workaround, but the performance would suffer, and you might hit other limits.

Some SQL servers allow using an XML with a result set, which can be loaded by a special SELECT statement, if the temporary or global table.

Too many pages in random pagination

Firstly, this problem might be rejected right away as too minor. Most people page through a couple of first pages and then try other means of finding their objects. Although going to last pages may be slow, it is not probable, that many users would do it regularly to slow the server down.

If you want to support random pagination as a regular operation, you need to consider the design of permissions checks.

1. Are you able to include the permission checks in the SQL statement, which is returning the results to show to the user? You don't need to worry about random pagination. Any page will be accessed with the same complexity.

2. Do you have to filter out objects, which the user cannot access, after executing the SQL statement? You should not implement random pagination. Alternatives with a good performanace are the prev/next pagination, the or next-only pagination (infinite scrolling).

OT Content Server belongs to the second group. Modern UIs for Content Server should not offer random pagination. As long as we see random pagination in CS UIs, users themselves are responsible for using the best practice, as David wrote.

Random pagination is not necessary, because you have sorting and filtering to limit the result set to a reasonable count, which you can browse through.

Chris Meyer

Hi Ferdinand,

Thanks for your reply. A few comments:

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

This is what I ended up doing, which solved both a performance problem and permitted the IN clause to operate with a set larger than what's supported by the database.

Do you have to filter out objects, which the user cannot access, after executing the SQL statement? You should not implement random pagination.

To paraphrase what I wrote earlier: What happens if your view contains 1'000'000 items but you only have permission to see 20? Will it permission check all 1'000'000 items? It seems to be (at least technically) the same problem to random pagination with the difference being that you only see one page.

Perhaps I'm missing something, but I don't see how this can be effectively handled in the general case without applying the permission check in the underlying SQL statement. If additional security callbacks are required then these can still be applied (or if applicable, added directly to the underlying query).

Ferdinand Prantl

Yes, of course. You can improve the performance only for navigating to following pages (by avoiding re-scanning of all items always from the beginning). You cannot avoid going over the lot of inaccessible items (at least once for the first page).

If you enter a container with 1 000 000 items, but you only have permission to see the last 20, you will pay the "penalty" of scanning and filtering out the first 999 980 items. If the page size is 10, you will receive the first 10 items from the 20. If you go to the second page (the last 10 items), you can use the table row number or some other database-cursor-like token from the first query to make the second query continue. For example:

GET /items?count=10{  items: [first 10 items]  nextStart: 123456 // to implement "go to next page"}GET /items?start=123456&count=10{  items: [next 10 items]  nextStart: null // these were the last items}

I wonder, if there are any callbacks in a minimal CS installation. (I know of them only from modules like Records Management and Security Clearance, for example.) If there are none, there should be no performance problem in the default CS installation.

If some callbacks are needed only in special areas, the customer should be able to enable them only in specific containers and volumes. I don't know, how feasible it is with existing functionality, or if it is acceptable in a real world system.

CBFilterNodes gets the whole batch of records to check permissions for. Modules should optimize their additional SELECTs to fetch data for all records at once, if some of them don't do it.

If the case of many inaccessible items comes very often, we could increase the "window size" of the main SELECT query. Anticipating, that a lot would be filtered out, we could fetch more records, than it was requested by the specified page size. For example, moving the database cursor by 100 instead of by 10 may help and still might not slow the system down.

I think, that if there is a performance problem, it should be analyzed in the concrete case, in which it causes trouble. The callbacks may not be the slowerst part in some particular scenario. Thay cannot be avoided in general.

sıɹɥɔ

Not to hijack the discussion but I should mention that when implementing pagination it is not a great idea to use a “table row number or some other database-cursor-like token”. Instead a better design is to use the value of the key for the rows being output. (This is sometimes referred to a “keyset pagination”.) Making the starting point of any page based on a logical value instead of something intrinsic to the database allows the dbms to better optimize its query. It’s easy to imagine cases where such a design actually does allow the earlier work for the previous pages to in fact be skipped.

-chris

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Wednesday, September 06, 2017 11:32 AM
To: eLink Recipient
Subject: RE MSSQL failing on large "in" clause 2

RE MSSQL failing on large "in" clause 2

Posted by Prantl, Ferdinand On 09/06/2017 02:29 PM

GET /items?count=10

  items: [first 10 items]

  nextStart: 123456 // to implement "go to next page"

GET /items?start=123456&count=10

  items: [next 10 items]

  nextStart: null // these were the last items

[…]