Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    30

    Unanswered: From Imperfect To Perfect. From 53 Views To The Optmization

    Hi To All,

    tank to the pretious help of Shammat, Pat, Bibjet, in order to prepare a pivot of the total of every item in my_table for every of 53 weeks of the year, for every year from to current_year until 2009 in reverse, I arrived to this result.

    The effect is that the total of every item of every of 53 weeks of the year is inserted in a table with a name autogenerated from the function.

    But now -I am not able to use Pg/psql and I have to go from imperfect to perfect- it is reasonable to ask the Wise Men of this mater if the function can be optmized. In fact, in my case, it is executed using 180.000 milliseconds

    Code:
    do
    $body$
    declare 
      week_nr integer;
      anno_nr integer;
      stmt    text;
    begin
      week_nr := 1;
      year_nr := 2012;
    
    for year_nr in reverse 2012..2009 loop
    
       for week_nr in 1..53 loop
        stmt := 'insert into year'||year_nr::text||
                ' select fkid_reparto, SUM(totale), week '||
                'from my_table '||
                'where week = '||week_nr::text||
                ' and year= '||year_nr::text||
                ' GROUP BY fkid_reparto, settimana'; 
              execute stmt;
              week_nr := week_nr + 1;
            end loop;
    
      year_nr := year_nr -1;
      week_nr := 1;
     end loop;
    end;
    $body$
    Tanks in advance
    Gianni Pinna.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What is your question?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2012
    Posts
    30
    Hi Shammat,
    Tank You for Your prompt reply.

    Wheter the funcion could be optmized, this is the question. The show on function is perfect for the purpose of myne, but the execution time is 3 minutes.

    Is it possible to optimize the show on function, or to use another way in order to the same end or purpose?

    Tanks in advance

    Gianni Pinna.

  4. #4
    Join Date
    Dec 2012
    Posts
    30
    I excuse for a little mistake wich i correct:
    Wether = Wheter

    Hi Shammat,
    Tank You for Your prompt reply.

    Whether the funcion could be optmized, this is the question. The show on function is perfect for the purpose of myne, but the execution time is 3 minutes.

    Is it possible to optimize the show on function, or to use another way in order to the same end or purpose?

    Tanks in advance

    Gianni Pinna.
    Edit/Delete Message

Posting Permissions

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