Optimize Range Query

• techstuff

  SSC Journeyman

  Points: 80

  More actions

  January 12, 2005 at 2:56 am

  #163628

  Problem:For a given sales in #temp1 table a matching group has to be found in the range table.

  Below is the sample DML, the actual table contains more then 800K records in the #temp1 table+ other fields.

  All the required indexes exists on the actual table.

  create table #temp1(TransNo int, Sales numeric(9,2))

  insert into #temp1

  select 1,150

  union all

  select 2,400

  union all

  select 3,550

  union all

  select 4,1000

  union all

  select 5,50

  create table #tmpRange(id1 int,fromrange numeric(9,2),

        torange numeric(9,2),grouptype char(1))

  insert into #tmpRange

  select 1,0,200,'A'

  union all

  select 2,201,400,'A'

  union all

  select 3,401,600,'A'

  union all

  select 4,601,1000,'A'

  union all

  select 5,1001,5000,'A'

  union all

  select 5,10000,20000,'B'

  union all

  select 5,20001,30000,'B'

  select TransNo,GroupType

  from #temp1

  inner join #tmpRange on sales between fromrange and torange

  where grouptype='A'

  drop table #temp1,#tmpRange

  one issue with the query below is  the logical reads for the #temp1 table will be twice the value in the range table is this normal or is there a better way to write the same query.

  Table '#temp1________00000003DFBD'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0.

  ------------------ set statistics io on results -------------------------------

  Table '#temp1________00000003DFBD'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0.

  Table '#tmpRange_____00000003DFBD'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 12, 2005 at 6:13 am

  #537008

  Are you absolutely sure about this?

  I tried your example - just copy & paste into QA and got:

  Table '#temp1________ ...... Scan count 5, logical reads 5, physical reads 0, read-ahead reads 0.

  (On SS 7.0 I might add.)

  /Kenneth

• techstuff

  SSC Journeyman

  Points: 80

  More actions

  January 12, 2005 at 9:08 pm

  #537185

  yes i did and got logical reads 10, now its back to 5!!!

  is there any other way to write the same query?

   

   

• Goce Smilevski

  SSCommitted

  Points: 1704

  More actions

  January 13, 2005 at 1:56 am

  #537195

  Hi,

  if you have index on temp1.Sales (as you said), I think that further optimizations will not give some major execution time savings. I tested some modified versions of the SQL on a table with 800K records and the index on Sales is the most usefull thing for the query. A minor difference occured when I put index on tmpRange (grouptype, fromrange, torange), but that is insignificant, when you have 800K records in temp1.

  By the way, why do you select the grouptype column, if you fix it to 'A' ?

  Regards,

  Goce.

• techstuff

  SSC Journeyman

  Points: 80

  More actions

  January 13, 2005 at 9:06 pm

  #537408

  Thanks,

  The script i posted here was a sample, the range table will be used by multiple SP's each querying for a diffrent groups.