Help me to better design my query so I can gain more performance

Question

I need to find so called first entry in database for items regarding their partners. I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed conception therefore this query can be written much better. So I am asking you to help me redesign this query to get better performance.
My running database system is Sql Server 2008

declare @od_datuma datetime
declare @do_datuma datetime

set @od_datuma='2011-12-01'
set @do_datuma='2011-12-31'


select 
doc.PAR_ID
,sdo.art_id
,MIN(doc.id) as doc_id
from 
dokumenti
inner join DOC on dokumenti.tip=DOC.TIP
inner join SDO on DOC.ID=SDO.DOC_ID
left  outer join (
    select par_id,art_id from dokumenti
            inner join DOC on dokumenti.tip=DOC.TIP
            inner join SDO on DOC.ID=SDO.DOC_ID     
            where   dokumenti.NABAVA =1 and
                    dokumenti.KOL_UL=1 and
                    DOC.DATUM<@od_datuma
                )  as stari_uazi on DOC.PAR_ID=stari_uazi.PAR_ID and SDO.ART_ID=stari_uazi.ART_ID
where 
dokumenti.NABAVA =1 and
dokumenti.KOL_UL=1 and
stari_uazi.ART_ID is null and 
doc.datum between @od_datuma and @do_datuma
group by doc.PAR_ID,sdo.art_id

Answer 1

The first this I would try is to add where doc.datum>=@od_datuma and doc.datum <=@do_datuma because the having clause. Maybe it will give a hint to the query planner. Then the big question is: why the outer join with the subquery? Does the " where .. stari_uazi.ART_ID is null " mean that you want to exclude all the rows returning from the subquery? I suspect the subquery is the real killer because the search for DOC is not bounded ( DOC.DATUM <@od_datuma ), so it might fetch a huge number of rows, which have not real purpose since the parent query excludes these rows anyway..

My guess is that if you suppress the subquery ( left outer join .. ) you will have something very close from what you want, much faster. If you need to suppress some rows from the result set you can put them in a temporary table and delete them afterwards, this might be faster than the outer join subquery.

Update: so if you want to keep only the rows between @od / @do _datum, remove the left join and add the condition where DOC.DATUM between @od_ and @do_ You will have the same result and it will be much faster.