Solved

Stuck on slow query

Posted on 2003-10-26
19
574 Views
Last Modified: 2012-05-04
Here’s the table layout…

e_veh_log *************************************
evnet_yr | event_nbr | veh_reg_id | veh_log_assign_dt
3             7543           03L435         03-OCT-2003 04:56:12 PM
3             7543           99L345         03-OCT-2003 04:22:19 PM
3             7545           98L678         03-OCT-2003 09:18:44 AM
3             7546           03L435         05-OCT-2003 11:02:39 PM  

run_ve_assign **********************************
v_reg_id | v_cc_id | v_dr_id | v_pos3_id | v_pos4_id | runsheet_dt
99L543     A2195     B4567     S7654                           03-OCT-2003
98K459     C2987     D4998     F7834         A8976         03-OCT-2003
03L435     F5692     H8235     W2974        Y8632          03-OCT-2003
03L435     F5692     D9845                                          05-OCT-2003    

SQL*************************
s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and 'F5692' in (R.v_cc_id, R.v_dr_id, R.v_pos_id, R.v_pos4_id ) "
s=s&"and e.veh_log_assign_dt > (sysdate - 30)"


I’m trying to retrieve all records from both tables where ‘F5692’ is found in any of the four fields in the R table, and in the last 30 days.

My queries will eventually time out on my ASP page without any output.

Thank you in advance for any assistance...
0
Comment
Question by:westbergk
  • 6
  • 6
  • 3
  • +3
19 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9623725
Make sure you have indexes on these fields:
R.runsheet_dt ,  e.veh_log_assign_dt , e.v_reg_id, R.veh_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos_id, R.v_pos4_id

Then use this query:
Basically, I change the line "s=s&"and 'F5692' in (R.v_cc_id, R.v_dr_id, R.v_pos_id, R.v_pos4_id ) "
to "s=s&"and (R.v_cc_id ='F5692' OR R.v_dr_id='F5692' OR  R.v_pos_id ='F5692' OR R.v_pos4_id='F5692' ) "
and put it at the end , the improvement is significant since "=" instead "IN" will take advantage of indexes.


s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and e.veh_log_assign_dt > (sysdate - 30) "
s=s&"and (R.v_cc_id ='F5692' OR R.v_dr_id='F5692' OR  R.v_pos_id ='F5692' OR R.v_pos4_id='F5692' ) "

0
 
LVL 1

Author Comment

by:westbergk
ID: 9623836
Thank you for the reply.

I've tried this before, but I just tried your lines, and it still takes forever, if at all.

The db is set in stone, and any tweaking is not an option... I'm not sure what the index situation is.  Is there an sql statement that will show this?

These tables have at least 7 years worth of data.  My guess is 200k rows for: run_ve_assign  and 30k rows on e_veh_log.

I do have a printed schema, which shows the following.


e_veh_log
******************
event_yr            PK
event_nbr            PK
v_reg_id            PK
veh_log_assign_dt      PK


run_ve_assign
******************
runsheet_dt      PK
veh_reg_id            PK


Thanks again...
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9624580
Surely you could add indices if needed.
To check your indices do this from schema owner

select *
from user_indexes
where table_name IN ('E_VEH_LOG_E',' RUN_VE_ASSIGN');
Analyze the tables with compute statistics
Also run explain plan on query to see what the optimizer is doing

You should definitely being using index on parts of where clause.
Consider putting index on the following. Of course you have to weigh
the impact if this is a heavy dml on the fields
R.v_cc_id, R.v_dr_id,  R.v_pos_id, R.v_pos4_id


seazodiac,

"=" is not faster then IN as the optimizer will transform this into "or" statements.
0
 
LVL 5

Expert Comment

by:gmyers
ID: 9624584
Firstly, the outer join (+) on your reg_id is serving no purpose, since you are also matching R.runsheet_dt = e.veh_log_assign_dt and you are expecting a date in e.veh_log_assign_dt and 'F5692' in run_ve_assign so you can't be looking for a 'made-up' outer join row from either of those tables
Get rid of it and see if that solves your problem.

