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

    Question Unanswered: Give me suggestions to improve performance

    Hello,

    I need to do an Update on the below table to calculate Aggregate Qty requied for EACH node(article_code_child+seq_id >> makes one NODE)

    The UPDATE Query is working ,MY PROBLEM is Its Very SLOW,no INDEX in that table.....

    Please Give me suggestions to improve performance

    UPDATE piano_tree P1 SET P1.agg_node_qty =(SELECT SUM(P2.needed_qty) FROM piano_tree P2 WHERE P1.article_code_child=P2.article_code_child AND P1.seq_id=P2.seq_id GROUP BY P2.article_code_child,P2.seq_id);

    CREATE TABLE PIANO_TREE (
    SEQ_ID NUMBER(8) NOT NULL,
    ROOT_ID NUMBER(8) NOT NULL,
    ROOT_PATH VARCHAR2(2000) NOT NULL,
    PARENT_CODE VARCHAR2(15) NOT NULL,
    ARTICLE_CODE_FATHER VARCHAR2(15) NOT NULL,
    ORDER_CODE VARCHAR2(15) NOT NULL,
    ARTICLE_CODE_CHILD VARCHAR2(15),
    RESOURCE_CODE VARCHAR2(10),
    NEEDED_QTY NUMBER(15,5) NOT NULL,
    STOCK_QTY NUMBER(15,5) NOT NULL,
    REQ_QTY NUMBER(15,5) NOT NULL,
    AGG_NODE_QTY NUMBER(15,5) NOT NULL,
    AGG_ORD_QTY NUMBER(15,5) NOT NULL
    );


    Thanks in advance

    Krishakumar VM

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If there's no index, why don't you create one? It would be nice to have a primary key, actually ... but, if you really, really don't want/need one (I can't imagine why not), then create index on columns included in the WHERE clause, such as

    CREATE INDEX pt_idx
    ON piano_tree (seq_id, article_code_child);

    Primary key constraint would be

    ALTER TABLE piano_tree
    ADD CONSTRAINT pk_pt PRIMARY KEY
    (seq_id, article_code_child);

    Include index tablespace clauses in both statements (if you have one).

    However, I doubt that column 'article_code_child' should be part of a primary key. 'seq_id' and 'root_id' seem to be better candidates for primary key columns to me.

    So ... create primary key on 'seq_id' and 'root_id' plus another index on 'seq_id' and 'article_code_child' columns.

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    Best suggestion... learn how to get and to understand the EXPLAIN output; the so-called "query execution plan." This is what the so-called "optimizer" produces in response to your SQL input, and it is the actual step-by-step internal instructions that will be given to the query-engine to produce your results. It will tell you, in advance, whether your query will run efficiently, and what you may do to improve it.

    The query-optimizer considers three inputs: (1) the SQL string; and (2) the topology of the database; and (3) guesses about the layout of the data as expressed by the "statistics."

    Although the ideal of SQL is that "you tell me what you want to do and I'll decide on my own how to do it," the reality is that there are usually several ways to write a functionally-equivalent query; and "the computer is still just a machine; still stoopid." Sometimes a very slight re-write of the query will make a huge difference.

    Sometimes also, the query will still be slow and inefficient no matter what you do, but you can break it down into a series of smaller queries; smaller chunks of data. INSERT and UPDATE queries are candidates for this. Because of the "rollback" capabilities of SQL, sometimes the engine will get (how can I say this delicately?), basically constipated on too-much data. The computer has no choice but to "do what it is told or die trying," but if you don't want it to actually do the latter, sometimes you must change the way that you present the chore to it. Break it down into smaller, simpler steps; process smaller chunks of data at one time; reach COMMIT-points sooner; and so-on.

    No matter how big and capacious your system is (and no matter how urgently the salesman wants to sell you a bigger, more capacious one) you can still overload it easily with SQL.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Start with this query which assums this gets you your proper data.
    I doubt you even need the article_code_child and seq_id columns, but
    I included them anyway so you can double-check your work.
    PHP Code:
    select 
      article_code_child

      
    seq_id,
      
    sum(needed_qtyover 
        
    (partition by article_code_childseq_idnew_agg_node_qty
    from piano_tree

    now, throw that query in to a cursor with a FOR UPDATE clause.
    PHP Code:
    declare
      
    cursor cUpdate is
        select 
          article_code_child

          
    seq_id,
          
    sum(needed_qtyover 
            
    (partition by article_code_childseq_idnew_agg_node_qty
        from piano_tree 
    for update;
    begin
      
    for vUpdate in cUpdate loop
        update piano_tree
          set agg_node_qty 
    vUpdate.new_agg_node_qty
        where current of cUpdate
    ;
      
    end loop;
      
    commit;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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