9 Replies Latest reply: Apr 17, 2013 9:56 AM by user648773 RSS

    SQL sub query runs too slow.

    user3206995
      Hi all

      I have this query that runs very slow, the prob;em seems to be related to this part of the query

      *(select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_ASP*

      I thought of rewriting that part of the query to a subquery in the where clause somewhere, but not sure how to do that and still present it's column value in the output as per "LAST_ASP".
      and not sure if it will be more efficient to do so.

      Currently, I think this colum subquery will run execute for every condition met and it seems to be the bottle neck in the query, took more than 30 Mins.

      The number of rows in this table is about 30 Million.


      Select p.ID as PARTICIPANTID,
      decode(concat(pp.SALUTATION_CODE,p.LANG),'1de','Dear Sir','2de','Dear Madam','1fr','Cher Monsieur','2fr','Ch<E8>re Madame','1it','Egregio Signor','
      2it','Gentile Signora','NA') as SALUTATION_TEXT,
      pp.FIRSTNAME,
      pp.LASTNAME,
      decode(p.VERIFIED, 0, p.EMAIL, 1, p.EMAIL, 2, p.ISL_EMAIL, 3, p.EMAIL,'NA') as EMAIL,
      p.LANG, TO_CHAR(pp.BIRTHDAY, 'dd.mm.yyyy') as BIRTHDAY,
      pa.STREET as Street, pa.HOUSENUMBER as NR,
      pa.ZIP as PLZ, pa.CITY as ORT,

      (select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_SPA,

      decode(p.verified, 0, 'verified', 1, 'Not verified', 2, 'Not Migrated', 3, 'Rejected', 4, 'changed to another participant', 5,
      'MoRo-Migrated', 6, 'ISL-User Asset', 'unknown') verified,
      decode(p.state, 0, 'eligible', 1, 'closed', 2, 'blocked', 'unknown') state
      from PARTICIPANT p
      left join PERSINFO pp on p.ID = pp.PLAYER_ID
      left join ADDRESS pa on p.ID = pa.PLAYER_ID
      where p.ENABLED = 1
      AND pp.STATUS = 1
      AND pa.STATUS = 1
      AND TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')


      Your help in solving this will be very much appreciated.

      Thank you
        • 1. Re: SQL sub query runs too slow.
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ


          consider doing as below

          ALTER SESSION SET SQL_TRACE=TRUE

          then process the trace file using TKPROF which will show you where time is actually being spent
          • 2. Re: SQL sub query runs too slow.
            surendra4y
            Have you applied Explain plan

            or
            set autotrace on

            take the out put and pest here

            Thanks,
            Surendra,
            • 3. Re: SQL sub query runs too slow.
              user648773
              Something like this:

              Select p.ID as PARTICIPANTID,
              decode(concat(pp.SALUTATION_CODE,p.LANG),'1de','Dear Sir','2de','Dear Madam','1fr','Cher Monsieur','2fr','Ch<E8>re Madame','1it','Egregio Signor','
              2it','Gentile Signora','NA') as SALUTATION_TEXT,
              pp.FIRSTNAME,
              pp.LASTNAME,
              decode(p.VERIFIED, 0, p.EMAIL, 1, p.EMAIL, 2, p.ISL_EMAIL, 3, p.EMAIL,'NA') as EMAIL,
              p.LANG, TO_CHAR(pp.BIRTHDAY, 'dd.mm.yyyy') as BIRTHDAY,
              pa.STREET as Street, pa.HOUSENUMBER as NR,
              pa.ZIP as PLZ, pa.CITY as ORT,

              v_last_spa.LAST_SPA,

              decode(p.verified, 0, 'verified', 1, 'Not verified', 2, 'Not Migrated', 3, 'Rejected', 4, 'changed to another participant', 5,
              'MoRo-Migrated', 6, 'ISL-User Asset', 'unknown') verified,
              decode(p.state, 0, 'eligible', 1, 'closed', 2, 'blocked', 'unknown') state
              from PARTICIPANT p
              left join PERSINFO pp on p.ID = pp.PLAYER_ID
              left join ADDRESS pa on p.ID = pa.PLAYER_ID
              left join (select t.GAME_PARTICIPANT, TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') LAST_SPA
                   from ticket t,
              PERSINFO pp
                   where t.GAME_PARTICIPANT = pp.ID
              and TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')
              group by t.GAME_PARTICIPANT) v_last_spa
              on p.id = v_last_spa.GAME_PARTICIPANT
              where p.ENABLED = 1
              AND pp.STATUS = 1
              AND pa.STATUS = 1
              AND TO_CHAR(pp.BIRTHDAY, 'mm') = TO_CHAR(ADD_MONTHS(CURRENT_DATE, -1), 'mm')
              • 4. Re: SQL sub query runs too slow.
                Etbin
                Maybe you could start with this all predicates pushed version as some predicates might reduce the number of rows to an extent where the use of indexes would not be absolutely necessary.
                It might not make things better as the optimizer is usually very good at predicate pushing but as well, you might be successful at undoing some and retaining some other pushed predicates.
                select p.id participantid,
                       decode(concat(pp.salutation_code,p.lang),'1de','Dear Sir',
                                                                '2de','Dear Madam',
                                                                '1fr','Cher Monsieur',
                                                                '2fr','Ch<E8>re Madame',
                                                                '1it','Egregio Signor',
                                                                '2it','Gentile Signora','NA'
                             ) salutation_text,
                       pp.firstname,
                       pp.lastname,
                       p.email,
                       p.lang,
                       to_char(pp.birthday,'dd.mm.yyyy') birthday,
                       pa.street,
                       pa.housenumber nr,
                       pa.zip plz,
                       pa.city ort,
                       t.last_asp,
                       pp.verified,
                       p.state
                  from (select id,
                               verified,
                               lang,
                               decode(verified,0,email,1,email,2,isl_email,3,email,'na') email,
                               decode(state,0,'eligible',1,'closed',2,'blocked','unknown') state
                          from participant
                         where enabled = 1
                       ) p
                       left join
                       (select player_id,
                               firstname,
                               lastname,
                               salutation_code,
                               birthday,
                               decode(verified,0,'verified',
                                               1,'Not verified',
                                               2,'Not Migrated',
                                               3,'Rejected',
                                               4,'changed to another participant',
                                               5,'MoRo-Migrated',
                                               6,'ISL-User Asset',
                                               'unknown'
                                     ) verified
                          from persinfo
                         where to_char(pp.birthday,'mm') = to_char(add_months(current_date,-1),'mm')
                           and status = 1
                       ) pp
                    on p.id = pp.player_id
                       left join
                       (select game_participant,max(played) last_asp
                          from ticket
                         group by game_participant
                       ) t
                    on p.id = t.game_participant
                       left join
                       (select player_id,
                               street,
                               housenumber,
                               zip,
                               city
                          from address
                         where status = 1
                       ) pa
                    on p.id = pa.player_id
                Regards

                Etbin
                • 5. Re: SQL sub query runs too slow.
                  user3206995
                  thanks to all for you input I tried user648773      choice and it made some improvments but no a great deal.

                  In the end these indexes were created.

                  CREATE INDEX DWE_New1 ON PERSINFO ( STATUS, TO_CHAR(BIRTHDAY, 'mm'))
                  CREATE INDEX DWE_New2 ON PARTICIPANT ( ID, ENABLED, LANG, EMAIL, ISL_EMAIL, VERIFIED, STATE)
                  and it did make some difference.

                  But I am not convinced about adding these indexes for a single query.
                  Ideas most welcomed

                  Thanks
                  • 6. Re: SQL sub query runs too slow.
                    user648773
                    Hello,

                    1- The first index is a great idea. Maybe the most helpfull it seems to be the birthday column that appear to be more selective than status.
                    Is there alredy any index on the birthday coumn?
                    Maybe the index could be just only on the column birthday and change the condition like this:
                    Birthday between to_date(to_char(trunc(trunc(ADD_MONTHS(CURRENT_DATE, -1),'month')),'ddmm')||(to_char(birthday,'yyyy')),'ddmmyyyy')
                     and 
                    to_date(to_char(trunc(CURRENT_DATE,'month'),'ddmm')||(to_char(birthday,'yyyy')),'ddmmyyyy')
                    By this way you are creating an index that could be use for more querys in the future.
                    Of course you can add the status column if it is helpful.

                    2- The second index have many columns. Oracle recomend not create index on more than three columns. Index with many columns increment the contention and time when you insert or update the table.
                    I understand that by this index you only access to the index and you don't need to acces the table, that is faster.
                    Maybe accessing by an index on the id column is enough.

                    Edited by: user648773 on 10/04/2013 10:22: I made a change in the condition
                    • 7. Re: SQL sub query runs too slow.
                      chris227
                      Without the plan it's kind of guessing, but you maay try to replace the subquery with a join
                      change
                      
                      (select TO_CHAR(MAX(t.PLAYED), 'dd.mm.yyyy HH:MI:SS') from ticket t where t.GAME_PARTICIPANT = p.ID) as LAST_SPA,
                      
                      in TO_CHAR(t.LAST_SPA, 'dd.mm.yyyy HH:MI:SS') LAST_SPA
                      
                      and add
                      
                      left outer join 
                      (select GAME_PARTICIPANT, max(played) last_spa
                       from ticket
                       group by GAME_PARTICIPANT) t
                      on
                      t.GAME_PARTICIPANT = p.ID
                      Edited by: chris227 on 10.04.2013 06:39
                      • 8. Re: SQL sub query runs too slow.
                        user3206995
                        All,

                        Thank you for your inputs, they are really great suggestions.
                        • 9. Re: SQL sub query runs too slow.
                          user648773
                          I'm glad to hear that.