如何提高SQL脚本的性能 [英] How do I improve performance of my SQL script

问题描述

嗨希望你很好



我目前面临着我的SQL脚本性能问题，当数据计数得到时，脚本会返回所需的内容大约10 000条记录然后它开始减速有时需要花费2分钟才能返回计数。



我如何改进我的脚本以提高速度？



所以基本上我想要实现的是我想要根据状态来计算特定进程（Pro_id）



我想要实现的是将每个状态分成他们自己的列而不是将它们放在自己的行中。



我有内置子查询来做到这一点，但一旦数据计数开始达到10 000-12 000条记录，它需要一段时间才能返回数据，我使用它来监控状态所以需要尽可能快地



我尝试过：

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"

推荐答案

这可能是一个启动者：



你继续执行同样的SQL语句：

Here's a starter, perhaps:

You keep executing this same SQL statement:

select queue_key from queue
       WHERE que_filepath like '\\MyNet\TESTING\' || interface ||  batch|| '\%'
       and pro_id = 36 and status_id = 3

如果执行一次并将结果放入临时表呢？因此，当您通过对临时表进行排队来完成剩余的工作时，您不必为了相同的结果而一遍又一遍地保持（如果您没有索引，扫描！）整个表格。



如果你要做的很多，而不是临时表，你可以考虑查看。



试试这个首发。

What about executing it once and putting the results in a temp table? Thus, when you do the remainder of your work by queering the temp table and you don't have to keep (if you have no indices, scanning!) entire tables over and over again for the same result.

If you'll be doing this a lot, instead of a temp table you may consider a VIEW.

Try that for a starter.

这篇关于如何提高SQL脚本的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！