Dropping a cursor for set based logic

• Ian_McCann

  SSCertifiable

  Points: 5163

  More actions

  April 14, 2009 at 5:35 am

  #208245

  I am trying to replace a cursor with some set based logic, but I have got it wrong, can someone put me back on track please.

  The cursor is

  declare @date datetime

  declare datecursor cursor for

  select distinct date from Request order by 1

  open datecursor

  while (1=1) begin

  fetch datecursor into @date

  if @@fetch_status = -1

  break

  update Request

  set top10 = 10

  where requestid in (

  select top(3) requestid

  from Request

  where ParameterClass = 'PClass1'

  and date = @date

  order by duration asc)

  end

  close datecursor

  deallocate datecursor

  I have written

  update Request

  set top10 = 10

  where requestid in

  (

  SELECT

  RequestId

  FROM

  Request r

  WHERE

  r.RequestId IN

  (

  SELECT TOP 3

  requestid

  FROM

  Request r1

  WHERE

  ParameterClass = 'PClass1'

  AND r1.Date = r.Date

  ORDER BY r.duration asc

  )

  )

  However the cursor is outperforming my statement.

  What I am attempting is to update the top 3 requests grouped by date from a table that holds 22 million records and 512 distinct dates

  CREATE TABLE [dbo].[Request](

  [top10] [int] NULL,

  [RequestId] [varchar](50) NOT NULL,

  [duration] [bigint] NULL,

  [parameterclass] [varchar](255) NULL,

  [date] [datetime] NULL

  ) ON [PRIMARY]

  INSERT INTO request

  (top10, RequestId, duration, parameterclass, date)

  SELECT '0','20070601-053816-006-1bfcad71','3','PClass1','Jun 1 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061134-607-1bfcad71','2','PClass1','Jun 1 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061143-279-3b3eb5bd','3','PClass1','Jun 1 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061155-435-1bfcad71','1','PClass1','Jun 1 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061202-389-3b3eb5bd','2','PClass1','Jun 1 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061211-998-1bfcad71','3','PClass1','Jun 2 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061217-998-3b3eb5bd','1','PClass1','Jun 2 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061220-905-1bfcad71','6','PClass1','Jun 2 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061233-436-3b3eb5bd','2','PClass1','Jun 2 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061238-718-1bfcad71','2','PClass1','Jun 2 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061305-640-3b3eb5bd','3','PClass1','Jun 3 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061317-984-1bfcad71','1','PClass1','Jun 3 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061326-078-3b3eb5bd','1','PClass2','Jun 3 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061410-813-1bfcad71','22','PClass3','Jun 3 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061538-299-1bfcad71','0','PClass3','Jun 3 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061557-549-1bfcad71','0','PClass3','Jun 4 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061756-458-1bfcad71','0','PClass1','Jun 4 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061814-021-3b3eb5bd','0','PClass1','Jun 4 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061819-255-1bfcad71','1','PClass1','Jun 4 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061823-990-3b3eb5bd','12','PClass1','Jun 4 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061857-272-1bfcad71','0','PClass1','Jun 5 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061926-694-3b3eb5bd','0','PClass1','Jun 5 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061941-304-1bfcad71','0','PClass1','Jun 5 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061942-007-3b3eb5bd','0','PClass1','Jun 5 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061942-476-1bfcad71','0','PClass1','Jun 5 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061943-007-3b3eb5bd','0','PClass1','Jun 6 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061943-507-1bfcad71','0','PClass1','Jun 6 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061944-054-3b3eb5bd','0','PClass1','Jun 6 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061944-569-1bfcad71','1','PClass1','Jun 6 2007 12:00AM' UNION ALL

  SELECT '0','20070601-061945-069-3b3eb5bd','0','PClass1','Jun 6 2007 12:00AM'

  I hope I have provided enough information here

• Kishore.P

  SSCrazy Eights

  Points: 8147

  More actions

  April 14, 2009 at 5:46 am

  #977137

  see the below link, this may help to you:

  URL: http://www.sql-server-performance.com/articles/per/operations_no_cursors_p1.aspx

• Ian_McCann

  SSCertifiable

  Points: 5163

  More actions

  April 14, 2009 at 5:59 am

  #977143

  Thanks for the link and an interesting article, but I was looking to move away from a cursor to a more set based approach, not to replace the cursor with a loop.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 14, 2009 at 6:06 am

  #977146

  Have you really got no indexes?

  I can't imagine a set based approach working well without indexes when you have millions of records.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]