Home » RDBMS Server » Performance Tuning » Query taking long time to execute (oracle 9i)
Query taking long time to execute [message #287757] |
Thu, 13 December 2007 06:37 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
venkatadeekshi
Messages: 17 Registered: October 2007
|
Junior Member |
|
|
By using the following query I Created a View. If i query the view for the required data it is taking around 1 minute to fetch the data.
The output of the query gives all the time slots for which each seat is available on each day starting with the effective start date in the xxcgs_adm_seat_mst table
It ends with last CAL_DAY of the xxcgs_admin_calendar table.
The XXCGS_SEAT_MST table contains all the seats data and XXCGS_ADM_ALLOCATION table contains all the allocated seat data.
The following is the query
SELECT Seat_Id,
sl_No,
cal_Day,
MIN(s_No) mIn_s_No,
MAX(s_No) Max_s_No,
MIN(From_Time) mIn_Time,
MAX(To_Time) Max_Time
FROM (SELECT Seat_Id,
sl_No,
cal_Day,
s_No,
pRev_s_No,
From_Time,
To_Time,
COUNT(CASE
WHEN pRev_s_No IS NULL THEN 'x'
WHEN pRev_s_No != s_No - 1 THEN 'x'
ELSE NULL
END) OVER(PARTITION BY Seat_Id,sl_No ORDER BY s_No) AS grp
FROM (SELECT Seat_Id,
sl_No,
cal_Day,
s_No,
From_Time,
To_Time,
Lag(s_No) OVER(PARTITION BY Seat_Id,sl_No ORDER BY s_No) AS pRev_s_No
FROM (SELECT Seat_Id,
sl_No,
cal_Day,
s_No,
MIN(From_Time) From_Time,
MAX(To_Time) To_Time
FROM (SELECT mst.Seat_Id,
cal.sl_No,
cal.cal_Day,
tm.s_No,
tm.From_Time,
tm.To_Time
FROM xxcgs_Adm_Seat_mst mst,
xxcgs_AdmIn_Calendar cal,
Time_Slots tm
WHERE cal.cal_Day >= Nvl(mst.Effective_Start,SYSDATE - 60)
AND cal.cal_Day <= Nvl(mst.Effective_End,SYSDATE + 365)
MINUS
SELECT Seat_Id,
sl_No,
cal_Day,
s_No,
From_Time,
To_Time
FROM (SELECT Alloc.Seat_Id,
cal.sl_No,
cal.cal_Day,
tm.s_No,
tm.From_Time,
tm.To_Time,
SUM(DECODE(Least(Alloc.Time_From,tm.From_Time),Alloc.Time_From,
DECODE(Greatest(Alloc.Time_To,tm.To_Time),Alloc.Time_To,
1,
0),
0)) Availability
FROM xxcgs_Adm_Allocation Alloc,
xxcgs_AdmIn_Calendar cal,
Time_Slots tm
WHERE cal.cal_Day >= Alloc.AllocFrom_Date
AND cal.cal_Day <= Alloc.AllocTo_Date
GROUP BY Alloc.Seat_Id,
cal.sl_No,
cal.cal_Day,
tm.s_No,
tm.From_Time,
tm.To_Time
ORDER BY cal.sl_No,
tm.s_No) a
WHERE a.Availability = 1)
GROUP BY Seat_Id,
sl_No,
cal_Day,
s_No)))
GROUP BY Seat_Id,
sl_No,
cal_Day,
grp
ORDER BY Seat_Id,
cal_Day
Tables used in this view
1)
CREATE TABLE XXCGS_ADM_ALLOCATION
(
SEAT_ID NUMBER,
SEAT VARCHAR2(250 BYTE),
LOCATION VARCHAR2(50 BYTE),
CATEGORY VARCHAR2(50 BYTE),
ALLOCFROM_DATE DATE ,
ALLOCTO_DATE DATE ,
TIME_FROM VARCHAR2(11 BYTE),
TIME_TO VARCHAR2(11 BYTE) );
The table is having the following sample data:
Insert into xxcgs_adm_allocation
Values(1121, 'NI-F1-SW-074', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007','00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1140, 'NI-F1-SW-076', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1122,'NI-F1-SW-075', 'MUMB.NIRL.I.A', 'VOICE.SOE', '12-DEC-2007', '27-12-2007', '04:30', '10:59');
Insert into xxcgs_adm_allocation
Values(1054,'NI-F1-SW-072', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1141,'NI-F1-SW-077', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
values(1142,'NI-F1-SW-078', 'CHEN.SPNR.I.A', 'VOICE.NON-SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
Insert into xxcgs_adm_allocation
Values(1120,'NI-F1-SW-073', 'MUMB.NIRL.I.A', 'DATA.SOE', '12-DEC-2007', '27-12-2007', '00:00', '04:59');
COMMIT;
2) CREATE TABLE XXCGS_ADM_SEAT_MST
(
SEAT_ID NUMBER,
LOCATION_ID NUMBER,
CATEGORY_ID NUMBER,
EFFECTIVE_START DATE,
EFFECTIVE_END DATE);
Sample data:
Insert into xxcgs_adm_seat_mst Values (1053, 1028, 1028,'15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1049, 1028, 1028,'15-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1054, 1037, 1036,'16-OCT-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1121, 1028, 1028,'12-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1120, 1028, 1028,'12-JAN-2007',NULL);
Insert into xxcgs_adm_seat_mst values (1080, 1040, 1038,'11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1100, 1040, 1028, 11-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1122, 1028, 1038,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1140, 1028, 1028,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1141, 1037, 1036,'01-DEC-2007',NULL);
Insert into xxcgs_adm_seat_mst Values (1142, 1037, 1036,'01-DEC-2007',NULL);
COMMIT;
3)
CREATE TABLE XXCGS_ADMIN_CALENDAR
(
CAL_DAY DATE,
SL_NO NUMBER
);
This table contains 3 years data.
In this table I inserted data with this code:
DECLARE
v_cal_Day DATE;
v_sl_No NUMBER;
BEGIN
v_cal_Day := '01-JAN-2007';
v_sl_No := 1;
LOOP
INSERT INTO xxcgs_AdmIn_Calendar
VALUES (v_cal_Day,
v_sl_No);
v_cal_Day := v_cal_Day + 1;
v_sl_No := v_sl_No + 1;
EXIT WHEN v_cal_Day > '31-DEC-2009';
END LOOP;
END;
4)
CREATE TABLE TIME_SLOTS
(
S_NO NUMBER(2),
HOURS VARCHAR2(6 BYTE),
FROM_TIME VARCHAR2(6 BYTE),
TO_TIME VARCHAR2(6 BYTE)
);
data in this table:
insert into time_slots values(1, '00:30', '00:00', '00:29');
insert into time_slots values(2, '01:00', '00:30', '00:59');
insert into time_slots values(3, '01:30', '01:00', '01:29');
insert into time_slots values(4, '02:00', '01:30', '01:59');
insert into time_slots values(5, '02:30', '02:00', '02:29');
insert into time_slots values(6, '03:00', '02:30', '02:59');
insert into time_slots values(7, '03:30', '03:00', '03:29');
insert into time_slots values(8, '04:00', '03:30', '03:59');
insert into time_slots values(9, '04:30', '04:00', '04:29');
insert into time_slots values(10, '05:00', '04:30', '04:59');
insert into time_slots values(11, '05:30', '05:00', '05:29');
insert into time_slots values(12, '06:00', '05:30', '05:59');
insert into time_slots values(13, '06:30', '06:00', '06:29');
insert into time_slots values(14, '07:00', '06:30', '06:59');
insert into time_slots values(15, '07:30', '07:00', '07:29');
insert into time_slots values(16, '08:00', '07:30', '07:59');
insert into time_slots values(17, '08:30', '08:00', '08:29');
insert into time_slots values(18, '09:00', '08:30', '08:59');
insert into time_slots values(19, '09:30', '09:00', '09:29');
insert into time_slots values(20, '10:00', '09:30', '09:59');
insert into time_slots values(21, '10:30', '10:00', '10:29');
insert into time_slots values(22, '11:00', '10:30', '10:59');
insert into time_slots values(23, '11:30', '11:00', '11:29');
insert into time_slots values(24, '12:00', '11:30', '11:59');
insert into time_slots values(25, '12:30', '12:00', '12:29');
insert into time_slots values(26, '13:00', '12:30', '12:59');
insert into time_slots values(27, '13:30', '13:00', '13:29');
insert into time_slots values(28, '14:00', '13:30', '13:59');
insert into time_slots values(29, '14:30', '14:00', '14:29');
insert into time_slots values(30, '15:00', '14:30', '14:59');
insert into time_slots values(31, '15:30', '15:00', '15:29');
insert into time_slots values(32, '16:00', '15:30', '15:59');
insert into time_slots values(33, '16:30', '16:00', '16:29');
insert into time_slots values(34, '17:00', '16:30', '16:59');
insert into time_slots values(35, '17:30', '17:00', '17:29');
insert into time_slots values(36, '18:00', '17:30', '17:59');
insert into time_slots values(37, '18:30', '18:00', '18:29');
insert into time_slots values(38, '19:00', '18:30', '18:59');
insert into time_slots values(39, '19:30', '19:00', '19:29');
insert into time_slots values(40, '20:00', '19:30', '19:59');
insert into time_slots values(41, '20:30', '20:00', '20:29');
insert into time_slots values(42, '21:00', '20:30', '20:59');
insert into time_slots values(43, '21:30', '21:00', '21:29');
insert into time_slots values(44, '22:00', '21:30', '21:59');
insert into time_slots values(45, '22:30', '22:00', '22:29');
insert into time_slots values(46, '23:00', '22:30', '22:59');
insert into time_slots values(47, '23:30', '23:00', '23:29');
insert into time_slots values(48, '24:00', '23:30', '23:59');
commit;
I dont have any knowledge of tuning the sql queries
can anybody give me a solution to speed up the query
[Formatted the OP's code with http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl
Please format your code next time]
[Updated on: Mon, 17 December 2007 20:55] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: Query taking long time to execute [message #288510 is a reply to message #288330] |
Mon, 17 December 2007 21:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Your SQL has 4 layers of aggregation, 2 layers of analytics, and a MINUS.
This can only be descibed as SPAGHETTI CODE.
I acknowlege that this may not be your fault. Someone else probably wrote the code and you are just trying to fix it. This is probably just the product of dozens of iterations of problems and fixes - surely nobody ever set out to write code like this.
The problem is that it is self-perpetuating. It is impossible for a normal human to fully grasp the functionality of this query enough so that they can change it without introducing some other small (or large) bug. Every time it changes it just guarantees another cycle through testing and development.
But you can be the hero. You can fix it forever - and here's how!
You need to have the courage to tell your architect / boss / project manager that this SQL will always suck no matter what you do. What you need is a little more time - 3 or 4 days at most - to redevelop it in PL/SQL.
The SQL is currently trying to implement a logical set of rules - an algorithm - that has been defined from the business requirements. But nobody can tell what this is because of the strucure of the code. Use cursors, temporary tables, associative arrays, whatever you need to make a program that follows a logical algorithm. When you do this, people will be able to understand what it is doing because it will follow logical steps.
Now there's a certain amount of pride at stake here, because you've invested so much time in this thing already. You don't want to "fail" by not finishing what you started. Trust me - if you finish it as-is you will have failed; because the next guy who tries to work with it will suffer the same problems as you.
Do you want to inflict that on someone else?
If you have the guts to make a fresh start, read here on some tuning techniques to make sure your PL/SQL is efficient.
Ross Leishman
|
|
|
Re: Query taking long time to execute [message #288856 is a reply to message #288510] |
Tue, 18 December 2007 23:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
venkatadeekshi
Messages: 17 Registered: October 2007
|
Junior Member |
|
|
Hi Ross,
Thanks for your valuable Suggestions. Actually I was also involved in developing some part of this code. But I am the only person which involved in tuning the code to improve its performance.
As you said I will try the other way of developing it.
Thanks & Regards
Deekshit
|
|
|
Goto Forum:
Current Time: Wed Sep 11 12:21:22 CDT 2024
|