SQL Query Doubt

ksolomon · #1 (**permalink**) 02-20-06, 16:32

My current SQL query behaves poorly. The SQL is
SELECT DISTINCT A.no, A.subject_cd, seq_nbr FROM etl.student A
WHERE
A.subject_cd IN ('3800', '4000', '5000', '6000')
AND
A.seq_nbr IN ( SELECT MAX(seq_nbr) FROM etl.student B WHERE
A.no = B.no AND
A.subject_cd = B.subject_cd )

Sample Data
======================
no subject_cd seq_nbr
1 3800 1
1 3800 2
1 4000 3
2 4000 4
2 5000 5
3 5000 6
4 6000 7

Expected O/P
==========================
no subject_cd seq_nbr
1 3800 2
2 4000 4
2 5000 5
3 5000 6
4 6000 7

How can I improve the query. I have indexes on all the 3 columns and the database statistics is also updated using runstats.

Any idea ?

Thanks,Vij

sathyaram_s · #2 (**permalink**) 02-20-06, 16:57

You have a co-related subquery ... try using joins ..

The have not syntax checked it ...

with temp as
(
SELECT no,subject_cd,MAX(seq_nbr) FROM etl.student B
where subject_cd IN ('3800', '4000', '5000', '6000')
group by no,subject_cd
)
select distinct a.no,a.subject_cd,seq_nbr from etl.student A,
etl.student B
where
A.no=B.no and A.subject_cd=b.subject_cd
and A.subject_cd IN ('3800', '4000', '5000', '6000')

hmmm ... there should be a much better method ..

Cheers
Sathyaram

Quote:

Originally Posted by ksolomon

My current SQL query behaves poorly. The SQL is
SELECT DISTINCT A.no, A.subject_cd, seq_nbr FROM etl.student A
WHERE
A.subject_cd IN ('3800', '4000', '5000', '6000')
AND
A.seq_nbr IN ( SELECT MAX(seq_nbr) FROM etl.student B WHERE
A.no = B.no AND
A.subject_cd = B.subject_cd )

Sample Data
======================
no subject_cd seq_nbr
1 3800 1
1 3800 2
1 4000 3
2 4000 4
2 5000 5
3 5000 6
4 6000 7

Expected O/P
==========================
no subject_cd seq_nbr
1 3800 2
2 4000 4
2 5000 5
3 5000 6
4 6000 7

How can I improve the query. I have indexes on all the 3 columns and the database statistics is also updated using runstats.

Any idea ?

Thanks,Vij

sathyaram_s · #3 (**permalink**) 02-20-06, 17:04

max will anyways cause a sort ...

So, why not something like this ?

I have given a template only
select * from
(select no,subject_cd,rownumber() over (partition by no,subject_cd order by seq_num desc) as rowid from etl.student
) as x where rowid=1

HTH

Sathyaram

ksolomon · #4 (**permalink**) 02-20-06, 20:16

Thanks Sathyaram, The 2nd option which you suggested using the OLAP function gave me a huge improvement.

Rob den Heijer · #5 (**permalink**) 03-01-06, 05:49

This is probably the best solution, Sathyaram.
In some instances, it might help to create the index with 'reverse scan' enabled. This could improve max() function performance; differences between min() and max() should disappear, performance-wise.
In the statement where the index is created, use 'ALLOW REVERSE SCANS' .

See
http://safariexamples.informit.com/0...n/r0000919.htm

cheers,
Rob.

sathyaram_s · #6 (**permalink**) 03-01-06, 07:18

Ron, Thanks for pointing out my error ..

What I actually meant to say was 'Group by' will cause a sort ...

Thanks for the link

Sathyaram

Rob den Heijer · #7 (**permalink**) 03-01-06, 07:25

So sorry!
I meant you were right in supplying the 'partition by' solution. That's the most direct answer, and usage of this syntax needs to be encouraged!

But I discovered recently why min() can perform much better than max(), and I wanted to share this knowledge. Both your approach and mine can help others.

DBA-Jr · #8 (**permalink**) 03-02-06, 15:47

It seems one record is missing from ksolomon's "Expected O/P" - "1 4000 3". If I understand ksolomon's need corrently, The original code:

Quote:

SELECT DISTINCT A.no, A.subject_cd, seq_nbr FROM etl.student A
WHERE
A.subject_cd IN ('3800', '4000', '5000', '6000')
AND
A.seq_nbr IN ( SELECT MAX(seq_nbr) FROM etl.student B WHERE
A.no = B.no AND
A.subject_cd = B.subject_cd )

should be easily converted to a the simplest one:

Code:

SELECT no, subject_cd, max(seq_nbr) 
   FROM etl.student 
   WHERE subject_cd IN ('3800', '4000', '5000', '6000')
   group by no, subject_cd

The "group by" grantees "distinct" is applied. The subselect is simply unnecessary. Am I too simple to be correct? Please correct me if I am actually wrong.

sathyaram_s · #9 (**permalink**) 03-02-06, 18:36

hmmm ... may be ...

My understanding was that for each subject, select the row with the max seq number

ksolomon should clarify ...

Quote:

Originally Posted by DBA-Jr

It seems one record is missing from ksolomon's "Expected O/P" - "1 4000 3". If I understand ksolomon's need corrently, The original code:

should be easily converted to a the simplest one:

Code:

SELECT no, subject_cd, max(seq_nbr) 
   FROM etl.student 
   WHERE subject_cd IN ('3800', '4000', '5000', '6000')
   group by no, subject_cd

The "group by" grantees "distinct" is applied. The subselect is simply unnecessary. Am I too simple to be correct? Please correct me if I am actually wrong.

ksolomon · #10 (**permalink**) 03-02-06, 19:15

DBA-Jr was right. Even I missed that row in my expected output. Sorry for the confusion.

Rob den Heijer · #11 (**permalink**) 03-03-06, 03:23

DBA-Jr,
I think you are very much right. This is very simple, and (AFAICT) correct.
We can now wait for performance results (with or without ALLOW REVERSE SCANS, I hope)
Rob.

Internet Services

Web Development

Digital Marketing

Consumer Tech