Home » RDBMS Server » Performance Tuning » Heavy query with GL
Heavy query with GL [message #268668] Wed, 19 September 2007 05:39 Go to next message
tarmenel
Messages: 63
Registered: February 2007
Location: Israel
Member

Hi there,

I'm posting this here because this is more SQL related than E-Biz suite related. I'm trying to query the General Ledger tables but it is extremely heavy and not sure if there's something in my query that may be a problem. What I need to do is take two sets of books in the organization and compare them against each other on one line for each line, please could someone take a look and see if I could optimize it:
Select je_headers.Name,
       je_headers.doc_sequence_value doc_num,
       je_headers.period_name,
       gcc.concatenated_segments accnt1001, -- jel1001.code_combination_id,
       je_headers.currency_code,
       Sum(nvl(jel1001.entered_dr, 0)) - Sum(nvl(jel1001.entered_cr, 0)) entered1001,
       Sum(nvl(jel1001.accounted_dr, 0)) - Sum(nvl(jel1001.accounted_cr, 0)) accounted1001,
       gcc2009.concatenated_segments accnt2009,
       Sum(nvl(jel2009.entered_dr, 0)) - Sum(nvl(jel2009.entered_cr, 0)) entered2009,
       Sum(nvl(jel2009.accounted_dr, 0)) - Sum(nvl(jel2009.accounted_cr, 0)) accounted2009,
       decode(gcc.code_combination_id-gcc2009.code_combination_id,0,'TRUE','FALSE') SameAcc
From   gl_je_lines jel1001,
       gl_code_combinations_kfv gcc,
       gl_je_lines jel2009,
       gl_code_combinations_kfv gcc2009,
       (Select jeh1001.je_header_id h1001,
               jeh2009.je_header_id h2009,
               jeh1001.Name,
               jeh1001.doc_sequence_value,
               jeh1001.currency_code,
               jeh1001.period_name
        From   gl.gl_je_headers jeh1001, gl.gl_je_headers jeh2009
        Where  jeh2009.set_of_books_id = 2009
        And    jeh2009.parent_je_header_id = jeh1001.je_header_id
              --*******************************
           --   And    jeh1001.Name = 'PLS Cellcom ILS'
        --And    jeh1001.doc_sequence_value = '710104'
        --*******************************
        ) je_headers
Where  jel1001.je_line_num = jel2009.je_line_num
--And    jel1001.code_combination_id = jel2009.code_combination_id
And    jel1001.period_name = jel2009.period_name
And    jel1001.je_header_id = je_headers.h1001
And    jel2009.je_header_id = je_headers.h2009
And    jel1001.code_combination_id = gcc.code_combination_id
And    jel2009.code_combination_id = gcc2009.code_combination_id
Group  By je_headers.Name,
          je_headers.doc_sequence_value,
          jel1001.je_header_id,
          gcc.concatenated_segments,
          je_headers.currency_code,
          je_headers.period_name,
          gcc2009.concatenated_segments,
          gcc.code_combination_id,gcc2009.code_combination_id 
Re: Heavy query with GL [message #268792 is a reply to message #268668] Wed, 19 September 2007 13:55 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
All of us have ERP installed on our PDA's with your indexes,
so we can really fast to recreate the problem without
TKPROF, EXpalin, etc.

Michael
Previous Topic: Query Tuning Problem (tkprof o/p,query & explain plan attached)
Next Topic: Tuning of query (Not DBA Level Tuning)
Goto Forum:
  


Current Time: Thu Dec 08 08:49:35 CST 2016

Total time taken to generate the page: 0.11917 seconds