I have been given an 800 lines SQL Query which is taking around 20 hours to fetch around 400 million records. There are 13 tables which are partitioned by month. The tables have records ranging from 10k to 400 million in each partition. The tables are indexed on primary keys. The query uses many inline views and outer joins and a few group by functions.
我已经获得了800行SQL查询,大约需要20个小时来获取大约4亿条记录。有13个表按月分区。这些表在每个分区中的记录范围从10k到4亿。表格在主键上编制索引。该查询使用许多内联视图和外连接以及一些按功能分组。
DBAs say we cannot add more indexes as it would slow down the performance since it is an OLTP system.
DBA表示我们无法添加更多索引,因为它会降低性能,因为它是OLTP系统。
I have been asked to convert the query logic to pl/sql and then populate a table in chunks.Then do a select * from that table.
我被要求将查询逻辑转换为pl / sql,然后以chunk的形式填充表。然后从该表中执行select *。
My end result should be a query which can be fed to my application. So even after I use pl/sql to populate a table in chunks,ultimately I need to fetch the data from that table as a query.
我的最终结果应该是一个可以提供给我的应用程序的查询。因此,即使我使用pl / sql以块的形式填充表,最终我还需要从该表中获取数据作为查询。
My question is, since pl/sql would require select and insert both, are there any chances pl/sql can be faster than sql?
我的问题是,由于pl / sql需要select和insert两者,有没有机会pl / sql比sql更快?
Are there any cases where pl/sql is faster for any result which is achievable by sql?
是否有任何情况下pl / sql对于sql可以实现的任何结果更快?
I will be happy to provide more information if the given info doesn't suffice.
如果给定的信息不够,我将很乐意提供更多信息。
2 个解决方案
#1
1
Implementing it as a stored procedure could be faster because the SQL will already be parsed and compiled when the procedure is created. However, given the volume of data you are describing its unclear if this will make a significant difference. All you can do is try it and see.
将其实现为存储过程可能会更快,因为在创建过程时SQL已经被解析和编译。但是,鉴于您所描述的数据量不清楚这是否会产生重大影响。你所能做的就是试试看。
#2
1
I think you really need to identify where the performance problem is; where the time is being spent. For example (and I have seen examples of this many times), the majority of the time might be in fetching to 400M rows to whatever the "client" is. In that case, re-writing the query or as PL/SQL will make no difference. Anyway, once you can enumerate the problem, you have a better chance of getting sound answers, rather than guesses...
我认为你真的需要确定性能问题的位置;花费时间的地方。例如(我已经看过很多次这样的例子),大部分时间可能是将400M行提取到“客户端”。在这种情况下,重写查询或作为PL / SQL将没有任何区别。无论如何,一旦你可以列举问题,你就有更好的机会得到合理的答案,而不是猜测......
#1
1
Implementing it as a stored procedure could be faster because the SQL will already be parsed and compiled when the procedure is created. However, given the volume of data you are describing its unclear if this will make a significant difference. All you can do is try it and see.
将其实现为存储过程可能会更快,因为在创建过程时SQL已经被解析和编译。但是,鉴于您所描述的数据量不清楚这是否会产生重大影响。你所能做的就是试试看。
#2
1
I think you really need to identify where the performance problem is; where the time is being spent. For example (and I have seen examples of this many times), the majority of the time might be in fetching to 400M rows to whatever the "client" is. In that case, re-writing the query or as PL/SQL will make no difference. Anyway, once you can enumerate the problem, you have a better chance of getting sound answers, rather than guesses...
我认为你真的需要确定性能问题的位置;花费时间的地方。例如(我已经看过很多次这样的例子),大部分时间可能是将400M行提取到“客户端”。在这种情况下,重写查询或作为PL / SQL将没有任何区别。无论如何,一旦你可以列举问题,你就有更好的机会得到合理的答案,而不是猜测......