0

I'm running jOOQ integration tests for large conditions of the form

WHERE x IN (:1, :2, :3, :4, ..., :3001, :3002)

The above example depicts that there are many bind variables in an IN condition. Oracle has a well-known limitation of 1000 values (bind values or inline values) between the parentheses of an IN condition. The solution is simple, just write:

WHERE x IN (:1, :2, :3, :4, ..., :1000) OR x IN (:1001, ...)

Sybase ASE 15.5 and SQL Server 2008 R8 on the other hand seem to have an overall limitation on the number of bind values: 2000 for Sybase ASE and 2100 for SQL Server respectively. In other words, there seems to be no way to split / transform the above condition using bind values for these two databases. Is there any way to circumvent that problem, short of inlining all bind values?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509

2 Answers2

1

If you create a table type, and then say IN (SELECT column FROM @variableThatIsMyTableType) then you aren't limited at all.

I won't regurgitate MSDN - which covers table types and table valued parameters here.

The SQL you then generate looks something like:

DECLARE @variableThatIsMyTableType mySchema.myTableType
INSERT @variableThatIsMyTableType VALUES (1)
INSERT @variableThatIsMyTableType VALUES (2)
EXEC proc @variableThatIsMyTableType

However, when submitting this from C# and SqlClient, it creates what's called a 'trivial plan' - and you can read about TVPs and trivial plans here and here. Doing it via SQL direct would result in a cached plan, so your mileage may vary.

Matt Whitfield
  • 6,436
  • 3
  • 29
  • 44
  • That certainly looks like a viable way to circumvent the problem. On the other hand, I have full control over the SQL rendered by jOOQ, so in this case, inlining all bind variables would be simpler than creating ad-hoc table types. – Lukas Eder Jun 30 '12 at 06:24
  • Yeah, it certainly seems like a 'six of one and half a dozen of the other' situation. – Matt Whitfield Jul 01 '12 at 16:49
0

The way we've finally implemented this in jOOQ is by using a ControlFlowException to abort rendering of SQL with bind values once we've encountered any limit. The limits are:

  • SQLite: 999
  • Ingres 10.1.0: 1024
  • Sybase ASE 15.5: 2000
  • SQL Server 2008: 2100

We've also blogged about this here:

Once this limit is hit, the ControlFlowException is caught at the query rendering site, where it is simply re-rendered with all bind values inlined - which always works (until you hit the query size limit, if any, of course).

We're assuming that inlined bind values, and the repetitive hard-parses that incur are OK in these cases, as chances that an execution plan can really be reused with such highly dynamic SQL are slim.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509