1
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ 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 ? \$\endgroup\$ – user272735 Sep 6 '16 at 5:23
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I have pasted the query. It is a simple one and works fast in db. \$\endgroup\$ – MaMu Aug 16 '16 at 15:36
  • 1
    \$\begingroup\$ 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? \$\endgroup\$ – Milney Aug 16 '16 at 15:51
  • \$\begingroup\$ I have executed directly in db. It is fast. While executing it from the page, it is slow. \$\endgroup\$ – MaMu Aug 16 '16 at 15:54
  • \$\begingroup\$ Also when I comment out while loop or fetchall line, so when I only call the sp, the page is fast. \$\endgroup\$ – MaMu Aug 16 '16 at 19:10
  • \$\begingroup\$ Have you looked into the network in between your web and database servers? \$\endgroup\$ – Milney Aug 17 '16 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.