Query join and optimization

Question

I have a table INFO whose design is like this

id - bigint
Name - varchar2
refid - bigint
status - int
ExpDate - datetime

status values can be 0,1,2,3,4
refid is the foreign key of other table which we are going to use too

I want to write a query where all records should come from this table with status 0, 1, 2, 3, but only 7 latest records by expdate should come whose status is 4.

I can't figure out how can we achieve this in T-SQL.

Please help me out or give me suggestion so that I can start writing it.

Upto now I have written

SELECT * 
FROM INFO 
WHERE STATUS IN (0,1,2,3) AND 
      REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')

SELECT TOP 7
FROM INFO 
WHERE STATUS=4 AND 
      REFID IN(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW') 
ORDER BY EXPDATE DESC

i need to join them??? how.. suggest and also the query

(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')

is coming twice how to optimize it thanks..

Do you want to optimize the query or remove duplicate records? — pavanred, Feb 23 '12 at 08:38
There are no duplicate records, because `STATUS` is different. — coms, Feb 23 '12 at 08:40
Ok.. the "is coming twice how to optimize it thanks.." made me think there could be duplicate records. — pavanred, Feb 23 '12 at 08:43
we are using sub queries in it.. same sub query is repeated.. i want to optimize that part — 1Mayur, Feb 23 '12 at 08:56
So we are both wrong cause I've checked it and both queries have exacly the same execution plan and number of reads. Durations on profiler are nearly same. — devarc, Feb 23 '12 at 11:21
@devarc: In which case REFTABLE is a parent of INFO : that is, you get at most 1 REFTABLE row per INFO. If you had "n" REFTABLE rows then the queries are different. Also see http://explainextended.com/2009/06/16/in-vs-join-vs-exists Finally, post your script if you're so sure... — gbn, Feb 23 '12 at 12:48
As I said in any case (1-1,1-many,mamy to many) I can see that execution plans are same. I've queried 2-3 milion record tables in many ways and always same result. Just check it. Maybe 2008r2 optimizer is more advanced than in 2005(I hope so :)) — devarc, Feb 23 '12 at 14:35
And in that link that you have pasted you have confirmation on that. — devarc, Feb 23 '12 at 14:52

score 4 · Accepted Answer · answered Feb 23 '12 at 08:41

Just UNION your 2 current queries together (you need a derived table to get the TOP though)

SELECT ...
FROM INFO 
WHERE STATUS IN (0,1,2,3) AND 
      REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')
UNION ALL
SELECT ...
FROM 
    (
    SELECT TOP 7 ...
    FROM INFO 
    WHERE STATUS=4 AND 
          REFID IN(SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW') 
    ORDER BY EXPDATE DESC
    ) T

You can get fancier, but it may not be as efficient:

SELECT *
FROM
    (
    SELECT ...,
       ROW_NUMBER() OVER (ORDER BY EXPDATE DESC) AS rn
    FROM INFO 
    WHERE REFID IN (SELECT REFID FROM REFTABLE WHERE REFCHAIN='BMW')
    ) T
WHERE
    rn <= 7 OR STATUS <= 4

sub queries are efficient or joins? – 1Mayur Feb 23 '12 at 09:13 — 1Mayur, Feb 23 '12 at 09:13

pavanred · Answer 2 · 2012-02-23T08:46:20.110

1

Do you want to optimize this or do you want to just remove the duplicate records.

If you want to remove duplicate records then you can try using UNION-

SELECT * FROM INFO 
INNER JOIN REFTABLE ON INFO.refId = REFTABLE.Id
WHERE REFCHAIN = 'BMW' AND INFO.status IN (0,1,2,3)
UNION
SELECT FROM 
    (
       SELECT TOP 7 FROM INFO 
       INNER JOIN REFTABLE ON INFO.refId = REFTABLE.Id
       WHERE INFO.status=4 AND REFCHAIN='BMW'
       ORDER BY EXPDATE DESC
    ) T

edited Feb 23 '12 at 08:46

answered Feb 23 '12 at 08:40

pavanred

12,717
14
53
59

Note that the UNION adds overhead which can be avoided using IN (or EXISTS). Reason: IN/EXISTS is a semi-join, JOIN is an equi-join. See http://stackoverflow.com/a/6966259/27535 – gbn Feb 23 '12 at 09:06

score 0 · Answer 3 · answered Feb 23 '12 at 08:53

0

for latest 7 values in ur reftable u can use this query--

select * from REFTABLE where REFID not in( select top (select count(*)-7 from info) REFID from REFTABLE )

it would have better if u could post the table structure..

answered Feb 23 '12 at 08:53

Teju MB

1,333
5
20
37

score 0 · Answer 4 · answered Feb 23 '12 at 09:04

gbn and Pavanred answers about UNION solve your problem "how to join this results". If it comes to optimization you can create temp table for results from select. Then query executes only once.

But... I think query is too trivial to optimize this in that way.

Anyway:

SELECT REFID 
INTO #temp1 
FROM REFTABLE WHERE REFCHAIN='BMW'

And then use this in both queries like that:

WHERE REFID IN (SELECT REFID FROM #temp1)

Or like that

SELECT ...
FROM INFO inf
INNER JOIN #temp1 t ON inf.REFID = t.REFID
...

Query join and optimization

4 Answers4