optimization - SQL Server 2008 Optimize FULL JOIN with ISNULL statements -

hi all

i hoping me improve query have run periodically. @ moment takes more 40 minutes execute. uses full allocated memory during time, cpu usage meanders @ 2% - 5%, every , jumping 40% few seconds.

i have table (simplified example):

    create table [dbo].[datatable]     (     [id] [int] identity(1,1) not null,     [dteeffectivedate] [date] null,     [dteprevious] [date] null,     [dtenext] [date] null,     [age] [int] null,     [count] [int] null     ) on [primary]      go 

here input values:

insert [yourdb].[dbo].[datatable]            ([dteeffectivedate]            ,[dteprevious]            ,[dtenext]            ,[age]            ,[count])      values ('2009-01-01',null,'2010-01-01',40,300), ('2010-01-01','2009-01-01', null,40,200), ('2009-01-01',null, '2010-01-01',20,100), ('2010-01-01','2009-01-01', null,20,50), ('2009-01-01',null,'2010-01-01',30,10) go 

each entry has dteeffectivedate field. in addition, each has dteprevious , dtenext, reflects dates of nearest previous/next effective date. want query calculate mid-value on count fields between successive periods, within specific age.

so example, in data above, age 40 have 300 @ 2009/01/01 , 200 @ 2010/01/01 query should produce 250.

note age 30 has 1 entry, 10. @ 2009/01/01. there no entry @ 2010/01/01, know data captured @ point, fact there nothing means 30 0 @ date. hence query should produce 5.

in order achieve use full join of table on itself, , use isnull select values. here code:

select      isnull(t1.dteeffectivedate,t2.dteprevious) [start date]     ,isnull(t1.dtenext,t2.dteeffectivedate)  [end date]     ,isnull(t1.age,t2.age) age      ,isnull(t1.[count],0) [count start]     ,isnull(t2.[count],0)   [count end]     ,(isnull(t1.[count],0)+isnull(t2.[count],0))/2 [mid count]          [expdbclient].[dbo].[datatable] t1     full join [expdbclient].[dbo].[datatable] t2      on      t2.dteeffectivedate = t1.dtenext     , t2.age = t1.age      isnull(t1.dteeffectivedate,t2.dteprevious) not null     , isnull(t1.dtenext,t2.dteeffectivedate) not null  go 

which outputs:

start date  end date    age count start count end   mid lives 2009-01-01  2010-01-01  40  300         200         250 2009-01-01  2010-01-01  20  100         50          75 2009-01-01  2010-01-01  30  10          0           5 

it works perfectly, when run on actual data, 7m records, takes painfully long execute.

does have suggestions?

thanks
karl

it's hard make lot of recommendations.

one thing i'd recommend indices on columns use foreign keys in join conditions, e.g.

• age
• dteeffectivedate
• dtenext

create nonclustered index on each of columns separately , measure again. few data rows, there's no improvement measurable - millions of rows, might make difference.