Implement a specific-query to improve performance at scale for certain
server operations using new SQL from iPlant (Tony Edgin).

Tony Edgin of iPlant recently found another SQL form used by the iRODS
system that would running slowly on the iPlant instance and developed
and refined an alternative form that performs much better.

This SQL form is from a general-query call to the ICAT that is done in
some of the server code, in the collection.c source file, in the
function rsQueryDataObjInCollReCur function.

The solution we've developed for these sorts of issues is to define a
specific query that can be used instead.

So I've updated the rsQueryDataObjInCollReCur function in collection.c
so if it is about to make this particular general-query call, to first
try a specific-query called 'DataObjInCollReCur'.  If that succeeds,
the results are used.  If it fails, for example if not defined, then
the code will fall-back to the previous method of making the
general-query call.

We were able to reproduce similar performance results on the DICE-UCSD
server srbbrick15 using the iPlant ICAT DB.  Timing results and
the specific query definition are in the log below.

Thanks to Tony Edgin for finding the issue and providing the SQL
solution.

I'm committing this into SVN post 3.3.1 for now, but it will be
applied as at patch at iPlant and will need to be integrated into 4.0+
sometime.

[sheauc@srbbrick15 wayne]$ cat sql.add
asq "WITH coll AS (SELECT coll_id, coll_name FROM r_coll_main WHERE R_COLL_MAIN.coll_name = ?  OR R_COLL_MAIN.coll_name LIKE ?)  SELECT DISTINCT d.data_id, (SELECT coll_name FROM coll WHERE coll.coll_id = d.coll_id) coll_name, d.data_name, d.data_repl_num, d.resc_name, d.data_path FROM r_data_main d WHERE d.coll_id = ANY(ARRAY(SELECT coll_id FROM coll)) ORDER BY coll_name, d.data_name, d.data_repl_num" DataObjInCollReCur

[sheauc@srbbrick15 wayne]$ iadmin rsq DataObjInCollReCur
[sheauc@srbbrick15 wayne]$ time imv d1 d1c

real	1m13.977s
user	0m0.001s
sys	0m0.003s
[sheauc@srbbrick15 wayne]$ time imv d1c d1

real	1m13.418s
user	0m0.001s
sys	0m0.001s
[sheauc@srbbrick15 wayne]$ iadmin < sql.add
iadmin>iadmin>[sheauc@srbbrick15 wayne]$ 
[sheauc@srbbrick15 wayne]$ time imv d1 d1c

real	0m1.932s
user	0m0.002s
sys	0m0.000s
[sheauc@srbbrick15 wayne]$ time imv d1c d1

real	0m1.983s
user	0m0.002s
sys	0m0.001s
[sheauc@srbbrick15 wayne]$ 




git-svn-id: svn://irodssvn.ucsd.edu/trunk@5682 ea137bd6-aa86-47b6-b90d-b101d9f19a90