To see what indexes you have :
SELECT table_name, index_name, column_position, column_name
from all_tab_indexes
where table_name in ( upper('run_ve_assign'), upper('e_veh_log'))
order by 1,2,3,4

Your printout suggests that runsheet_dt is the first field of the primary key, so it should be indexed.
As such, I'd re-write the last line of the query as :
"and R.runsheet_dt  between (sysdate - 30) and sysdate + 1;"
By using a BETWEEN, it's more likely to use an index (it's got a finite range to look through).
You say " last 30 days" so I assume you are not expecting events in the future.
However I've used sysdate+1 just in case there's timezone issues (eg an event that happened at 10pm in New Zealand may have the local time recorded, which will appear as a 'future' date to some-one in the US where 10pm is still several hours away).

Unlike the previous commentor, I'd suggest that you DON'T want to use indexes on R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id (unless F5692 is a rare value in all four columns). If you did, it would mean scanning four indexes instead of (at worst) a single full table scan. The data range scan should limit it to around 1/84 (ie one month from 7 years of data) of the table which should be sufficient.

"My guess is 200k rows for: run_ve_assign  and 30k rows on e_veh_log."

It seems relatively small to have a performance problem (though outer joins can do that). It takes just a few seconds for my database to do a full table scan of 200,000 rows.
If the tables have 7 years of data, then that's about 2,500 per month (maybe more in recent months if 'activity' has increased over that time), and about 350 on e_veh_log.
0
 
LVL 1

Expert Comment

by:maturner
ID: 9625945
Why don't you post the plan for this SQL - it will make it easier to see what is *actually* happening.
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 250 total points
ID: 9627218
A few things:

*  I noticed your runsheet_dt has no times.  If you still want to join them, they will need to be via the trunc function.
*  You are outter joining the veh_reg, but not the date.  Didn't you intend to outter join both?
*  If the e_veh_log record MUST be present, can't you simply lose the outter join AND the date join?

I've expanded the letter abbreviations:

select  e_veh_log.event_yr,
        e_veh_log.event_nbr,
        e_veh_log.veh_reg_id,
        e_veh_log.veh_log_assign_dt,
        run_ve_assign.runsheet_dt,
        run_ve_assign.v_reg_id,
        run_ve_assign.v_cc_id,
        run_ve_assign.v_dr_id,
        run_ve_assign.v_pos3_id,
        run_ve_assign.v_pos4_id
from    e_veh_log,
        run_ve_assign
