Solved

Speedup SQL View

Posted on 2007-04-10
18
1,951 Views
Last Modified: 2013-12-19
I have been asked to look at a query and try to speed up execution.  The query was setup as a view, using SQL Developer.  It is accessed by different users.
Eg.
User 1:  SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '500'  
User 2:  SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '502'

JE_VIEW has approximately 10 FROM tables.  I did a count on some of the tables and found that some of them were over 10 million records.  

Some thoughts I had are as follows:
1)  Mention only 1 FROM table and use the JOIN to reference the other tables.
2)  Do the DIV check at the VIEW level, although I believe they setup the system this way so if they make a change, only 1 view would require changing.  Maybe Stored procedures would be the way to go?

Any help would be appreciated.
 
0
Comment
Question by:lilputian
  • 4
  • 4
  • 4
  • +2
18 Comments
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 250 total points
ID: 18886744
Hi lilputian,

10 million records is quite a big table and would usually a number of seconds or even minutes to query on such a table. The speed depends on the complexity of your SQL of your view but a simple query like

SELECT J1.* FROM GL.JE_VIEW J1 WHERE J1.DIV = '500'    shouldn't take particularly long.

and do make sure the DIV table is indexed on the Table referenced by the view.

Do post the view SQL syntax as that is where we should be tackling.

Cheers,
NicksonKoh
0
 
LVL 1

Author Comment

by:lilputian
ID: 18887591
Here's the SQL view:
Select
  gl.je_header_id, gl.je_line_num,
  jc.user_je_category_name,
  case when gl.gl_sl_link_table = 'RSL' then rsl.accounted_dr else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ael.accounted_dr else gl.accounted_dr end end accounted_dr,
  case when gl.gl_sl_link_table = 'RSL' then rsl.accounted_cr else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ael.accounted_cr else gl.accounted_cr end end accounted_cr,
    case when gl.gl_sl_link_table = 'RSL' then pv.vendor_name   else case when gl.gl_sl_link_table = 'APECL' or jr.gl_sl_link_table = 'APECL' then ipv.vendor_name   else gl.reference_4  end end reference_4, --ref4_max_vendor,
   id.invoice_id,  
  gl.accounted_dr je_accounted_dr, gl.accounted_cr je_accounted_cr,
  rsl.accounted_dr sl_accounted_dr, rsl.accounted_cr sl_accounted_cr,
  gl.entered_dr je_entered_dr, gl.entered_cr je_entered_cr,
  rsl.entered_dr sl_entered_dr, rsl.entered_cr sl_entered_cr,
  ph.segment1 sl_max_ponum,
  rt.attribute5 rec_sl_att5_max_wonum,  
  rt.attribute6 rec_sl_att6_max_fincntrlid,
  rt.attribute7 rec_sl_att7_max_itemnum,
  id.attribute5 inv_sl_att5_max_wonum,  
  id.attribute6 inv_sl_att6_max_fincntrlid,
  id.attribute7 inv_sl_att7_max_itemnum,
  gl.reference_1 je_ref1,
  gl.reference_2 je_ref2,
  gl.reference_3 je_ref3,
  pv.segment1 vendno, pv.vendor_name,
  gl.gl_sl_link_id, gl.gl_sl_link_table,
  jr.gl_sl_link_id jr_gl_sl_link_id, gl.gl_sl_link_table jr_gl_sl_link_table,
  rsl.je_source_name, rsl.je_category_name, rsl.je_batch_name, rsl.je_header_name, rsl.je_line_description,
  gl.last_update_date, gl.last_updated_by,
  gl.set_of_books_id, gl.code_combination_id, gl.period_name, gl.effective_date,
  gl.status, gl.creation_date, gl.created_by, gl.last_update_login,
  gl.description,
  gl.invoice_date,
  gl.amount_includes_tax_flag, gl.tax_document_identifier, gl.tax_document_date,
  gl.tax_customer_name, gl.tax_customer_reference, gl.tax_registration_number,
  gl.tax_line_flag, gl.tax_group_id,
  id.invoice_distribution_id, rt.transaction_id
from
  gl.gl_je_headers gh,
  gl.gl_je_lines gl,     -- over 11 million records
  gl.gl_code_combinations gcc,
  gl.GL_JE_CATEGORIES_TL jc,
  PO.RCV_RECEIVING_SUB_LEDGER RSL,
  po.RCV_TRANSACTIONS RT,
  po.po_headers_all ph,
  po.po_vendors pv,
  ap.ap_ae_lines_all ael,    -- over 12 million records
 (
     Select idi.invoice_distribution_id, idi.line_type_lookup_code, idi.invoice_id, idi.po_distribution_id,      
          idi.dist_code_combination_id, idi.amount, idi.attribute5, idi.attribute6, idi.attribute7
     FROM ap.ap_invoice_distributions_all idi
     WHERE idi.line_type_lookup_code = 'ITEM'
     UNION
     Select idt.tax_invoice_distribution_id invoice_distribution_id,
           idt.line_type_lookup_code, idt.invoice_id,
           idt.po_distribution_id, idt.dist_code_combination_id,
           idt.tax_amount * idt.perc_dist amount,
           idt.attribute5, idt.attribute6, idt.attribute7
     FROM wfm.wfm_ap_invoice_tax_dist idt
 ) id,
  gl.gl_import_references jr,
  po.po_distributions_all ipd,
  po.po_headers_all iph,
  po.po_vendors ipv
