exec sp_cursorfetch statement executes 41507 times

• manavmit

  SSC Rookie

  Points: 35

  More actions

  May 16, 2008 at 3:42 am

  #187754

  SELECT TOP 5000 AVG(ISNULL(DATALENGTH(Deleted),0)+ISNULL(DATALENGTH(GroupID),0)+ISNULL(DATALENGTH(Members),0)+ISNULL(DATALENGTH(MembershipModTag),0)) FROM Groups

  Ans: 860

  SELECT MAX( DATALENGTH(Members) ) FROM Groups

  Ans: 98295

  declare @P1 int

  set @P1=180150000

  declare @P2 int

  set @P2=4

  declare @P3 int

  set @P3=1

  declare @P4 int

  set @P4=-1

  exec sp_cursoropen @P1 output, N'SELECT Deleted,GroupID,Members,MembershipModTag FROM Groups', @P2 output, @P3 output, @P4 output

  select @P1, @P2, @P3, @P4

  exec sp_cursorfetch 180150000, 2, 1, 3

  'exec sp_cursorfetch 180150000, 2, 1, 3' statement will executes 41507 time thus making the application very slow. Why this 'exec sp_cursorfetch 180150000, 2, 1, 3' statements run?

  If i run these queries directly on the Query analyser, 'exec sp_cursorfetch 180150000, 2, 1, 3' will not run at all. Is there any solution?

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  June 24, 2008 at 6:51 pm

  #833658

  The code you have posted is not meant to be executed by you... things like sp_cursoropen are SQL Server code that appears when SQL Server actually runs a stored proc.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)