Query verrry slow

Database
Enthusiast

Query verrry slow

Hi all!

This query takes a long time to run..Can this query be optimized..?

select distinct a.task,a.lgc_id,t.btch_id , cast(a.start_dt as date) from table a
inner join
(select task,lgc, max(btch_id) btch_id
from tableb
group by task,lgc) t
on a.btch_id=t.btch_id
and sys_calendar.calendar.calendar_date - cast(start_dt as date) > 7

Table B has a unique combination of task,lgc and batch id's. And I want to find the record with the max(batchid) for every unique combination of task and lgc. Then I want to find the matching records in table a on batch id and then use the start_date field in table a to find the difference bwetween the run date and the start_date. If this difference is greater than 7, I need the corresponding task and the lgc. I removed the distinct clause but it gives me duplicates with task,a.lgc_id andt.btch_id .
12 REPLIES
Enthusiast

Re: Query verrry slow

Dieter, can you please help me out?
Senior Apprentice

Re: Query verrry slow

This query is going to run for a long time, so you better shouldn't submit it :-)
You're joining to the calendar table without any join condition -> cross join to ∼ 73k rows.

If you check Explain you'll see that product join, this is why you have to use Distinct.

I can't follow all your narrative, but this sounds like a simple join.
What do you mean by "run date"? Today?
Then it sounds like:

select a.task,a.lgc_id,t.btch_id , cast(a.start_dt as date) from table a
inner join
(select task,lgc, max(btch_id) btch_id
from tableb
group by task,lgc) t
on a.btch_id=t.btch_id
where cast(start_dt as date) > current_date - 7

Could you post some sample data and expected result set?

Dieter
Enthusiast

Re: Query verrry slow

Table B : I need a unique combinations of Task and Lgc where batch_id is the highest. In this eg the task=29 with btch_id =4369 shd be selected. This has to be joined ti TableA to pull the strt_dt. This is the date when the task was last run. If the diff between today and thsi strt_dt is greater than 7 , we need to select that task and lgc.
Task btch_id Lgc grp
29 3219 11 11
29 4367 11 11
320 4367 48 48
329 1761 ? 1150

Table A
Btch_id Start_dt
5654 2010-06-18 05:28:48.827582
5653 2010-06-18 04:48:05.373722
5652 2010-06-18 04:39:24.740000
4368 2010-06-10 04:07:51.240000

So the output shd have the task=29 with lgc=11.
There is a twist for using the GRP too but will work on it once this part is done.

Thanks!
Enthusiast

Re: Query verrry slow

If I dont use the distinct clause then I get duplucates of task and Lgc . And I want a unique combination of task and lgc.
Senior Apprentice

Re: Query verrry slow

This is the query i already posted, i just made a mistake in the where-condition:
where cast(start_dt as date) < current_date - 7

Dieter
Enthusiast

Re: Query verrry slow

So do you thik the distinct clause is not needed?
Senior Apprentice

Re: Query verrry slow

If the batch_id is unique there's no need for distinct.

Dieter
Enthusiast

Re: Query verrry slow

Sorry I forgot to mention that I dont need the btch_id in teh final Output. My query had it because I wanted to check teh results. I just need a unique combination of task and lgc. I need the batch id just for joining the 2 tables and the start date to calculate the diff in days.
So shd I still have the distinct clause? I am trying to run the queries both ways right now and seeing the diff.
Enthusiast

Re: Query verrry slow

The query without distinct took 45 min. I am checking with distinct clause. DO you thin thsi query can be optimized further?