CODEWITHSUNDEEP
sqloracle-databaseplsql
user3789200
user3789200

Reputation: 1186

Efficiency comparison

Please kindly explain me whether these two give difference performances in execution. Both methods are giving nearly same results within nearly same time, but I need to know when it comes for selection of millions of data, what would be more suitable. Or are their execution logic inside same?

1)
  CURSOR get_cost_ IS
  SELECT EQUIP, COST
  FROM   EXIPMNT
  WHERE  mch_code = mch_code_
  AND contract = contract_
  AND CRE_DATE BETWEEN TO_DATE('01/01/' || year_, 'DD/MM/YYYY')
  AND TO_DATE('01/01/' || year_, 'DD/MM/YYYY') + INTERVAL '1' YEAR;

BEGIN

FOR rec_ IN get_cost_ LOOP
  IF (rec_. EQUIP = 'M') THEN
     material_ := material_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'P') THEN
     personal_ := personal_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'T') THEN
     tool_facility_ := tool_facility_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'X') THEN
     expense_ := expense_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'E') THEN
     external_ := external_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'D') THEN
     direct_sales_ := direct_sales_ + nvl(rec_.cost, 0);
  END IF; 

 NULL;
 END LOOP;

2) 

select sum(case when equip = 'M' then cost end) material_total,
       sum(case when equip = 'P' then cost end) personal_total,
       sum(case when equip = 'T' then cost end) tool_facility_total,
       sum(case when equip = 'X' then cost end) expense_total,
       sum(case when equip = 'E' then cost end) external_total,
       sum(case when equip = 'D' then cost end) direct_sales_total,
       sum(cost) total
from   exipmnt
where  mch_code = mch_code_
and    contract = contract_
and    cre_date >= TO_DATE('01/01/' || year_, 'DD/MM/YYYY')
and    cre_date < TO_DATE('01/01/' || year_, 'DD/MM/YYYY') + INTERVAL '1' YEAR
and    equip in ('M', 'P', 'T', 'X', 'E', 'D');

Upvotes: 0

Views: 61

Answers (2)

krokodilko
krokodilko

Reputation: 36107

Hi... I'm not quite understandable, why first query is a bad practice.

Make a simple experiment, and you will see why it is a bad practice.
Make a very simple queries (without WHERE clause), one in SQL and the other in PL/SQL, against a table with a few hundred thousands rows, and compare how longthey take.
Both queriest just retrieve all rows from the table and calculate a result.

Below is what I did on my PC:

Create a test table

create table EXIPMNT as
select column_value as equip,
       trunc(dbms_random.value(0,100)) as cost
from table(DBMSOUTPUT_LINESARRAY('M','P','T','X'))
cross join
all_objects
;

select count(*) from EXIPMNT;

  COUNT(*)
----------
    294852

An then the last query (SQL)

set timing on
select sum(case when equip = 'M' then cost end) material_total,
       sum(case when equip = 'P' then cost end) personal_total,
       sum(case when equip = 'T' then cost end) tool_facility_total,
       sum(case when equip = 'X' then cost end) expense_total,
       sum(case when equip = 'E' then cost end) external_total,
       sum(case when equip = 'D' then cost end) direct_sales_total,
       sum(cost) total
from   exipmnt;

Elapsed: 00:00:00.078

and the first query (PL/SQL)

set timing on
DECLARE
  CURSOR get_cost_ IS
  SELECT EQUIP, COST
  FROM   EXIPMNT;

     material_ number;
     personal_ number;
     tool_facility_ number;
     expense_ number;
     external_ number;
     direct_sales_ number;

BEGIN
FOR rec_ IN get_cost_ LOOP
  IF (rec_. EQUIP = 'M') THEN
     material_ := material_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'P') THEN
     personal_ := personal_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'T') THEN
     tool_facility_ := tool_facility_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'X') THEN
     expense_ := expense_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'E') THEN
     external_ := external_ + nvl(rec_.cost, 0);
  ELSIF (rec_. EQUIP = 'D') THEN
     direct_sales_ := direct_sales_ + nvl(rec_.cost, 0);
  END IF; 

 NULL;
 END LOOP;
end;
/

anonymous block completed
Elapsed: 00:00:03.265

Now check results (elapsed time) - 3.265 sec. (PL/SQL) and 0.078 sec. (SQL)

3.265 / 0.078 = 41,85,
this means, that in this simple experiment PL/SQL is about 42 times slower than simple SQL.

That means the difference is 4200% !!!

Because of the above PL/SQL is perceived as a "bad practice".

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

You want to know what's better: PL/SQL or SQL for the task given.

PL/SQL is a programming language using SQL. So PL/SQL sents the query to the SQL machine and gets results that it reads record for record. This is a rather slow process. Okay, both the PL/SQL and the SQL engine are on the same machine, so there is not that much overhead, but still...

Your second approach shows that the task is easily solvable with one query. The optimizer will read the query and find the most appropriate way to execute it. Maybe there is an index helping look up the eqip code, so the task becomes very easy. Or the optimizer decides to execute this in several parallel tasks to speed up the process.

You see, there are many things possible for the DBMS when it executes SQL. Simply getting raw data and evaluate it from a programming language doesn't make use of these possibilities. So when you can execute the same task with SQL only, this is usually the way to go. (Though PL/SQL is a great language and can make complex tasks very readable, so even when you can write a query for a task you may still decide for PL/SQL for this reason.)

Upvotes: 1

Related Questions