where   run_ve_assign.v_reg_id          = e_veh_log.veh_reg_id       /*  Add outter joins to BOTH lines, or is outter join really needed?    */
and     trunc(run_ve_assign.runsheet_dt = trunc(e_veh_log.veh_log_assign_dt)     /* Is this really needed?   */
and     e_veh_log.veh_log_assign_dt     > (sysdate - 30)
and     'F5692' in (run_ve_assign.v_cc_id,
                    run_ve_assign.v_dr_id,
                    run_ve_assign.v_pos_id,
                    run_ve_assign.v_pos4_id)
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9627935
Can you do some sanity check first in your database?
to me, it's very suspicious that you have date data type being displayed very differently.
for example:

veh_log_assign_dt field from e_veh_log table has this format "03-OCT-2003 04:56:12 PM";
whereas runsheet_dt column from run_ve_assign table has this format "03-OCT-2003".

Now I suspect that veh_log_assign_dt field is a VARCHAR2 type and runsheet_dt is a DATE type because 'DD-MON-YYYY' is the oracle default date format.

so in the SQLPLUS window do a "describle  e_veh_log " to check the data type of veh_log_assign_dt field. if that field is VARCHAR2 , change it to DATE type.


My second recommendation , based on the fact that you CANNOT change any thing in the database,
is that you should break your original query into 4 single queries below:

1.

s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and R.v_cc_id='F5692'  "
s=s&"and e.veh_log_assign_dt > (sysdate - 30)"

2.
s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and R.v_dr_id='F5692'  "
s=s&"and e.veh_log_assign_dt > (sysdate - 30)"

3.
s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and R.v_pos_id='F5692'  "
s=s&"and e.veh_log_assign_dt > (sysdate - 30)"

4.
s="select e.event_yr, e.event_nbr, e.veh_reg_id, e.veh_log_assign_dt, "
s=s&"R.runsheet_dt, R.v_reg_id, R.v_cc_id, R.v_dr_id, R.v_pos3_id, R.v_pos4_id "
s=s&"from e_veh_log e, run_ve_assign R "
s=s&"where R.runsheet_dt = e.veh_log_assign_dt "
s=s&"and e.v_reg_id = R.veh_reg_id(+) "
s=s&"and R.v_pos4_id ='F5692'  "
s=s&"and e.veh_log_assign_dt > (sysdate - 30)"


0
 
LVL 1

Author Comment

by:westbergk
ID: 9629948
Thanks everyone for the speedy replies.

Here is what I have tried:
1) Removing outer joins = 0 records returned, should be many.
2) Confirmed both foelds are legit DATE type
3) Tried searching single field for id = still slow/time outl
4) Changed date requirement to BETWEEN = no change
5) Negative findings on Index queries.  I'm sure there are, but the query returned 0.

Let me explain what it is I'm trying to do, which I should have done first...

The e_veh_log, is a table which is capturing when a vehicle is sent somewhere.  The movement/event is given a year number, sequence number, a datetime stamp and the vehicle id.  There can be multiple vehicles at an event, but will never have duplicate vehicle id's in the same event.

The run_ve_assign is a table capturing who is assigned to be on the vehicle and when there assigned.  It contains a vehicle id, crewchief postion, driver position, passenger 3, passenger 4 and the assignement date.  There are 60 vehicles which have people assigned to each position, each day.

DESIRED OUTCOME:
Determine what events an individual was at and what position they were in, using there person ID and a date range.  Goal is 30 days.  So If I ran the query now, using the data below I would find that F5692 was at the following events:

3-7543
3-7546

e_veh_log *************************************
evnet_yr | event_nbr | veh_reg_id | veh_log_assign_dt
3             7543           03L435         03-OCT-2003 04:56:12 PM
3             7543           99L345         03-OCT-2003 04:22:19 PM
3             7545           98L678         03-OCT-2003 09:18:44 AM
3             7546           03L435         05-OCT-2003 11:02:39 PM  

run_ve_assign **********************************
v_reg_id | v_cc_id | v_dr_id | v_pos3_id | v_pos4_id | runsheet_dt
99L543     A2195     B4567     S7654                           03-OCT-2003
98K459     C2987     D4998     F7834         A8976         03-OCT-2003
03L435     F5692     H8235     W2974        Y8632          03-OCT-2003
03L435     F5692     D9845                                          05-OCT-2003    


Thanks everyone, for time spent on this issue...
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9630016
just to satisfy my curiosity:
can you post the results of these commands:

1. at the sqlplus window

SQL>desc e_veh_log
and
SQL>desc run_ve_assign
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9630038
Can you time the individual query I posted above?
0
 
LVL 1

Author Comment

by:westbergk
ID: 9630094
Here you go...  

I've been trimming the field names to make it more manageable.  The ** are the fields I have mentioned in previous samples.

describe runsheet_ve_assign;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
** RUNSHEET_DT                               NOT NULL DATE
** VEHICLE_REGISTRATION_ID                   NOT NULL VARCHAR2(8)
 RUNSHEET_VEH_REMARKS_TX                            VARCHAR2(60)
 FIRE_STATION_ID                                    CHAR(1)
