Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    98

    Unanswered: PL/SQL effeciency question

    Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

    it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

    So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

    I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

    Any suggestion is appreciated

    Mark

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL effeciency question

    Originally posted by mchih
    Right now I have a few PL/SQL procedures that calculate count(*) and sum(*) values for different reports. So far it's a daily operation, with around 80000 records per day (seems to be increasing by 10000 records every week though)

    it's not a complex procedure, just a lot of number crunching for each columns. Right now the basic logic is to use a separate SQL statement for each column(with different conditions, of course), and repeat it until all the reports are filled in.

    So far it's taking about 20 secs for going thru every 10000 records. Add the time it takes to load the records in (about 25-30 secs per 80000 records), and it becomes about 3 minutes of operation each day.

    I have been thinking about how I can improve the performance of the procedure. So far I am thinking about whether the performance would improve if I can change the logic by declaring a cursor for the records, and just go thru it one time, then put the value into different variables.

    Any suggestion is appreciated

    Mark
    Generally it is preferable to avoid procedural logic if you want best performance. Maybe you could combine all (or many of) your counts and sums into a single query using DECODE (or CASE) to filter the records:

    SELECT SUM( DECODE( col1, 'x', 1, 0 )) as COUNT_WHERE_COL1_IS X
    , COUNT(*) TOTAL_COUNT,
    , SUM( DECODE( col3, 123, col4, 0 )) as SUM_COL4_WHERE_COL3_IS_123
    ...
    FROM ...

  3. #3
    Join Date
    Nov 2002
    Posts
    98
    it might be difficult to put many of the query together, since each of them has a different where clause.

    eg:

    table temp(
    card_type
    card_amount
    message_type
    message_response
    .
    .
    .
    )

    a sample query would be
    select count(*), sum(card_amount) from temp
    where card_type = x
    and message_type = y
    and message_response = z;

    now that i think about it, it might be possible to use a GROUP BY to get all the values with similar WHERE clause, but I don't know how to store the values individually so i can access them later (eg. put it into different table)

    TIA

    Mark

Posting Permissions

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