Forum OpenACS Development: Re: Limitation on length of bind variables
Since in your case it's much faster, that must meen that the PL/SQL overhead of converting the string is much lower than the time burned up in re-parsing the query with the embedded string literals every time? The only difference in your two versions of the query is the parsing of string litereals vs. parsing of a string to bind variables, right?
The str2tbl PL/SQL function above is basically a little custom special-purpose query parser, so at first glance, it seems odd to me that it would be substantially faster than Oracle's C-coded general purpose SQL query parser. Hm, must be that the SQL parser is doing much more stuff every time - trying to optimize the query plan from scratch, etc. - while the PL/SQL function is doing only one specific simple thing and not invoking all that other in-this-case-useless overheard of the query parser.
One way around the limitations on the length of an in list is to do a really ugly UNION ALL of thousands of "select 'FOO' from dual" statements in an inline view. AFAIK there is no limit on how many such subselects you do, so you can create in-lists of arbitrary size that way. Of course, that seems to make a rather heinously ugly query, but surpisingly enough it usually seems to work ok. However, in one case for a particular (non-OpenACS) query with 1500 integers on the in list, I recently compared that to using an Oracle tempory table, and the temporay table was consistently about twice as fast.
By "temporary table" I mean one definied like this:
create global temporary table foo (...) on commit delete rows;
I never tested many concurrent runs my query using a temporary table, as that was not relevent to our application. So if I had done so, I don't know whether I would have seen temp. tablespace contention like what Dirk reported for aiesec.net.
Dirk, was your aiesec.net temporary tablespace locally managed or dictionary managed? The default in Oracle 8i is dictionary managed, which generally sucks and thus is never what you want. (9i changed the default to locally managed tablespaces.)
Since temporary tables are Oracle's designed solution for this sort of query, I would not expect them to ever have significantly worse performance than other more hackish solutions, when Oracle is configured correctly. So if aiesec.net's Oracle instance was definitely configured correctly - locally managed temp. tablespace, etc. - and yet still had lousier performance when using a temp. table in the query than other seemingly uglier solutions, that would be particularly interesting. I'd like to know for sure. Dirk?
Actually, I don't see why values inserted into a temporary table would ever go to disk at all, while values inserted into a PL/SQL table would stay in memory. Perhaps that also is tweakable in some way? If doing a query that uses Jain'es str2tbl PL/SQL function above (possibly multiple times) turns out to really always be superior in performance to using temporary tables, for all in list sizes, that would be useful to know!
(Of course, as you all know and mention above, the best solution is to eliminate the in-lists entirely in some fashion, and instead pass along a specification defining how to do the query that created the big long in list in the first place. But temp. tables are still very handy in many cases.)
