0

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.

DBAs say we cannot add more indexes as it would slow down the performance since it is an OLTP system.

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.

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.

My question is, since pl/sql would require select and insert both, are there any chances pl/sql can be faster than sql?

Are there any cases where pl/sql is faster for any result which is achievable by sql?

I will be happy to provide more information if the given info doesn't suffice.

Oradev
  • 1
  • Yes, there is a chance. Set-based queries are usually faster than bespoke code, but there are definitely exceptions. – Gordon Linoff Jan 22 '18 at 12:14
  • The smart money is on an 800-line query using *"many inline views and outer joins and a few group by functions"* being poorly written. An informed rewrite would probably yield vast performance improvements. That's a better bet on using PL/SQL to populate a temporary table. However, actually rewriting the code is beyond the scope of this site. So please don't ask, unless you are looking to pay for some consultancy ;) – APC Jan 28 '18 at 21:29

2 Answers2

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.

Mark Wagoner
  • 1,729
  • 1
  • 13
  • 20
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...

BobC
  • 4,208
  • 1
  • 12
  • 15