WHERE 1=1
  -- link the journal header to the journal line
  and gh.je_header_id = gl.je_header_id
  -- link the journal line to the gl code combination
  and gl.code_combination_id = gcc.code_combination_id
  -- link the category to the user category
  and gh.je_category = jc.je_category_name
  -- link the journal line to the PO sublegder
  and gl.gl_sl_link_id = rsl.gl_sl_link_id (+)
  -- link the subledger line to the transaction
  and rsl.rcv_transaction_id = rt.transaction_id (+)
  -- link the transaction to the po header
  and rt.po_header_id = ph.po_header_id (+)
  -- link the po to the vendor
  and ph.vendor_id = pv.vendor_id (+)
  and ael.source_id = id.invoice_distribution_id (+)
  -- link journal import reference to AP accounting entries
--  and NVL(jr.gl_sl_link_table,'APECL') = 'APECL'
  and jr.gl_sl_link_id = ael.gl_sl_link_id (+)
  -- link journal line to journal import references
  and gl.je_header_id = jr.je_header_id (+)
  and gl.je_line_num = jr.je_line_num (+)
  -- link the invoice distribution to the po distribution, po header and po vendor
  and id.po_distribution_id = ipd.po_distribution_id (+)
  and ipd.po_header_id = iph.po_header_id (+)
  and iph.vendor_id = ipv.vendor_id (+)
  and gcc.segment1 in ('501', '502', '524', '525')
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18887632
What is the nature of the underlying data?

Is it a transactional system/Datawarehouse environment?
If the data is uploaded at intervals, then you can consider having materialized views instead of a view.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18887766
Wow! That SQL can blow me off anytime. Joins with lots of tables and 2 very big ones! It will surely take some time to execute that view.

The view SQL looks good actually but for such a complex SQL on big tables, you should write the result to a temp table rather than as online view. If the view already takes many minutes or even hours to execute, there is really no point in keeping it as an view. Worst such a SQL is likely to choke up lots of the server resources and will slow down others who may be accessing the database.

Change it to say PL/SQL that writes to a temp table. Schedule it to run periodically (how often will depend on the business need). Create the necessary indexes on the temp table.

Now instead of having the using to access the view, have them access this temp table.

cheers
Nickson
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18888366
Sujith,
Even if this is a datawarehouse env, I dont think creating a MV is an option, According to the author-- His q is to look at the query, It appears that user 1 and 2 are novice users and they dont know how to form a query, If you are speaking about non query rewriteable MV, still the same problem persists because of the where clause there in the query that is being run by user 1 and 2.


lilputian,

Please let me know if my observation is correct- Is it OK if I give you a diff query other than one the user uses?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18888814
@ramumorla
>>they dont know how to form a query
The query from the users is from a VIEW. From what I understood users are not creating the view each time. So, if the data is not modified frequently(Data WH env), the view text can be used to form an MV.

>>non query rewriteable MV???
As the select is going to be directly from the MV, no question of query re-write. Moreover, you can put a filter condition for direct/indirect select from an MV.
Finally the user query will still remain the same, but the underlying object will be an MV.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18889072
Sujith

1. You are right

2.You are right, The speed is going to enhanced because, in case of a view, the view is created in memory, every time the select statement is fired by user1 or 2 or both, But in case of a MV- The MV is already built physiocally so it fastens up the query parsing, but the time taken to make a tablescan is same in both the cases.

Lilputian,

Can you please let me know the where clause variables granularity?

Thx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:ramumorla
ID: 18889649
BTW it all is true only when this is DWH env.. If this is OLTP environment, creation of MV hurts the performance
0
 
LVL 1

Author Comment

by:lilputian
ID: 18890187
Thanks for all your replies.  It is not a datawarehouse environment.  

Ramumorla:
I'm sorry, I don't know what the 'WHERE clause variables GRANULARITY' means.
Could you give me an example.  Thanks.

And yes, you can give me a different query.

I believe they need to do the filtering at the view level to limit the records.  Would Stored Procedures be the way to go?  One could pass the Period as well as the division variable.  I would as well change the table joins as follows, what do you think?
SELECT ......
FROM  GL.GL_JE_HEADERS GH1
LEFT OUTER JOIN GL.GL_JE_LINES GL1 ON GH1.JE_HEADER_ID = GL1.JE_HEADER_ID
LEFT OUTER JOIN GL.GL_CODE_COMBINATIONS GCC1 ON  GL1.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID
LEFT OUTER JOIN ....
etc.
WHERE GCC1.SEGMENT1 = '501' And GH1.PERIOD_NAME = 'MAR-07'    
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18890342
can you run select count(distinct J1.DIV )
and give me the number?
0
 
LVL 1

Author Comment

by:lilputian
ID: 18891095
The count is 161.
0
 
LVL 1

Author Comment

by:lilputian
ID: 18891127
There are 545,000 records in the GL_CODE_COMBINATIONS file.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18894957
Hi lilputian,

I do not think that breaking up the records by filtering is not a good idea.

The method suggested by Sujith and myself are similar because they are the method to prepare the consolidated data in the background. The difference is that materialized View is a utility in Oracle that allows you to do it with very little coding while my method requires hardcore programming. It's a good method but you must understand and learn how to use materialized view and this oracle utility must have be set up into the database. I am sure Sujith will be able to guide a little on the steps if you take the materialized view approach.

Data preparation in the background is the key to improving dramatically the performance for such complex consolidation on huge datasets. It requires more hardwork from but will ease the users pain.

cheers
Nickson
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18894959
Sorry I meant "I do not think that breaking up the records by filtering is a good idea."
0
 

Expert Comment

by:ahmedcoeur
ID: 25968967
try index the foreign keys
and making anothe file group to handel talbe
i recommend that u make a seprated file group for the big table
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now