Performance issue in sorting of the result set in a query

• siva.matrim

  Newbie

  Points: 7

  More actions

  February 21, 2012 at 8:32 am

  #251932

  I have performance issue in a query. If any one could suggest any solution that would be helpful.

  here is my issue:

  Table tbl1 has columns Img (foreign key), A, B , p1 (foreign key of tbl0.p1)

  Table tbl2 has columns Img (primay key), Cam (foreign key),....

  Table tbl3 has columns Cam (primary key),...

  There is an index (clustered) on key columns on all the tables.

  I have a query like this:

  select

  t3.Cam, t2.Img,

  ROW_NUMBER() over (partition by t3.Cam order by SQRT(t1.a*t1.a+t1.b*t1.b)) as Rnk,

  COUNT(*) over (partition by t3.Cam) Cnt

  from

  tbl1 t1,

  tbl2 t2,

  tbl3 t3

  where t3.Cam = t2.Cam

  and t2.img = t1.img

  and the result set is around 10 million records. the query runs for more than 15 minutes.

  sort takes so much time and I have tried to reduce the sorting time using indexed view created on the above select columns. still it is so poor in performance.

  could anyone please suggest any idea..

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  February 21, 2012 at 8:41 am

  #1449753

  If you give the DDL for your tables and indexes, some readily consumable sample data and the actual execution plan of the query, you're likely to get some better advice.

  Just from looking at your query though, what's probably killing this is the scalar function you're applying as your sort (SQRT).

  You may be able to create a computed column as your square root function and add it to an appropriate covering index