c# - sql server query performance issues -

i have table has columns of

• non-unique id
• n-columns of various data

a date of when data updated

i have query that, given date range, gets list of entries updated during date range , entry of last update prior 1 found. date range within day 07-10-2013 00:00:000 - 7-11-2013 00:00:000.

eg. given 07-10-2013 00:00:000 - 7-11-2013 00:00:000, query found 2 entries

id   new data      updatedate              old data     last updatedate 3    randomdata    7-10-2013 03:30:343     randomdata   7-05-2013 06:34:764 4    randomdata    7-10-2013 13:30:343     randomdata   6-09-2013 04:37:376 

this result get. have query query slow because there 3 inner joins on table has lot of entries , wondering if think of way make query faster. using sql server 2000.

table info

• non-unique id
• unique id (this auto increment id)
• various data collected
• updated date

edit:

indexes on id , updatedate

query i'm using (generalizing it) ::warning::it's not pretty::warning::

select *       (select distinct          s1.id id,          s1.randdata1, s2.randdata1, s1.randdata2, s2.randdata2,...,          s1.updatedate newupdatedate,         s2.updatedate prevupdatedate,          datediff(second, s1.updatedate ,s2.updatedate) maxdate                (select *           updates           updatedate >= '{0}' , updatedate < '{1}'             , id in ('{3}')) s1       inner join          updates s2 on s1.id = s2.id , s1.updateid != s2.updateid) t1   inner join       (select            s1.id, max(datediff(second, s1.updatedate, s2.updatedate)) maxdate2        updates s1        inner join updates s2 on s1.id in ('{3}') , s1.id = s2.id , s1.updateid != s2.updateid        datediff(second, s1.updatedate, s2.updatedate) < 0         , s1.updatedate < '{1}' , s2.updatedate < '{1}'        group           s1.id) t2 on t1.id = t2.id , t1.maxdate = t2.maxdate2 

the {0} {1} , {2} params passed in.

edit: query being executed in c# if makes difference. if necessary, 2 queries too. i'm looking changed in data on selected date.

i have had pretty luck getting nested select statements #temp tables. depending on how big talking possibly use table variables.

could double check query step step see if need carry data long in each nested query. in otherwords nested queries consumed outer query or data coming over.

i think complexity seen here using temp tables best bet. way nested queries can build temp table instead of having hold them in memory.

your final query more compact , quicker.