sql - Very slow stored procedure -

i have hard time query optimization, i'm close point of database redesign. , stackoverflow last hope. don't think showing query enough i've linked not database script attached database backup in case don't want generate data hand

here can find both script , backup

the problems start when try following...

exec lockbranches @count=64,@lockedby='034c0396-5c34-4dda-8ad5-7e43b373ae5a',@lockedon='2011-07-01 01:29:43.863',@unlockon='2011-07-01 01:32:43.863' 

the main problems occur in part:

update b set b.lockedby = @lockedby,     b.lockedon = @lockedon,     b.unlockon = @unlockon,     b.complete = 1 (     select top (@count) b.lockedby, b.lockedon, b.unlockon, b.complete     objectives o     inner join generations g on g.objectiveid = o.id     inner join branches b on b.generationid = g.id     inner join     (         select sb.branchid branchid, sum(x.suitableprobes) suitableprobes         spiciebranches sb         inner join probes p on p.spicieid = sb.spicieid         inner join         (             select p.id, 1 suitableprobes             probes p /* ----> */ inner join results r on p.id = r.probeid /* ssms estimated execution plan says operation roughest */             group p.id             having count(r.id) > 0         ) x on p.id = x.id         group sb.branchid     ) x on x.branchid = b.id                 (o.active = 1)         , (b.sealed = 0)         , (b.generationno < o.branchgenerations)         , (b.lockedby null or datediff(second, b.unlockon, getdate()) > 0)         , (b.complete = 1 or x.suitableprobes = o.branchsize * o.estimatecount * o.probecount)         ) b 

edit: here amounts of rows in each table:

spicies         71536 results         10240 probes          10240 spiciebranches  4096 branches        256 estimates       5 generations     1 versions        1 objectives      1 

somebody else might able explain better can why quicker. experience tells me when have bunch of queries collectively run slow should quick in individual parts worth trying temporary table.

this quicker

alter procedure lockbranches -- add parameters stored procedure here   @count int,    @lockedon datetime,   @unlockon datetime,   @lockedby uniqueidentifier     begin    -- set nocount on added prevent result sets    -- interfering select statements.    set nocount on    --create temp table select spiciebranches.branchid branchid, sum(x.suitableprobes) suitableprobes  #branchsuitableprobecount spiciebranches  inner join probes p on p.spicieid = spiciebranches.spicieid   inner join   (        select p.id, 1 suitableprobes        probes p        inner join results r on p.id = r.probeid        group p.id        having count(r.id) > 0   ) x on p.id = x.id   group spiciebranches.branchid   update b set  b.lockedby = @lockedby,     b.lockedon = @lockedon,     b.unlockon = @unlockon,     b.complete = 1 (   select top (@count) branches.lockedby, branches.lockedon, branches.unlockon, branches.complete     objectives     inner join generations on generations.objectiveid = objectives.id     inner join branches on branches.generationid = generations.id     inner join #branchsuitableprobecount on branches.id = #branchsuitableprobecount.branchid           (objectives.active = 1)      , (branches.sealed = 0)      , (branches.generationno < objectives.branchgenerations)      , (branches.lockedby null or datediff(second, branches.unlockon, getdate()) > 0)      , (branches.complete = 1 or #branchsuitableprobecount.suitableprobes = objectives.branchsize * objectives.estimatecount * objectives.probecount) ) b  end 

this quicker average execution time of 54ms compared 6 seconds original one.

edit

had , combined ideas rbarryyoung's solution. if use following create temporary table

select sb.branchid branchid, count(*) suitableprobes #branchsuitableprobecount   spiciebranches sb inner join probes p on p.spicieid = sb.spicieid exists(select * results r r.probeid = p.id) group sb.branchid 

then can down 15ms 400x better started with. looking @ execution plan shows there table scan happening on temp table. avoid table scans best can 128 rows (in case) quicker whatever doing before.