Stored Proc issue

[neskin]

hi, I have a sp .It is work fine but I think it could be written more efficient but i don't know how

select FlightDir,sum(day_event_mon) day_event_mon,sum(night_event_mon)night_event_mon
from
(select FlightDir,
 sum(case when days = 'day'then 1 else 0 end ) day_event_mon,
sum(case when days = 'night'then 1 else 0 end ) night_event_mon
from
(select (case when runwayname like '34%' then 'NInitialDepartures'              
			     when runwayname like '16%' then 'SInitialDepartures'end)FlightDir,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day'  else
      'night'end) days
from oper o,ganout g
where adflag ='D'and 
o.opnum = g.opnum and
dir = 1 )
group by days ,FlightDir
union 
select gatename as Flightdir, 
sum(case when days = 'day'then 1 else 0 end ) day_event_mon,
 sum(case when days = 'night'then 1 else 0 end ) night_event_mon
from
(select gatename,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day'  else
      'night'end) days
from oper o,ganout g
where adflag in ('A','D')and 
o.opnum = g.opnum and
dir = 1 and gatename in('PS Arrival East','PS Arrival West','PSDeptsouth','PSdeptnorth','ElliotBayDept'))
group by days,gatename)e
group by FlightDir

any ideas

 

[SantaMufasa]

Neskin,

What makes you think you could write this more efficiently? If you think it could be more efficient, can you suggest ways that you could improve it?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...and...what makes you think that this is a stored procedure? The code looks to me like it is a SQL query with a complex in-line view.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

Hi Mufasa,
it is a part of the stored proc

 

[Thargy]

neskin,

If this really is part of a stored procedure, then I would say that your department has some problems.

If one of our developers ever came to me to code review such an SP, it would fail inside 5 seconds. It's what I call "wall of ink" code, i.e. there's absolutely tons of it on one page.

Using multiple sequential SQL statements inside SP's is a fundamental flaw. Your SQL should be encapsulated within functions, which in turn should be encapsulated within a package. This approach will assist with precisely what you're struggling with.

Encapsulated SQL simplifies the high level code and makes it easy to read. Because it's easy to read, it's easy to understand the logical flow. Because it's easy to understand the logical flow, it's easier to spot errors and inefficiencies.

Once any errors and/or inefficiencies are spotted, it's easier to fix them, because they will be encapsulated (contained within) a small logical unit of code, i.e. one of the encapsulated functions. Obviously enough, it is significantly easier to fix a small five line function than a 50 line procedure.

<Dusts self off, descends from soap box>

Regards

Tharg

P.S. the illustrious Mr Feuerstein has published a book called "Oracle PL/SQL Best Practices" published by O'Reilly, ISBN 0-596-00121-5, which, according to its back cover retails on your side of the pond for $19.95. I suggest that you get your dev team to acquaint themselves with at least its basic tenets.

Grinding away at things Oracular

 

[carp]

Amen to buying Mr. Feuerstein's book - AND reading it - AND thinking about it - AND keeping it close by so you can repeat the above as necessary!
Without knowing anything about your indexes, data volumes, etc, the only thing I could suggest might be replacing "UNION" with "UNION ALL". However, this might allow duplicate rows in your inline view which, without knowing your requirements) may or may not violate the logic/business rules of your procedure. On the other hand, if you can guarantee the two data sets will always be mutually exclusive, then this might speed your query up a bit.

 

[Thargy]

carp,

I am concerned that my previous post comes across as a rant. Your opinion is?

T

Grinding away at things Oracular

 

[Dagon]

There are different views on whether it is best to use complex SQL or simplify the SQL as much as possible and do the work in the SP code, which is the Feuerstein view. Years ago, I had an argument with a developer over a very complex set of nested decode statements in a cursor declaration, which I said should be converted into a more readable set of "if" statements. The developer argued that the decodes were more efficient and unfortunately the team leader agreed with thim, so I was forced to back down.

People tend to write complex SQL statements because it's more efficient than breaking it down into lots of individual statements, each of which requires you to open and close a cursor for every row.

My comments on the original SQL:

"UNION ALL" could clearly be used as the FlightDir from the first part of the query must either be 'NInitialDepartures' or 'SInitialDepartures. In the second part, it is the gatename, which is constrained to be one of ('PS Arrival East','PS Arrival West','PSDeptsouth','PSdeptnorth','ElliotBayDept'). There is therefore no possibility of overlap and UNION ALL could be used.

That being the case, what does the second group by achieve ? There is no overlap between the two data sets, so the second group by cannot further compress the data. I think you will find that, if you just run this, you will get identical results to the original:

select FlightDir,
 sum(case when days = 'day'then 1 else 0 end ) day_event_mon,
sum(case when days = 'night'then 1 else 0 end ) night_event_mon
from
(select (case when runwayname like '34%' then 'NInitialDepartures'              
                 when runwayname like '16%' then 'SInitialDepartures'end)FlightDir,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day'  else
      'night'end) days
from oper o,ganout g
where adflag ='D'and 
o.opnum = g.opnum and
dir = 1 )
group by days ,FlightDir
union all
select gatename as Flightdir, 
sum(case when days = 'day'then 1 else 0 end ) day_event_mon,
 sum(case when days = 'night'then 1 else 0 end ) night_event_mon
from
(select gatename,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day'  else
      'night'end) days
from oper o,ganout g
where adflag in ('A','D')and 
o.opnum = g.opnum and
dir = 1 and gatename in('PS Arrival East','PS Arrival West','PSDeptsouth','PSdeptnorth','ElliotBayDept'))
group by days,gatename

 

[carp]

T -
My opinion: Negative on "rant".

 

[neskin]

Thank u dagon for your comments,
In my db I have fields
runwayname(34L,34R,16L,16R) ,adflag (A or D)
what i basicly need to count numbers of flights if runway Like 34% then it is north departures if runway 16 then it is south departures and split them if date or night.
and also count numbers of fligths in gatenames

runwayname gatename actualtime
34L PS Arrival West day
34L PS Arrival West day
34L PS Arrival West day
16R PSdeptnorth night
34L PSdeptnorth day
34L ElliotBayDept day
34L PSdeptnorth day
34L ElliotBayDept day

the out come
flight dir day_count night_count
NInitialDepartures 7 0
SInitialDepartures 0 1
PS Arrival West 3 0
PSdeptnorth 2 1
ElliotBayDept 2 0

that what I try to get with my sql script

regards,
Nat

 

[carp]

I think this will give you what you want:

SELECT DECODE(runwayname,'34L','NInitialDepartures', 
                                           '34R', 'NInitialDepartures', 
                                           '16L',','NInitialDepartures',
                                           '16R'','NInitialDepartures') runwayname,
              SUM(DECODE(adflag,'D',1,0) day_count,
              SUM(DECODE(adflag,'N',1,0) night_count
FROM my_table
GROUP BY DECODE(runwayname,'34L','NInitialDepartures', 
                                           '34R', 'NInitialDepartures', 
                                           '16L',','NInitialDepartures',
                                           '16R'','NInitialDepartures') 
UNION ALL
SELECT gatename, 
              SUM(DECODE(adflag,'D',1,0) day_count,
              SUM(DECODE(adflag,'N',1,0) night_count
FROM my_table
GROUP BY gatename;