Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanx, Yes I do want to exclude all the rows returning from the subquery. I want to show only rows which is between two dates with distinct Par_id per art_id. –  adopilot Jan 28 '12 at 15:18
    
I know that is "NOT IN" faster then "LEFT OUTER JON and IS NULL" but I need to exclude rows with two conditions. I also did change my original query but I still suffering with performance. –  adopilot Jan 28 '12 at 15:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.