title: SQL Query with Cursor optimization
body: <p>I have a query where I iterate through a table -> for each entry I iterate through another table and then compute some results. I use a cursor for iterating through the table. This query takes ages to complete. Always more than 3 minutes. If I do something similar in C# where the tables are arrays or dictionaries it doesn't even take a second. What am I doing wrong and how can I improve the efficiency?</p>
  
  <pre><code>DELETE FROM [QueryScores]
  GO
  
  INSERT INTO [QueryScores] (Id)
  SELECT Id FROM [Documents]
  
  DECLARE @Id NVARCHAR(50)
  
  DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
  SELECT [Id] FROM [QueryScores]
  
  OPEN myCursor
  
  FETCH NEXT FROM myCursor INTO @Id
  
  WHILE @@FETCH_STATUS = 0
      BEGIN
          DECLARE @Score FLOAT = 0.0
  
          DECLARE @CounterMax INT = (SELECT COUNT(*) FROM [Query])
          DECLARE @Counter INT = 0
  
          PRINT 'Document: ' + CAST(@Id AS VARCHAR)
          PRINT 'Score: ' + CAST(@Score AS VARCHAR)
  
          WHILE @Counter &lt; @CounterMax
              BEGIN
  
              DECLARE @StemId INT = (SELECT [Query].[StemId] FROM [Query] WHERE [Query].[Id] = @Counter)
  
              DECLARE @Weight FLOAT = (SELECT [tfidf].[Weight] FROM [TfidfWeights] AS [tfidf] WHERE [tfidf].[StemId] = @StemId AND [tfidf].[DocumentId] = @Id)
  
              PRINT 'WEIGHT: ' + CAST(@Weight AS VARCHAR)
  
              IF(@Weight &gt; 0.0)
                  BEGIN
                  DECLARE @QWeight FLOAT = (SELECT [Query].[Weight] FROM [Query] WHERE [Query].[StemId] = @StemId)
                  SET @Score = @Score + (@QWeight * @Weight)
                  PRINT 'Score: ' + CAST(@Score AS VARCHAR)
                  END
  
              SET @Counter = @Counter + 1
              END 
  
          UPDATE [QueryScores] SET Score = @Score WHERE Id = @Id 
  
          FETCH NEXT FROM myCursor INTO @Id
      END
  
  CLOSE myCursor
  DEALLOCATE myCursor 
  </code></pre>
  
  <p>The logic is that i have a list of docs. And I have a question/query. I iterate through each and every doc and then have a nested iteration through the query terms/words to find if the doc contains these terms. If it does then I add/multiply pre-calculated scores.</p>