Connect by prior run amuck...

[Wholsea]

Have an employee table set up like this:


tbl_employees
--------------------
employee_id
manager_id
org_id

Manager_id is the employee_id of the employee's manager

We are trying to do an update on another table that controls some organizational assignments, the other table is set up like this:

tbl_assigned_orgs
--------------------
employee_id
start_date
end_date
org_id

Query looks like this:

update tbl_assigned_orgs set end_date = sysdate where
employee_id = president_emp_id
and sysdate > start_date and end_date is null
and org_id not in (
select distint org_id from tbl_employees
where org_id is not null
start with employee_id = tbl_assigned_orgs.employee_id connect by prior employee_id = manager_emp_id
)

We solved the "issue" we are having in our development and production environments by issuing a "Compute Statistics" command before running this query, in dev/test environments this query runs in < 15 minutes, in our production environment, however, this query runs between 3 and 6 HOURS...

The employee table has about 8,000 rows in it.

Any better ideas on how to get the same results, or at least speed up this query?


ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[Thargy]

Wholsea,

it's impossible to give a definitive answer on the information available.

However, I would suggest the following:-

Check that the prod data volumes match dev/test. If need be, copy the prod tables to dev and recomput stats.

Is the prod server being hammered by other concurrent tasks, hence wiping out available memory and/or CPU power?

Is the indexing on prod identical to dev/test?

Is the prod server as powerful as dev/test?

Is the prod environment getting data from slow mass storage?

Unless dev/test is identical to prod, you aren't comparing like with like. Hence the above questions.

Regards

Tharg

Grinding away at things Oracular

 

[Dagon]

Have you tried doing an ESTIMATE statistics instead of COMPUTE ?

 

[Wholsea]

Dev/Test on older slower machine, stuff usually runs a lot faster in production than in dev/test.

Tables/indexes everything is identical as far as DB set up and volume...

The only difference is machine speed, which production should be a lot faster...

I'll look at using estimate statistics instead of compute...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise