title: TSQL Faster In Cursor Loop
body: <p>Building up a query.   </p>
  
  <p>The single query is shown below and runs in 7.0 seconds.   It does return the correct answer.  Need to count rows based on certain conditions and then get the maximum count.  My problem is performance of this stand along query.  That same query wrapped in a cursor is 0.15 seconds.  In the cursor the query plan is much different.  How can I make the stand alone query run faster? </p>
  
  <p>Using hints was able to get the stand alone to plan to look like the cursor plan and that fixed the speed problem.</p>
  
  <p>Fixed query:  (not all the way fixed as OPTION fails)</p>
  
  <pre><code>select max(list.match) as 'max'
    from
    (
        SELECT 
         count(*) as 'match'  
        FROM [docSVenum1] with (nolock)   
        INNER LOOP JOIN  [FTSindexWordOnce] as w1 with (NOLOCK, FORCESEEK) 
          ON [docSVenum1].sID = w1.[sID] and [docSVenum1].[enumID] = '142'
        INNER HASH JOIN [FTSindexWordOnce] as w2 with (NOLOCK)
          ON  w1.wordID = w2.wordID and w2.[sID] = '2'      
        GROUP BY W1.[sID]
        -- OPTION (HASH GROUP)
    ) as list;
  </code></pre>
  
  <p>problem query:</p>
  
  <pre><code>select getdate();
  go
    select max(list.match) as 'max'
    from
    (
        SELECT 
         count(*) as 'match'
        FROM [FTSindexWordOnce] as w1 with (nolock)
        INNER JOIN [docSVenum1] with (nolock)
          ON [docSVenum1].sID = w1.[sID] and [docSVenum1].[enumID] = '142'
        INNER JOIN [FTSindexWordOnce] as w2 with (nolock)
          ON  w1.wordID = w2.wordID AND w2.[sID] = '2'
        GROUP BY W1.[sID]
    ) as list;
  go
  select getdate();   -- 7.0 seconds
  </code></pre>
  
  <p>I also need to run that single query against multiple values and put it in a cursor with a loop. I know cursor is bad but I could not figure out how to do it without a cursor.  </p>
  
  <p>The query stand alone and inside the loop both return the same correct answer.</p>
  
  <p>My surprise is the exact same query inside a cursor loop is 40 times faster. </p>
  
  <pre><code>DECLARE @sid int
  
  DECLARE sID_cursor CURSOR FOR 
  SELECT top 80 sID
  FROM docSVsys
  WHERE sID = '2'  -- actually I want to not have this and let it loop through all
                   -- when i built the loop i saw performance improvement
  ORDER BY sID
  
  OPEN sID_cursor
  
  FETCH NEXT FROM sID_cursor
  INTO @sID
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT @sID
  
    select max(list.match) as 'max'
    from
    (
        SELECT 
         count(*) as 'match'
        FROM [FTSindexWordOnce] as w1 with (nolock)
        INNER JOIN [docSVenum1] with (nolock)
          ON [docSVenum1].sID = w1.[sID] and [docSVenum1].[enumID] = '142'
        INNER JOIN [FTSindexWordOnce] as w2 with (nolock)
          ON  w1.wordID = w2.wordID AND w2.[sID] = @sID
        GROUP BY W1.[sID]
    ) as list
  
      FETCH NEXT FROM sID_cursor
      INTO @sID
  
  END 
  CLOSE sID_cursor;
  DEALLOCATE sID_cursor;
  go
  select getdate();  -- 0.15 seconds
  </code></pre>