0

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. 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()

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. for this I did following code

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. can you please tell me how can I make it fast to get result? Thanks

mdkamrul
  • 274
  • 1
  • 13

3 Answers3

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.

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'
                   )
APC
  • 144,005
  • 19
  • 170
  • 281
0

There isn't a single "make it fast" solution, without first understanding the performance profile of your code. I strongly recommend utilising some type of application performance monitoring for your code. This will let you measure how long your script takes to run, how long it takes waiting on SQL queries, etc. etc.

There are a few things that jump out as potential performance issues (and the data from your APM solution will confirm this):

  • SQL Queries that have subqueries in their FROM clause can cause some performance issues depending on the query and data.
  • If you're retrieving lists of data that you only want the aggregate from, try using COUNT() other functions. For example SELECT COUNT(empid) as empid_count. This way you're not processing the data twice (once in the query, and a second time in your code).
mdkamrul
  • 274
  • 1
  • 13
developerjack
  • 1,173
  • 6
  • 15
  • I think this two suggestion not will increase speed, can you give me other solutions base on query? – mdkamrul Dec 01 '19 at 07:01
  • 1
    The only way you'll know is to run profiling tools over your code. If you're confident it's the SQL, then look at the query analyser. For MSSQL SQL try https://stackify.com/performance-tuning-in-sql-server-find-slow-queries/ – developerjack Dec 01 '19 at 07:17
0

After you sort out the SQL as other answers have suggested, then consider these other performance tips:

  • Use bind variables instead of string concatenation syntax like:

    and eofficeuat.cardprintlog_cpa.empid='". $emp[$i] ."'

    String concatenation is a SQL Injection security risk,

  • Tune oci8.default_prefetch or oci_set_prefetch() to reduce 'round trips' between PHP and the database when fetching query results.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48