title: C# + Sql Server - Execute a stored procedure large number of times. Best way?
body: <p>I have one stored procedure which inserts data into 3 tables, (does UPSERTS), and has some rudamentary logic. (IF-THEN-ELSE)</p>
  
  <p>I need to execute this Sproc millions of times (From a C# app) using different parameters and I need it to be FAST.</p>
  
  <p>What is the best way to do so?</p>
  
  <p><strong>Does anybody know an open-source (or not) off the shelf document indexer besides Lucene or Sql Server FTS??</strong></p>
  
  <p>*I am trying to build a document word-index. For each word in the document I insert into the DB the word, docID, and word position.</p>
  
  <p>This happens 100000 times for 100 documents for example.</p>
  
  <p><strong>The Sproc</strong> : there are 3 tables to insert into, for each one I do an UPSERT.</p>
  
  <p><strong>The C# app</strong> : </p>
  
  <pre><code>using (SqlConnection con = new SqlConnection(_connectionString))
              {
                  con.Open();
                  SqlTransaction trans = con.BeginTransaction();
                  SqlCommand command = new SqlCommand("add_word", con, trans);
                  command.CommandType = System.Data.CommandType.StoredProcedure;
                  string[] TextArray;
                  for (int i = 0; i &lt; Document.NumberOfFields; i++)
                  {
                    ...
                   Addword(..., command);  &lt;---- this updates parameters with new values and ExecuteNonQuery.
                  }
  
              }
  </code></pre>
  
  <p>I Forgot to mention , this code produces deadlocks in Sql Server . I have no idea why this happens.</p>