How do I improve performance of my SQL script

See more:

SQL

Hi hope you are well

I am currently facing an issue with the performance of my SQL script, the script returns what is required, when the data count gets to around 10 000 records then it starts slowing down sometimes taking up to 2 minutes to return a the count.

How could i improve my script to improve the speed?

So basically what i am trying to a achieve is i am trying to do a count of a particular process(Pro_id),based on a status

What i wanted to achieve is splitting each status into their own column rather than having them in their own row.

I have built in sub queries to do this but once data counts start getting to 10 000-12 000 records its taking sometime to return data and I am using this to monitor status so needs to be as quick as possible

What I have tried:

select int_desc "Interface",
       NVL((select count(*)
       from queue, "interface" where
       "interface" = interface_key
       and que_from_cde in
       (
        select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || "interface" ||  batch || '\%'
       and pro_id = 36 and status_id = 3
       )
       and pro_id in (80)
       and status_id = 1
       and que_interface_key = c.que_interface_key
       group by int_desc,status_id),0) as "Pending",
       NVL((select count(*)
       from queue,"interface" where
       que_interface_key = interface_key(+)
       and que_from_cde in
       (
       select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || interface || batch|| '\%'
       and pro_id = 36 and status_id = 3
       )
       and pro_id in (80)
       and status_id = 2
       and que_interface_key = c.que_interface_key
       group by int_desc,status_id),0) as "Processing",
       NVL((select count(*)
       from queue,"interface" where
       que_interface_key = interface_key(+)
       and que_from_cde in
       (
       select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || interface ||  batch|| '\%'
       and pro_id = 36 and status_id = 3
       )
       and pro_id in (80)
       and status_id = 3
       and que_interface_key = c.que_interface_key
       group by int_desc,status_id),0) as "Sucessful",
       NVL((select count(*)
       from queue,"interface" where
       que_interface_key = interface_key(+)
       and que_from_cde in
       (
       select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || interface ||  batch|| '\%'
       and pro_id = 36 and status_id = 3
       )
       and pro_id in (80)
       and status_id = 4
       and que_interface_key = c.que_interface_key
       group by int_desc,status_id),0) as "Failed",
       NVL((select count(*)
       from queue,"interface" where
       que_interface_key = interface_key(+)
       and que_from_cde in
       (
       select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || interface ||  batch|| '\%'
       and pro_id = 36 and status_id = 3
       )
       and pro_id in (80)
     and status_id = 5
 and que_interface_key = c.que_interface_key
    group by int_desc,status_id),0) as "Duplicate/In Progress"
      from queue c,"interface" where
      que_interface_key = interface_key(+)
     and queue_key in
     (
     select queue_key from queue
    WHERE que_filepath like '\\MyNet\TESTING\' || interface ||  batch|| '\%'
   and pro_id = 36
      )
      and c.status_id = 3
    group by int_desc,"interface"