c# - Multithread SQL select statements -


i multithreading novice , sql novice, please excuse rookie mistakes.

i trying execute many sql queries asynchronously. queries select statements same table in same database. can run them synchronously , works fine, testing small subset leads me believe run queries synchronously take approximately 150 hours, far long. such, i'm trying figure out how run them in parallel.

i have tried model code after answer @ run method multiple times simultaneously in c#, code not executing correctly (it's erroring, though not know how. code says error occurs).

here have (a smaller , simpler version of doing):

class program {     static void main(string[] args)     {         list<string> employeeids = file.readalllines(/* filepath */);         list<tuple<string, string>> namesbyid = new list<tuple<string, string>>();          //what not want (because takes long) ...         using (sqlconnection conn = new sqlconnection(/* connection string */))         {             foreach (string id in employeeids)             {                 using (sqlcommand cmd = new sqlcommand("select firstname employees (nolock) employeeid = " + id, conn))                 {                     try                     {                         conn.open();                         namesbyid.add(new tuple<string, string> (id, cmd.executescalar().tostring()));                     }                                         {                         conn.close();                     }                 }             }         }           //what want (but errors) ...         var tasks = employeeids.select(id => task<tuple<string, string>>.factory.startnew(() => runquery(id))).toarray();         task.waitall(tasks);         namesbyid = tasks.select(task => task.result).tolist();     }      private static tuple<string, string> runquery(string id)     {         using (sqlconnection conn = new sqlconnection(/* connection string */))         {             using (sqlcommand cmd = new sqlcommand("select firstname employees (nolock) employeeid = " + id, conn))             {                 try                 {                     conn.open();                     return new tuple<string, string> (id, cmd.executescalar().tostring());                 }                                 {                     conn.close();                 }             }         }     } } 

note: not care how multithreaded (tasks, parallel.foreach, backgroundworker, etc). going used run ~30,000 select queries 1 time, need run fast (i'm hoping ~8 hrs = 1 work day, i'll take can get) 1 time. doesn't have pretty.

thank in advance!

this plain wrong. should build 1 query select firstnames need. if need pass bunch of ids server, no problem, use table valued parameter (aka tvp), coma separated list of values not scale well. if query correctly written , tables indexed, should quite fast. 100k rows table small table.

the query may this

select dollaramount, comp.companyid transactions  join (select min(transactionid) mintransactionid, companyid        companytransactions              group companyid      ) comp  on transactions.transactionid = comp.mintransactionid join @idlist on id = comp.companyid 

you may use in instead of join if ids in tvp not unique.

btw. know nolock means? if user of database , use single threaded or not modify data, safe. other means okay small chance of:

  • some records may missing in result
  • there duplicate records in result
  • there rows in result, have never been committed , never accepted valid data
  • if use varchar(max), may text has never been stored

Comments

Popular posts from this blog

ZeroMQ on Windows, with Qt Creator -

unity3d - Unity SceneManager.LoadScene quits application -

python - Error while using APScheduler: 'NoneType' object has no attribute 'now' -