Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: Update With Subquery Too Slow

    Hi ,

    I need to do an update on a table,the query i used is given below.the problem is its taking TOO SLOW,there may be a better way to accomplish what I'm trying to do .

    UPDATE COMPONENTE_MATERIALE_COSTO SET COSTO_MATERIALE = CASE WHEN ID_COMPONENTE_MATERIALE = 'FE' THEN COSTO_MATERIALE * 1.0005 WHEN ID_COMPONENTE_MATERIALE = 'GH' THEN COSTO_MATERIALE * 1.0050 WHEN ID_COMPONENTE_MATERIALE = 'AL' THEN COSTO_MATERIALE * 1.5000 WHEN ID_COMPONENTE_MATERIALE = 'M_ELE' THEN COSTO_MATERIALE * 5.0000 ELSE COSTO_MATERIALE END ,DATA_ORA_AGGIORNAMENTO = CASE WHEN ID_COMPONENTE_MATERIALE = 'FE' THEN TO_DATE('2000-9-24','YYYY-MM-DD') WHEN ID_COMPONENTE_MATERIALE = 'GH' THEN TO_DATE('2000-9-24','YYYY-MM-DD') WHEN ID_COMPONENTE_MATERIALE = 'AL' THEN TO_DATE('2000-9-24','YYYY-MM-DD') WHEN ID_COMPONENTE_MATERIALE = 'M_ELE' THEN TO_DATE('2000-9-24','YYYY-MM-DD') ELSE NULL END WHERE ID_COMPONENTE IN ( SELECT ID_COMPONENTE FROM COMPONENTE )


    Thanks in advance
    Krishna

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that CASE (which makes this query to seem huge and complicated) doesn't slow things down, but your WHERE condition ... Are there many rows in "componente" table? If so, it HAS to be slow as no index can be used here.

    How about omiting the complete WHERE clause? You could easily do it if all "componente_materiale_costo.id_componente"s are contained in the "componente" table.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Pity your SQL is so badly formatted. Here's a nicer version:
    Code:
    UPDATE COMPONENTE_MATERIALE_COSTO 
    SET COSTO_MATERIALE = CASE WHEN ID_COMPONENTE_MATERIALE = 'FE' THEN COSTO_MATERIALE * 1.0005 
                               WHEN ID_COMPONENTE_MATERIALE = 'GH' THEN COSTO_MATERIALE * 1.0050
                               WHEN ID_COMPONENTE_MATERIALE = 'AL' THEN COSTO_MATERIALE * 1.5000 
                               WHEN ID_COMPONENTE_MATERIALE = 'M_ELE' THEN COSTO_MATERIALE * 5.0000 
                               ELSE COSTO_MATERIALE 
                               END 
    ,   DATA_ORA_AGGIORNAMENTO = CASE WHEN ID_COMPONENTE_MATERIALE = 'FE' THEN TO_DATE('2000-9-24','YYYY-MM-DD')
                                      WHEN ID_COMPONENTE_MATERIALE = 'GH' THEN TO_DATE('2000-9-24','YYYY-MM-DD') 
                                      WHEN ID_COMPONENTE_MATERIALE = 'AL' THEN TO_DATE('2000-9-24','YYYY-MM-DD') 
                                      WHEN ID_COMPONENTE_MATERIALE = 'M_ELE' THEN TO_DATE('2000-9-24','YYYY-MM-DD')
                                      ELSE NULL
                                      END
    WHERE ID_COMPONENTE IN ( SELECT ID_COMPONENTE FROM COMPONENTE )
    There should be an index on COMPONENTE_MATERIALE_COSTO.ID_COMPONENTE to support that WHERE clause, assuming it is needed at all (which it may not be, as Littlefoot has already pointed out).

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Sorry to jump way off subject, but Tony you should consider adding a sticky to the top of the forum page telling everyone the tags they need to use to format code correctly - It definitely gets questions answered quicker as I've seen you say!
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    you should also have an index on COMPONENTE.ID_COMPONENTE - this will allow oracle to rewrite the subquery to a join (not saying it will, but it might). Depending on how many rows are in each table, the IN could be inefficient. And, depending on the version of Oracle, the optimizer setting, the stats, and a plethora of other things, Oracle may or may not perform a straight NL join, as opposed to a MERGE or HASH. You could try an EXISTS subquery (again, Oracle may do it for you anyway, but give it a try):

    UPDATE COMPONENTE_MATERIALE_COSTO x
    ...
    WHERE EXISTS (SELECT NULL FROM COMPONENTE
    where ID_COMPONENTE = x.ID_COMPONENTE )
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by ss659
    Sorry to jump way off subject, but Tony you should consider adding a sticky to the top of the forum page telling everyone the tags they need to use to format code correctly - It definitely gets questions answered quicker as I've seen you say!
    Done - quickly knocked together a replacement for the previous sticky thread. Any suggestions gratefully received!

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Perhaps EXISTS would be better in the where clause and we might see some
    improvement. HOWEVER! Why not post the EXPLAIN plan and a TKPROF so
    we know what the hell is going on????


    PHP Code:
    UPDATE COMPONENTE_MATERIALE_COSTO 
    SET COSTO_MATERIALE 
    = CASE WHEN ID_COMPONENTE_MATERIALE 'FE' THEN COSTO_MATERIALE 1.0005 
                               WHEN ID_COMPONENTE_MATERIALE 
    'GH' THEN COSTO_MATERIALE 1.0050
                               WHEN ID_COMPONENTE_MATERIALE 
    'AL' THEN COSTO_MATERIALE 1.5000 
                               WHEN ID_COMPONENTE_MATERIALE 
    'M_ELE' THEN COSTO_MATERIALE 5.0000 
                               
    ELSE COSTO_MATERIALE 
                               END 
    ,   DATA_ORA_AGGIORNAMENTO = CASE WHEN ID_COMPONENTE_MATERIALE 'FE' THEN TO_DATE('2000-9-24','YYYY-MM-DD')
                                      
    WHEN ID_COMPONENTE_MATERIALE 'GH' THEN TO_DATE('2000-9-24','YYYY-MM-DD'
                                      
    WHEN ID_COMPONENTE_MATERIALE 'AL' THEN TO_DATE('2000-9-24','YYYY-MM-DD'
                                      
    WHEN ID_COMPONENTE_MATERIALE 'M_ELE' THEN TO_DATE('2000-9-24','YYYY-MM-DD')
                                      ELSE 
    NULL
                                      END
    WHERE EXISTS
      
    select null from COMPONENTE 
        where COMPONENTE_MATERIALE_COSTO
    .id_componente COMPONENTE.id_componente 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    On the subject of readability, isn't it the same as:
    Code:
    UPDATE componente_materiale_costo 
    SET    costo_materiale = costo_materiale *
           CASE id_componente_materiale
                WHEN 'FE' THEN 1.0005 
                WHEN 'GH' THEN 1.005
                WHEN 'AL' THEN 1.5 
                WHEN 'M_ELE' THEN 5 
                ELSE 1
           END 
         , data_ora_aggiornamento =
           CASE id_componente_materiale
                WHEN 'FE' THEN DATE '2000-09-24'
                WHEN 'GH' THEN DATE '2000-09-24'
                WHEN 'AL' THEN DATE '2000-09-24'
                WHEN 'M_ELE' THEN DATE '2000-09-24'
                ELSE NULL
           END
    WHERE  id_componente IN
           ( SELECT id_componente
             FROM   componente );

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    William, I think you messed up in the "DATE" department.

    WHEN 'FE' THEN DATE '2000-09-24'
    WHEN 'GH' THEN DATE '2000-09-24'
    WHEN 'AL' THEN DATE '2000-09-24'
    WHEN 'M_ELE' THEN DATE '2000-09-24'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You are right - since they are all the same, it would be neater as:

    Code:
    UPDATE componente_materiale_costo
    SET    costo_materiale = costo_materiale *
           CASE id_componente_materiale
                WHEN 'FE' THEN 1.0005
                WHEN 'GH' THEN 1.005
                WHEN 'AL' THEN 1.5
                WHEN 'M_ELE' THEN 5
                ELSE 1
           END
         , data_ora_aggiornamento =
           CASE WHEN id_componente_materiale IN ('FE','GH','AL','M_ELE')
                THEN DATE '2000-09-24'
                ELSE NULL
           END
    WHERE  id_componente IN
           ( SELECT id_componente
             FROM   componente );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •