c# - Efficient way to load thousands of data from database and process it -

i have table in sql server 2008 named photostorages following columns. table contains 5000 data.

photostorageid              bigint photo                       image 

currently loading data using c# below

string sql = "select * photostorages"; using (sqlcommand sqlcommand = new sqlcommand(sql)) {     using (sqldataadapter dataadapter = new sqldataadapter(sqlcommand))     {         using (datatable datatable = new datatable())         {             dataadapter.fill(datatable);              if (datatable.rows.count > 0)             {                 (int = 0; < datatable.rows.count; i++)                 {                     /*resize image , again update resized image same database table*/                 }             }         }     } } 

now execution of process slow. want know there alternative way achieve this. in advance!

you can break query smaller chunks using row_number function in sql server paginate results.

i've based answer in question: paginate rows sql

first have convert query 1 performs pagination:

string sql = @"select                 *                               (                     select                         row_number() over(order id desc) rownumber,                         *                                             photostorages                 ) t                               rownumber between @start , @start + @pagesize"; 

you should change order id match actual id column of query. in specific case, can use column in table since using data, don't need worry order being returned. 5000 rows not big in grand scheme of things if large table 100,000+ rows, want use column has index on it. , not use binary data column.

then can run query inside loop:

var pagesize = 10; var startat = 0;  while(true)  {     using (sqlcommand sqlcommand = new sqlcommand(sql))     {         sqlcommand.parameters.add("@start", sqldbtype.int).value = startat;         sqlcommand.parameters.add("@pagesize", sqldbtype.int).value = pagesize;          using (sqldataadapter dataadapter = new sqldataadapter(sqlcommand))         {             using (datatable datatable = new datatable())             {                 dataadapter.fill(datatable);                  var rowcount = datatable.rows.count;                 var startat = startat + rowcount;                  if (datatable.rows.count > 0)                 {                     (int = 0; < datatable.rows.count; i++)                     {                         /*resize image , again update resized image same database table*/                     }                 }                 else                  {                     break;                 }             }         }     } } 

the idea select number of rows (which have provided via pagesize variable. take rows resize, , continue loop. loop continue until query returns no rows. value use pagesize you, example if have 2gb of memory free, , each photo 2mb, using memory if pagesize 1000 rows. want use substantially less available memory not slow down process, other processes happening on machine require free memory.

my c# skills rusty, should work you.