Create multi-dim array from cursor

Question

I'm executing a cursor. I have cut off the code how the procedure is called and executed. This part is efficient. At last I have a small cursor. I'm calling the procedure, which returns this cursor many times on the page and I need to create a multidimensional array from it.

This array should look like the following:

$ret = oci_execute($outrefc) ; 
while ($row = @oci_fetch_array($outrefc))
{
 foreach (array_keys($row) as $key)
 {
 $res[$i][$key] = $row[$key];
 }
 $i++;
}

I have also tried like this:

 oci_fetch_all($outrefc, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW+OCI_NUM);

Is there any way to make the upper snippet faster? The multidimensional array should stay as it is. I only wonder if I could create it in any more efficient way.

DB query:

select code, texttext, id, text
 from text,
 lang,
 client,
 cls,
 (select id, order, texttext text
 from sgr, text, lang
 where id = textid
 and textlngid = lngid
 and lngcode in ('en'))
 where 1 = 1
 and textcliid = cliid
 and textlngid = lngid
 and textclsid = clsid
 and sgrid(+) = clssgrid
 and nvl(showheadlinefrom, to_date('03.07.2016', 'dd.mm.yyyy')) <=
 to_date('03.07.2016', 'dd.mm.yyyy')
 and nvl(showheadlinetill, to_date('03.07.2016', 'dd.mm.yyyy')) >=
 to_date('03.07.2016', 'dd.mm.yyyy')
 and flgshowheadline in ('J')
 and lngcode in ('en')
 and clicode in ('mycode')
 order by order;

Here the explainplan of the query

• user272735: The problem can be located in the code you don't show to us. How do you construct $outrefc ? Do you use bind variables or not ? How many times you execute the query during a page load ?

Answer 1

You are asking if you can speed up the assignment into the array? I don't think you will be able to get a notable speed improvement on this part no. This should be completely negligible compared to the data retrieval time. What would give you a notable speed improvement is not using a cursor and doing this in a set based manner... however we would need to see your query to attempt to suggest something for this.

• MaMu: I have pasted the query. It is a simple one and works fast in db.
• Milney: So are you saying that running that query executes fast? But populating the array with the results is much slower? I find that hard to believe... There must be something else in your code which is slowing the page down. What makes you think the array population is the slow part?
• MaMu: I have executed directly in db. It is fast. While executing it from the page, it is slow.
• MaMu: Also when I comment out while loop or fetchall line, so when I only call the sp, the page is fast.
• Milney: Have you looked into the network in between your web and database servers?