c# - DataAdapter.Update() performance -

i have relatively routine looks @ database entries media files, calculates width, height , filesize, , writes them database.

the database sqlite, using system.data.sqlite library, processing ~4000 rows. load rows ado table, update rows/columns new values, run adapter.update(table); on it.

loading dataset db tables half second or so, updating rows image width/height , getting file length fileinfo took maybe 30 seconds. fine.

the adapter.update(table); command took somewhere in vicinity of 5 7 minutes run.

that seems awfully excessive. id pk integer , - according sqlite's docs, inherently indexed, yet can't think if run separate update command each individual update, have completed faster.

i had considered ado/adapters relatively low level (as opposed orms anyway), , terrible performance surprised me. can shed light on why take 5-7 minutes update batch of ~4000 records against locally placed sqlite database?

as possible aside, there way "peek into" how ado processing this? internal library stepthroughs or...??

thanks

public static int fillmediasizes() {         // returns 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 , (mediawidth null or mediaheight null or mediafilesizebytes null);";              cmdupdate.connection = conn;             cmdupdate.commandtext =                 "update media set mediawidth = @w, mediaheight = @h, mediafilesizebytes = @b 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;     } 

loading dataset db tables half second or so

this single sql statement (so it's fast). excute sql select, populate dataset, done.

updating rows image width/height , getting file length fileinfo took maybe 30 seconds. fine.

this updating in memory data (so that's fast too), change x row in dataset, don't talk sql @ all.

the adapter.update(table); command took somewhere in vicinity of 5 7 minutes run.

this run sql update for every updated row. why it's slow.

yet can't think if run separate update command each individual update, have completed faster.

this it's doing anyway!

---

from msdn

the update performed on by-row basis. every inserted, modified, , deleted row, update method determines type of change has been performed on (insert, update or delete). depending on type of change, insert, update, or delete command template executes propagate modified row data source. when application calls update method, dataadapter examines rowstate property, , executes required insert, update, or delete statements iteratively each row, based on order of indexes configured in dataset.

---

is there way "peek into" how ado processing this?

yes: debug .net framework source code in visual studio 2012?