php - 花很多时间从oracle获取数据并显示在php中(taking much time to fetch data from oracle and show up at php)

Question

I am working with php and oracle to get data from oracle database and show up at php table with php code by connecting with oracle database.

(我正在使用php和oracle从oracle数据库中获取数据，并通过与oracle数据库连接来使用php代码在php表中显示。)

The problem is, I am getting data after long time during I have some for loop to run other query At first I have run a query which give me total card issued for till 23/11/2019(the date I have assigned at query), and this query also give all card canceled between 23/11/2019 to 25/11/2019 here are below the query in php with oci_excecute()

(问题是，我经过一些for循环以运行其他查询后很长时间后才获取数据首先我运行了一个查询，该查询向我提供了截至2019年11月23日（我在查询中分配的日期）的总卡数，并且此查询还会给出在23/11/2019到25/11/2019之间取消的所有卡，这在php中带有oci_excecute（）的查询下面)

    $stid = oci_parse($conn, "
                                select
                                (count(till_today_card_issued) - today_total_canceled) as till_today_card_issued,
                                today_total_canceled
                                from
                                (
                                SELECT
                                    eofficeuat.cardprintlog_cpa.empid as till_today_card_issued,
                                    (
                                      select count(eofficeuat.cardprintlog_cpa.empid)
                                      from 
                                      eofficeuat.cardprintlog_cpa
                                      where
                                      eofficeuat.cardprintlog_cpa.cardstatus='DISCARDED'
                                      and eofficeuat.cardprintlog_cpa.dateofissue BETWEEN TO_DATE('23/11/2019', 'dd/mm/yyyy') AND TO_DATE('25/11/2019', 'dd/mm/yyyy')
                                    ) as today_total_canceled

                                FROM
                                    eofficeuat.cardprintlog_cpa

                                )
                                group by today_total_canceled

                            "
                        );
                      oci_execute($stid);

and here is below code for fetch data

(这是获取数据的以下代码)

           $TILL_TODAY_CARD_ISSUED = 0;
                                  $TODAY_TOTAL_CANCELED = 0;
                                while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS))
                                {
                                    $TILL_TODAY_CARD_ISSUED = $row['TILL_TODAY_CARD_ISSUED'];
                                    $TODAY_TOTAL_CANCELED = $row['TODAY_TOTAL_CANCELED'] ;

                                }

now I wanted to write another query which can give total Reissue cards but condition is employee have canceled a card before and reissue again between the date 23/11/2019 to 25/11/2019.

(现在我想写另一个查询，该查询可以提供总重发卡，但条件是员工之前已取消卡并在23/11/2019到25/11/2019之间重新发卡。)

for this I did following code

(为此，我做了以下代码)

         $canceled_query = oci_parse($conn, "
                                      select eofficeuat.cardprintlog_cpa.empid
                                      from 
                                      eofficeuat.cardprintlog_cpa
                                      where
                                      eofficeuat.cardprintlog_cpa.cardstatus='DISCARDED'

                        ");
                     oci_execute($canceled_query);
                     $emp =  array();

                     while ($row = oci_fetch_array($canceled_query, OCI_ASSOC+OCI_RETURN_NULLS))
                     {
                         $emp[] = $row['EMPID'];
                     }

                     $rissue_emp = array();
                    for($i=0; $i<sizeof($emp); $i++){
                    $rquery = oci_parse($conn, "
                                   select eofficeuat.cardprintlog_cpa.empid
                                      from 
                                      eofficeuat.cardprintlog_cpa
                                      where
                                      eofficeuat.cardprintlog_cpa.cardstatus='READY'
                                      and eofficeuat.cardprintlog_cpa.dateofissue BETWEEN 
       TO_DATE('23/11/2019', 'dd/mm/yyyy') AND TO_DATE('25/11/2019', 'dd/mm/yyyy')
                                      and eofficeuat.cardprintlog_cpa.empid='". $emp[$i] ."'
                        ");
                      oci_execute($rquery);



                     while ($row = oci_fetch_array($rquery, OCI_ASSOC+OCI_RETURN_NULLS)){
                             $rissue_emp[] = $row['EMPID'];
                        }
                    }

and finally show up the data at php table

(最后在php表中显示数据)

      echo "<tr>";
                                    echo "<td>Port Staff</td>";
                                    echo "<td>". $TILL_TODAY_CARD_ISSUED ."</td>";
                                    echo "<td>". $TODAY_TOTAL_CANCELED ."</td>";
                                    echo "<td>". sizeof($rissue_emp) ."</td>";

                                  echo "</tr>";

Now problem is there have a for loop executing a query till the array size which taking much time and I am getting result after long time.

(现在的问题是，有一个for循环会执行查询直到数组大小，这要花很多时间，而且长时间后我会得到结果。)

can you please tell me how can I make it fast to get result?

(您能告诉我如何快速取得结果吗？)

Thanks

(谢谢)

  ask by mdkamrul translate from so

Answer 1

There can be many causes of poor query performance.

(导致查询性能下降的原因有很多。)

We cannot just look at a query, stroke our chins and then say "Ah ha! It's this line".

(我们不能只看一个查询，抚摸下巴，然后说“啊哈！就是这一行”。)

Please read this excellent post on asking Oracle tuning questions .

(请阅读有关询问Oracle调优问题的出色文章 。)

Having said which, in this case you should reconsider the application design.

(话虽如此，在这种情况下，您应该重新考虑应用程序设计。)

Query loops within query loops are always a red flag.

(查询循环中的查询循环始终是一个危险信号。)

A single query which joins all the required tables for rendering in the client would likely be more efficient:

(联接所有必需的表以在客户端中进行渲染的单个查询可能会更有效：)

select eofficeuat.cardprintlog_cpa.empid
from   eofficeuat.cardprintlog_cpa
where  eofficeuat.cardprintlog_cpa.cardstatus='READY'
and    eofficeuat.cardprintlog_cpa.dateofissue BETWEEN TO_DATE('23/11/2019', 'dd/mm/yyyy') AND TO_DATE('25/11/2019', 'dd/mm/yyyy')
and    eofficeuat.cardprintlog_cpa.empid in (
                   select eofficeuat.cardprintlog_cpa.empid
                   from   eofficeuat.cardprintlog_cpa
                   where  eofficeuat.cardprintlog_cpa.cardstatus='DISCARDED'
                   )