c# - Parallel.Foreach is to0 slow. How can I optimize my code -

i have flollowing code takes more 7 minutes complete, when use parallel.foreach. list "final_products" iterate on contains 7000 products.

 public void generatetreefromallfinalproducts()     {         xmlserializer serializer = new xmlserializer(typeof(imagefeature<float>[]));         dstableadapters.products_uniquetableadapter pft = new dstableadapters.products_uniquetableadapter();         dstableadapters.products_unique_surftableadapter pus = new dstableadapters.products_unique_surftableadapter();         ds.products_uniquedatatable final_products = pft.getdata();          stopwatch stopwatch = new stopwatch();         stopwatch.start();          parallel.foreach(final_products.asparallel(), row =>         {                              //get surf data images found similar image             types.products_unique_surfrow surfdata = getdatabyuniqueproductid(row.id);              imagefeature<float>[] row_features = (imagefeature<float>[])serializer.deserialize(new stringreader(decompress(surfdata.surf)));             if (row_features != null)                 flann.addsurfdescriptors(row_features, row.id);                                });          stopwatch.stop();         console.writeline("time elapsed: {0}", stopwatch.elapsed);     } 

is normalt takes long , if not how can optimize code?

getdatabyuniqueproductid(row.id) call database returns 1 single row.

private static types.products_unique_surfrow getdatabyuniqueproductid(int rowid)     {         types.products_unique_surfrow ret = new types.products_unique_surfrow();          string sqltext = "set rowcount 1 select surf products_unique_surf unique_product_id =" + rowid;          using (sqlconnection myconn = new sqlconnection(global::scbot.properties.settings.default.dataconnectionstring))         {             myconn.open();              sqlcommand cmd = new sqlcommand(sqltext, myconn);             try             {                 cmd.commandtype = commandtype.text;                  sqldatareader reader = cmd.executereader();                 while (reader.read())                 {                     types.products_unique_surfrow row = new types.products_unique_surfrow();                     row.surf = convert.tostring(reader["surf"]);                      ret = row;                 }             }             catch (exception e)             {                 messagebox.show(e.tostring());             }         }         return ret;     } 

my initial code following

 public void generatetreefromallfinalproducts()     {         xmlserializer serializer = new xmlserializer(typeof(imagefeature<float>[]));         dstableadapters.products_uniquetableadapter pft = new dstableadapters.products_uniquetableadapter();         dstableadapters.products_unique_surftableadapter pus = new dstableadapters.products_unique_surftableadapter();         ds.products_uniquedatatable final_products = pft.getdata();          foreach (ds.products_uniquerow row in final_products)         {             //get surf data images found similar image             list<ds.products_unique_surfrow> surfdata = pus.getdatabyuniqueproductid(row.id).tolist();              foreach (ds.products_unique_surfrow data in surfdata)             {                 imagefeature<float>[] row_features = (imagefeature<float>[])serializer.deserialize(new stringreader(decompress(data.surf)));                 flann.addsurfdescriptors(row_features, row.id);             }         }     } 

but slow, why tried parallel.foreach

you should measure time inner code takes. parallel won't speed-up inner code. instead of nesting methods decompress/deserialize, measure them separately.

after edit:

each thread create new connection. think great example how not use parallel speeding up. changing algoritme more. because querying single rows on different connections/threads wll cost more time query them (or selection) , single threaded foreach loop them.

my view:

i collect id's of final_products enemration , buildup string using stringbuilder.

stringbuilder sb = new stringbuiler(); bool isfirst = true;  sb.append("("); foreach(var prod in final_products) {     if(isfirst)         isfirst = false;     else         sb.append(", ");      sb.append(prod.id); } sb.append(")");  string query = "select surf products_unique_surf id in "+sb.tostring();  // execute query  // foreach row, decompress, deserialize etc...