title: DataAdapter.Update() performance
body: <p>I have a relatively simply routine that looks at database entries for media files, calculates the width, height and filesize, and writes them back into the database.</p>
  
  <p>The database is SQLite, using the System.Data.SQLite library, processing ~4000 rows.  I load all rows into an ADO table, update the rows/columns with the new values, then run adapter.Update(table); on it.</p>
  
  <p>Loading the dataset from the db tables half a second or so, updating all the rows with image width/height and getting the file length from FileInfo took maybe 30 seconds.  Fine.</p>
  
  <p>The adapter.Update(table); command took somewhere in the vicinity of 5 to 7 minutes to run.</p>
  
  <p>That seems awfully excessive.  The ID is a PK INTEGER and thus - according to SQLite's docs, is inherently indexed, yet even so I can't help but think that if I were to run a separate update command for each individual update, this would have completed much faster.</p>
  
  <p>I had considered ADO/adapters to be relatively low level (as opposed to ORMs anyway), and this terrible performance surprised me.  Can anyone shed some light on why it would take 5-7 minutes to update a batch of ~4000 records against a locally placed SQLite database?  </p>
  
  <p>As a possible aside, is there some way to "peek into" how ADO is processing this?  Internal library stepthroughs or...??</p>
  
  <p>Thanks</p>
  
  <pre><code>public static int FillMediaSizes() {
          // returns the count of records updated
  
          int recordsAffected = 0;
  
          DataTable table = new DataTable();
          SQLiteDataAdapter adapter = new SQLiteDataAdapter();
  
          using (SQLiteConnection conn = new SQLiteConnection(Globals.Config.dbAppNameConnectionString))
          using (SQLiteCommand cmdSelect = new SQLiteCommand())
          using (SQLiteCommand cmdUpdate = new SQLiteCommand()) {
  
              cmdSelect.Connection = conn;
              cmdSelect.CommandText =
                  "SELECT ID, MediaPathCurrent, MediaWidth, MediaHeight, MediaFilesizeBytes " +
                  "FROM Media " +
                  "WHERE MediaType = 1 AND (MediaWidth IS NULL OR MediaHeight IS NULL OR MediaFilesizeBytes IS NULL);";
  
              cmdUpdate.Connection = conn;
              cmdUpdate.CommandText =
                  "UPDATE Media SET MediaWidth = @w, MediaHeight = @h, MediaFilesizeBytes = @b WHERE ID = @id;";
  
              cmdUpdate.Parameters.Add("@w", DbType.Int32, 4, "MediaWidth");
              cmdUpdate.Parameters.Add("@h", DbType.Int32, 4, "MediaHeight");
              cmdUpdate.Parameters.Add("@b", DbType.Int32, 4, "MediaFilesizeBytes");
              SQLiteParameter param = cmdUpdate.Parameters.Add("@id", DbType.Int32);
              param.SourceColumn = "ID";
              param.SourceVersion = DataRowVersion.Original;
  
              adapter.SelectCommand = cmdSelect;
              adapter.UpdateCommand = cmdUpdate;
  
              try {
                  conn.Open();
                  adapter.Fill(table);
                  conn.Close();
              }
              catch (Exception e) {
                  Core.ExceptionHandler.HandleException(e, true);
                  throw new DatabaseOperationException("", e);
              }
  
              foreach (DataRow row in table.Rows) {
  
                  try {
  
                      using (System.Drawing.Image img = System.Drawing.Image.FromFile(row["MediaPathCurrent"].ToString())) {
  
                          System.IO.FileInfo fi;
  
                          fi = new System.IO.FileInfo(row["MediaPathCurrent"].ToString());
  
                          if (img != null) {
  
                              int width = img.Width;
                              int height = img.Height;
                              long length = fi.Length;
  
                              row["MediaWidth"] = width;
                              row["MediaHeight"] = height;
                              row["MediaFilesizeBytes"] = (int)length;
                          }
                      }
                  }
                  catch (Exception e) {
                      Core.ExceptionHandler.HandleException(e);
                      DevUtil.Print(e);
                      continue;
                  }
              }                
  
  
              try {
                  recordsAffected = adapter.Update(table);
              }
              catch (Exception e) {
                  Core.ExceptionHandler.HandleException(e);
                  throw new DatabaseOperationException("", e);
              }
  
  
          }
  
          return recordsAffected;
      }
  </code></pre>