First I would upgrade to 1.24 that would speed things up conciderably as it can do more than on fow per round trip fetch.
The problem with VARRAY is that you have to iterate over the arrary at each select to build up the perl array. While it is done in OCI (C) it will still take time. Ther is no supper magic in PERL, or OCI to get the varray as a big chunk. In fact a Varry, table and object are not realy what most people thing they are. They are just a predfined link (join) from one table to, in the case of VARRY, another table of one type, Oracle just hides the nasty relational db stuff from you the user. 1600 row is not a very large select are you having speed problems?? It could ber a ver large select if you are doing 1600*200+1600*200 for the two varrys. The internal nastyness of the varry in OCI is unknown to me. I would not be surprised if OCI is actully makeing a round trip to the varry table for each of the fields that it is returning. The actual process in OCI for filling the varray is get object get itrator for object iterate over object get and then copy contents of object to perl It wold not surprise me that the iteration and get part does a hidden round trip to the DB. according to the docs it might. Not way to tell for sure except by a tracke. There may be some hope here as I see in the doc that one can cache the object though it is not clear how. That would take a little time By the way you seem to be the only one using varray would love to talk to you off line n how you use them. Cheers John Scoles From: carlso...@llnl.gov To: dbi-users@perl.org Date: Fri, 30 Apr 2010 16:12:20 -0700 Subject: speeding varray queries in DBD::Oracle We have a lot of similar queries (see attached) that return a non-unique identifier and two varrays. One varray is a timestamp array and the other is a float value array. See attached SQL. We are currently returning on the order of 1600 rows. We would like to change the settings in DBD::Oracle 1.23 (or possibly upgrade), possibly change the queries to use table functions (how?). Our DBA suggested using “set arraysize” how do you do that from DBD::Oracle? We have VARRAY(200) for our varrays. Thanks! John Carlson _________________________________________________________________ Hotmail & Messenger. Get them on your phone now. http://go.microsoft.com/?linkid=9724463