** VEHICLE_CREWCHIEF_ID                               VARCHAR2(9)
** VEHICLE_DRIVER_ID                                  VARCHAR2(9)
** VEHICLE_POSITION3_ID                               VARCHAR2(9)
** VEHICLE_POSITION4_ID                               VARCHAR2(9)
 VEHICLE_POSITION5_ID                               VARCHAR2(9)
 VEHICLE_POSITION6_ID                               VARCHAR2(9)


describe event_vehicle_log;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
** EVENT_YR                                  NOT NULL NUMBER(2)
** EVENT_NBR                                 NOT NULL NUMBER(5)
** VEHICLE_REGISTRATION_ID                   NOT NULL VARCHAR2(8)
** VEHICLE_LOG_ASSIGN_DT                     NOT NULL DATE
 VEHICLE_LOG_ASSIGN_TM                     NOT NULL NUMBER(4)
 VEHICLE_LOG_REL_DT                                 DATE
 VEHICLE_LOG_REL_TM                                 NUMBER(4)
 VEHICLE_LOG_EQUIP_CST                              NUMBER(9,2)
 VEHICLE_LOG_PERS_COST                              NUMBER(9,2)
 VEHICLE_LOG_ON_SC_TM                               VARCHAR2(8)
 VEHICLE_LOG_MAN_HOURS                              NUMBER(9,2)
 VEHICLE_LOG_ONSCENE_DT                             DATE

0
 
LVL 1

Author Comment

by:westbergk
ID: 9630166
It timed out at 7 minutes.  

I'm doing this thru asp because the only oppertunity I have to work on these reports is from home...  
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9630185
In this case, you query is just fine.

you have to ask your DBA to come in to do some tuning on the database side.

I know you previously remarked that your database is set in stone, but hey, you are working with a bad tuned database.

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9630229
I am sure that you are missing the indexes on the required columns.

In addition, you probably need to re-create the primary key. when you have composite primary key,

the order of the columns in the primary key is somewhat significant.  in order to fully, efficiently utilize the primary key, it should be defined the right order.

for example , in your query 'where" clause,
if you will be using

where a=<a value> and b=<a value> and c=<a value>

then your primary key should be created as "constraint <name> primary key(a, b, c)", in which a is the leading columns, b is the second, and c is the last.  if you build the primary key like "primary key(c, b, a)" you actually did not use the index on the primary key at all.
0
 
LVL 5

Accepted Solution

by:
gmyers earned 250 total points
ID: 9630965
"Here is what I have tried:
1) Removing outer joins = 0 records returned, should be many."

As another poster remarked, given the data, you will need to use TRUNC
Simple test scenario below :

create table e
  (event_year number(2), event_nbr number(5),
  vehicle_registration_id varchar2(8), vehicle_log_assign_dt date);

create table r
  (runsheet_dt date, vehicle_registration_id varchar2(8), vehicle_crewchief_id varchar2(9),
   vehicle_driver_id varchar2(9),vehicle_position3_id varchar2(9), vehicle_position4_id varchar2(9));

Data as per your post :

insert into e values (3,7543,'03L435',to_date('03-Oct-2003 16:56:12','DD-Mon-yyyy HH24:mi:ss'));
insert into e values (3,7543,'99L345',to_date('03-Oct-2003 16:22:19','DD-Mon-yyyy HH24:mi:ss'));
insert into e values (3,7545,'98L678',to_date('03-Oct-2003 09:18:44','DD-Mon-yyyy HH24:mi:ss'));
insert into e values (3,7546,'03L435',to_date('05-Oct-2003 23:02:39','DD-Mon-yyyy HH24:mi:ss'));

insert into r (vehicle_registration_id, vehicle_crewchief_id ,
 vehicle_driver_id ,vehicle_position3_id , vehicle_position4_id ,runsheet_dt) values
