Hi,
I have the below query which looks odd to me. I am trying to give it a good shape in terms of performance and reducing the no. of sub queries.


SELECT SHP_PT_ID FROM TAB1
WHERE SHP_PT_TYP_CD = 'SSC'
AND LOC_ID IN ( SELECT LOC_ID FROM TAB2
WHERE LOC_RLTNSP_TYP_CD = 'SSCRST'
AND RLTD_LOC_ID IN ( SELECT LOC_ID FROM TAB1 WHERE FCILTY_NB = 2910)
);

Any pointers will be helpful..

Thanks
MPasha

I suspect that regardless of whether you use sub-queries or joins, DB2 will re-write the query in the most optimal form for you. Just make sure you have the right indexes set up, and that you have run runstats.

Thanks for your reply Marcus. I was curious because I am using the same table in a separate subquery again.

If you look at the query, the data I need is from a table(TAB1) which should satisfy a condition in another table(TAB2) which in turn satisfy a different condition in the first table(TAB1). To achieve this, I am using the first table separately in the 2nd sub query, which appears a little dirty/odd to me and If the table is huge, then my query will either DIE or Kill DB2!!!

Q1: REMOVE THE SUB-Q which contains TAB1
SELECT SHP_PT_ID FROM TAB1
WHERE SHP_PT_TYP_CD = 'SSC'
AND LOC_ID IN ( SELECT LOC_ID FROM TAB2
WHERE LOC_RLTNSP_TYP_CD = 'SSCRST'
AND RLTD_LOC_ID = LOC_ID AND FCILTY_NB = 2910);

Q2: REMOVE THE SUB-Q which contains TAB2
SELECT SHP_PT_ID FROM TAB1, TAB2
WHERE SHP_PT_TYP_CD = 'SSC'
AND LOC_ID = TAB2. LOC_ID AND TAB2.LOC_RLTNSP_TYP_CD = 'SSCRST'
AND RLTD_LOC_ID = LOC_ID AND FCILTY_NB = 2910;

Nidm,
I think you might have to look a bit closer at what you gave Pasha, as I am pretty sure it will not return the correct results.

Pasha,
Go along with info that Marcus has given you already. The proper indexes should help quite a bit, try using index advisor. Also, the performance will depend greatly on how restrictive the 2 subqueries are. If they return thousands of values, then of course your performance will be a concern. Those items should be the concern rather than how many subqueries you have written in your SQL. Also, I can only see you getting rid of one of those subqueries, but it still involves accessing tab1 twice and the query rewrite is probably already being done by DB2.

Dave

Thanks for your time folks.
Nidm-
I am afraid, I had already tried the options you gave and they wont work as the values in the cols are not directly related. Thats why there is no relation between the first tab1 query and 2nd TAB1 sub-query.

dav1mo - I agree with you and marcus, but something in me still tells that this is not the way I should write a query, which means there is much better a way(I am not sure what )

Do an explain and look at the DB2 query re-write.