c# - SQL Query pauses -

my query seems stalling every many passes through query.

        status_text.text = "check existing records...";         status_text.refresh();         using (streamreader reader = new streamreader(df_text_filename))         {             using (streamwriter writer = new streamwriter(df_text_filename + "_temp"))             {                 while ((product = reader.readline()) != null)                 {                     if (product != _aff_svc.dfheaderprod)                     {                         df_product = _product_factory.getproductdata(_vsi, product);                     }                     status_text.text = "checking existing record of vendor record id " + df_product.sku;                     status_text.refresh();                     if (_pctlr.getbysku(df_product.sku) != null)                     {                         continue;                     }                     writer.writeline(product);                     application.doevents();                 }                 writer.close();             }             reader.close();         }         system.io.file.delete(df_text_filename);         system.io.file.move(df_text_filename + "_temp", df_text_filename);   

the code runs through getbysku 10 times, pauses second or so, ten records. occurs throughout processes, not particular query.

it occurs whether or not have application.doevents() fire.

the other problem is not consistent. can working few hours, of sudden, zip through loop intended (expected).

my sql server running on same machine program.

i looked dedicating resources server mitigate behavior, have found nothing.

it looks if you're program parsing text file product info , parses, in while loop you're executing couple sql queries. it's bad idea make sql round trips inside loop.

instead, parsing file, gathering product ideas, closing file , make 1 call sql passing a/many tvps (table valued parameters) sproc , return data need sproc - possibly many tables.

edit: mentioned in comments file large lots of processing. consider batching sql work in lets 100?

also, if you're sql isn't tuned continually slow down more data written. there's not enough info in question analyze indexes, query plans etc... have @ data set grows.