('99L543', 'A2195', 'B4567', 'S7654', '', to_date('03-OCT-2003','dd-mon-yyyy'));
insert into r (vehicle_registration_id, vehicle_crewchief_id ,
 vehicle_driver_id ,vehicle_position3_id , vehicle_position4_id ,runsheet_dt) values
('98K459', 'C2987', 'D4998', 'F7834', 'A8976' , to_date('03-OCT-2003','dd-mon-yyyy'));
insert into r (vehicle_registration_id, vehicle_crewchief_id ,
 vehicle_driver_id ,vehicle_position3_id , vehicle_position4_id ,runsheet_dt) values
('03L435', 'F5692', 'H8235', 'W2974', 'Y8632' , to_date('03-OCT-2003','dd-mon-yyyy'));
insert into r (vehicle_registration_id, vehicle_crewchief_id ,
 vehicle_driver_id ,vehicle_position3_id , vehicle_position4_id ,runsheet_dt) values
('03L435', 'F5692', 'D9845', '', '', to_date('05-OCT-2003','dd-mon-yyyy'));

Original query :

select e.*, r.*
from e, r
where r.runsheet_dt = e.vehicle_log_assign_dt
and e.vehicle_registration_id = r.vehicle_registration_id (+)
and 'F5692' in (Vehicle_Crewchief_Id, Vehicle_Driver_Id, Vehicle_Position3_Id, Vehicle_Position4_Id)
and e.vehicle_log_assign_dt > (sysdate - 30)

will return no rows.
Query without outer join, but with trunc and BETWEEN will return the desired two rows.

select e.*, r.*
from e, r
where r.runsheet_dt = trunc(e.vehicle_log_assign_dt)
and e.vehicle_registration_id = r.vehicle_registration_id
and 'F5692' in (Vehicle_Crewchief_Id, Vehicle_Driver_Id, Vehicle_Position3_Id, Vehicle_Position4_Id)
and e.vehicle_log_assign_dt between (sysdate - 30) and sysdate +1
and  r.runsheet_dt between (sysdate - 30) and sysdate +1

EYear      Event_Nbr      VRegistration_Id      Vehicle_Log_Assign_Dt      Runsheet_Dt            
3      7543      03L435            03-OCT-2003 16:56:12      03-OCT-2003      F5692      H8235      W2974      Y8632
3      7546      03L435            05-OCT-2003 23:02:39      05-OCT-2003      F5692      D9845            

My suspicion is that you don't actually have any indexes on either table (or they are disabled or unusable)
However even without them, seven minutes should be enough for a full table scan on 230k rows

Since you said removing the outer join returned 0 rows, at least that didn't time out.
0
 
LVL 1

Author Comment

by:westbergk
ID: 9636243
gmyers... that was it.  dsacker mentioned this earlier, and I tried it just as it was posted.  The result was a timed out page.  When I tried gmyers post, it responded within 1 second, and with 30 days worth of data... very cool.

The only difference I could see was that dsacker had trunc on both date fields.  So after researching what TRUNC was used for, I'm not completely sure why it did not work the first time.  It was so fast that I was able to tie in 2 other tables which contained 'good to know' data.

So, I would like to split points between gmyers for the correct syntax, and dsacker for spotting the issue right out of the gate.  Would both of you agree?
0
 
LVL 1

Author Comment

by:westbergk
ID: 9636279
But most of all I wont to thank all of you for the awsome inputs.  I pray that all of you are paid what your worth... which these days I suspect not.  

Cheers to you all.
0
 
LVL 5

Expert Comment

by:gmyers
ID: 9637385
"So, I would like to split points between gmyers for the correct syntax, and dsacker for spotting the issue right out of the gate.  Would both of you agree? "
Agree here. Whatever split, I'm happy.

0
 
LVL 20

Expert Comment

by:dsacker
ID: 9655076
Much appreciated ... I've used trunc for quite awhile.  It takes the time off the date.  Sometimes it's just the simple things, eh? :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now