public void StartLoad(BackgroundWorker worker) { var useParams = false; var badParams = new List <string>(); foreach (var theKey in _paramMappings.Keys) { if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0)) { badParams.Add(theKey); } } foreach (var theKey in badParams) { _paramMappings.Remove(theKey); } //Need some parameters? if (_paramMappings.Count > 0) { ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings); useParams = true; } //Initialize the connection pool var conn = new SqlConnection(_connectionString); //TODO: use this or not?? SqlConnection.ClearPool(conn); conn.Open(); conn.Dispose(); //make sure the run cancelled flag is not set QueryInput.RunCancelled = false; //Spin up the load threads for (var i = 0; i < _threads; i++) { conn = new SqlConnection(_connectionString); //TODO: Figure out how to make this option work (maybe) //conn.FireInfoMessageEventOnUserErrors = true; SqlCommand statsComm = null; var queryComm = new SqlCommand { CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query }; if (useParams) { queryComm.Parameters.AddRange(ParamServer.GetParams()); } var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : "") + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : ""); if (setStatistics.Length > 0) { statsComm = new SqlCommand { CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics }; } //Queue<queryOutput> queryOutInfo = new Queue<queryOutput>(); var input = new QueryInput(statsComm, queryComm, // this.queryOutInfo, _iterations, _forceDataRetrieval); var theThread = new Thread(input.StartLoadThread) { Priority = ThreadPriority.BelowNormal }; _threadPool.Add(theThread); _commandPool.Add(queryComm); //queryOutInfoPool.Add(queryOutInfo); } //Start the load threads for (var i = 0; i < _threads; i++) { _threadPool[i].Start(); } //Start reading the queue... var finishedThreads = 0; var cancelled = false; while (finishedThreads < _threads) { // for (int i = 0; i < threads; i++) // { // try // { QueryOutput theOut = null; //lock (queryOutInfoPool[i]) lock (QueryOutInfo) { //if (queryOutInfoPool[i].Count > 0) //theOut = (queryOutput)queryOutInfoPool[i].Dequeue(); if (QueryOutInfo.Count > 0) { theOut = QueryOutInfo.Dequeue(); } else { Monitor.Wait(QueryOutInfo); } } if (theOut != null) { //Report output to the UI worker.ReportProgress((int)(finishedThreads / (decimal)_threads * 100), theOut); //TODO: Make this actually remove the queue from the pool so that it's not checked again -- maintain this with a bitmap, perhaps? if (theOut.Finished) { finishedThreads++; } } /* } * catch (InvalidOperationException e) * { * } */ /* * if (theOut != null) * Thread.Sleep(200); * else * Thread.Sleep(10); */ // } //TODO: Remove this ? GC.Collect(); if (worker.CancellationPending && !cancelled) { QueryInput.RunCancelled = true; //First, kill connections as fast as possible SqlConnection.ClearAllPools(); //for each 20 threads, create a new thread dedicated //to killing them var threadNum = _threadPool.Count; var killerThreads = new List <Thread>(); while (threadNum > 0) { var i = threadNum <= 20 ? 0 : threadNum - 20; var killThreads = new Thread[threadNum - i < 1 ? threadNum : threadNum - i]; var killCommands = new SqlCommand[threadNum - i < 1 ? threadNum : threadNum - i]; _threadPool.CopyTo(i, killThreads, 0, killThreads.Length); _commandPool.CopyTo(i, killCommands, 0, killCommands.Length); for (var j = threadNum - 1; j >= i; j--) { _threadPool.RemoveAt(j); _commandPool.RemoveAt(j); } var kill = new ThreadKiller(killThreads, killCommands); var killer = new Thread(kill.KillEm); killer.Start(); Thread.Sleep(0); killerThreads.Add(killer); threadNum = i; } //wait for the kill threads to return //before exiting... foreach (var theThread in killerThreads) { theThread.Join(); } cancelled = true; } } //clear any remaining messages -- these are almost certainly //execeptions due to thread cancellation //queryOutInfo.Clear(); }
public void StartLoadThread() { try { //do the work using (var conn = _queryComm.Connection) { SqlInfoMessageEventHandler handler = GetInfoMessages; for (var i = 0; i < _iterations; i++) { if (_runCancelled) { throw new Exception(); } Exception outException = null; try { //initialize the outInfo structure _outInfo = new QueryOutput(); conn.Open(); //set up the statistics gathering if (_statsComm != null) { _statsComm.ExecuteNonQuery(); Thread.Sleep(0); conn.InfoMessage += handler; } //Params are assigned only once -- after that, their values are dynamically retrieved if (_queryComm.Parameters.Count > 0) { ParamServer.GetNextRow_Values(_queryComm.Parameters); } _sw.Start(); //TODO: This could be made better if (_forceDataRetrieval) { var reader = _queryComm.ExecuteReader(); Thread.Sleep(0); do { Thread.Sleep(0); while (reader.Read()) { //grab the first column to force the row down the pipe var x = reader[0]; Thread.Sleep(0); } } while (reader.NextResult()); } else { _queryComm.ExecuteNonQuery(); Thread.Sleep(0); } _sw.Stop(); } catch (Exception e) { if (_runCancelled) { throw; } else { outException = e; } if (_sw.IsRunning) { _sw.Stop(); } } finally { //Clean up the connection if (_statsComm != null) { conn.InfoMessage -= handler; } conn.Close(); } var finished = i == _iterations - 1; //List<string> infoMessages = null; //infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null; /* * queryOutput theout = new queryOutput( * outException, * sw.Elapsed, * finished, * (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray()); */ _outInfo.E = outException; _outInfo.Time = _sw.Elapsed; _outInfo.Finished = finished; lock (QueryOutInfo) { QueryOutInfo.Enqueue(_outInfo); Monitor.Pulse(QueryOutInfo); } //Prep the collection for the next round //if (infoMessages != null && infoMessages.Count > 0) // infoMessages.Clear(); _sw.Reset(); } } } catch { if (_runCancelled) { //queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null); _outInfo.Time = new TimeSpan(0); _outInfo.Finished = true; lock (QueryOutInfo) { QueryOutInfo.Enqueue(_outInfo); } } else { throw; } } }
public void StartLoad(BackgroundWorker worker) { bool useParams = false; List <string> badParams = new List <string>(); foreach (string theKey in paramMappings.Keys) { if ((paramMappings[theKey] == null) || (paramMappings[theKey].Length == 0)) { badParams.Add(theKey); } } foreach (string theKey in badParams) { paramMappings.Remove(theKey); } //Need some parameters? if (paramMappings.Count > 0) { ParamServer.Initialize(paramQuery, paramConnectionString, paramMappings); useParams = true; } //Initialize the connection pool SqlConnection conn = new SqlConnection(this.connectionString); //TODO: use this or not?? SqlConnection.ClearPool(conn); conn.Open(); conn.Dispose(); //make sure the run cancelled flag is not set queryInput.RunCancelled = false; //Spin up the load threads for (int i = 0; i < threads; i++) { conn = new SqlConnection(this.connectionString); //TODO: Figure out how to make this option work (maybe) //conn.FireInfoMessageEventOnUserErrors = true; SqlCommand stats_comm = null; SqlCommand query_comm = new SqlCommand(); query_comm.CommandTimeout = this.commandTimeout; query_comm.Connection = conn; query_comm.CommandText = this.query; if (useParams) { query_comm.Parameters.AddRange(ParamServer.GetParams()); } string setStatistics = ((collectIOStats) ? (@"SET STATISTICS IO ON;") : ("")) + ((collectTimeStats) ? (@"SET STATISTICS TIME ON;") : ("")); if (setStatistics.Length > 0) { stats_comm = new SqlCommand(); stats_comm.CommandTimeout = this.commandTimeout; stats_comm.Connection = conn; stats_comm.CommandText = setStatistics; } //Queue<queryOutput> queryOutInfo = new Queue<queryOutput>(); queryInput input = new queryInput( stats_comm, query_comm, // this.queryOutInfo, this.iterations, this.forceDataRetrieval); Thread theThread = new Thread(new ThreadStart(input.startLoadThread)); theThread.Priority = ThreadPriority.BelowNormal; threadPool.Add(theThread); commandPool.Add(query_comm); //queryOutInfoPool.Add(queryOutInfo); } //Start the load threads for (int i = 0; i < threads; i++) { threadPool[i].Start(); } //Start reading the queue... int finishedThreads = 0; bool cancelled = false; while (finishedThreads < threads) { // for (int i = 0; i < threads; i++) // { // try // { queryOutput theOut = null; //lock (queryOutInfoPool[i]) lock (queryOutInfo) { //if (queryOutInfoPool[i].Count > 0) //theOut = (queryOutput)queryOutInfoPool[i].Dequeue(); if (queryOutInfo.Count > 0) { theOut = queryOutInfo.Dequeue(); } else { Monitor.Wait(queryOutInfo); } } if (theOut != null) { //Report output to the UI worker.ReportProgress((int)(((decimal)finishedThreads / (decimal)threads) * 100), theOut); //TODO: Make this actually remove the queue from the pool so that it's not checked again -- maintain this with a bitmap, perhaps? if (theOut.finished) { finishedThreads++; } } /* } * catch (InvalidOperationException e) * { * } */ /* * if (theOut != null) * Thread.Sleep(200); * else * Thread.Sleep(10); */ // } //TODO: Remove this ? GC.Collect(); if (worker.CancellationPending && (!cancelled)) { queryInput.RunCancelled = true; //First, kill connections as fast as possible SqlConnection.ClearAllPools(); //for each 20 threads, create a new thread dedicated //to killing them int threadNum = threadPool.Count; List <Thread> killerThreads = new List <Thread>(); while (threadNum > 0) { int i = (threadNum <= 20) ? 0 : (threadNum - 20); Thread[] killThreads = new Thread[((threadNum - i) < 1) ? threadNum : (threadNum - i)]; SqlCommand[] killCommands = new SqlCommand[((threadNum - i) < 1) ? threadNum : (threadNum - i)]; threadPool.CopyTo( i, killThreads, 0, killThreads.Length); commandPool.CopyTo( i, killCommands, 0, killCommands.Length); for (int j = (threadNum - 1); j >= i; j--) { threadPool.RemoveAt(j); commandPool.RemoveAt(j); } ThreadKiller kill = new ThreadKiller( killThreads, killCommands); Thread killer = new Thread(new ThreadStart(kill.KillEm)); killer.Start(); Thread.Sleep(0); killerThreads.Add(killer); threadNum = i; } //wait for the kill threads to return //before exiting... foreach (Thread theThread in killerThreads) { theThread.Join(); } cancelled = true; } } //clear any remaining messages -- these are almost certainly //execeptions due to thread cancellation //queryOutInfo.Clear(); }
public void startLoadThread() { try { //do the work using (SqlConnection conn = query_comm.Connection) { int connectionHashCode = conn.GetHashCode(); SqlInfoMessageEventHandler handler = new SqlInfoMessageEventHandler(queryInput.GetInfoMessages); for (int i = 0; i < iterations; i++) { if (runCancelled) { throw new Exception(); } Exception outException = null; try { //initialize the outInfo structure queryInput.outInfo = new queryOutput(); conn.Open(); //set up the statistics gathering if (stats_comm != null) { stats_comm.ExecuteNonQuery(); Thread.Sleep(0); conn.InfoMessage += handler; } //Params are assigned only once -- after that, their values are dynamically retrieved if (query_comm.Parameters.Count > 0) { ParamServer.GetNextRow_Values(query_comm.Parameters); } sw.Start(); if (forceDataRetrieval) { SqlDataReader reader = query_comm.ExecuteReader(); Thread.Sleep(0); do { Thread.Sleep(0); while (reader.Read()) { //grab the first column to force the row down the pipe object x = reader[0]; Thread.Sleep(0); } } while (reader.NextResult()); } else { query_comm.ExecuteNonQuery(); Thread.Sleep(0); } sw.Stop(); } catch (Exception e) { if (runCancelled) { throw; } else { outException = e; } if (sw.IsRunning) { sw.Stop(); } } finally { //Clean up the connection if (stats_comm != null) { conn.InfoMessage -= handler; } conn.Close(); } bool finished = (i == (iterations - 1)) ? true : false; //List<string> infoMessages = null; //infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null; /* * queryOutput theout = new queryOutput( * outException, * sw.Elapsed, * finished, * (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray()); */ outInfo.e = outException; outInfo.time = sw.Elapsed; outInfo.finished = finished; lock (LoadEngine.queryOutInfo) { LoadEngine.queryOutInfo.Enqueue(outInfo); Monitor.Pulse(LoadEngine.queryOutInfo); } //Prep the collection for the next round //if (infoMessages != null && infoMessages.Count > 0) // infoMessages.Clear(); sw.Reset(); } } } catch { if (runCancelled) { //queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null); outInfo.time = new TimeSpan(0); outInfo.finished = true; lock (LoadEngine.queryOutInfo) { LoadEngine.queryOutInfo.Enqueue(outInfo); } } else { throw; } } }
private void StartLoad(BackgroundWorker worker) { var useParams = false; var badParams = new List <string>(); foreach (var theKey in _paramMappings.Keys) { if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0)) { badParams.Add(theKey); } } foreach (var theKey in badParams) { _paramMappings.Remove(theKey); } //Need some parameters? if (_paramMappings.Count > 0) { ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings); useParams = true; } //Initialize the connection pool var conn = new SqlConnection(_connectionString); //TODO: use this or not?? SqlConnection.ClearPool(conn); conn.Open(); conn.Dispose(); //Spin up the load threads for (var i = 0; i < _threads; i++) { conn = new SqlConnection(_connectionString); //TODO: Figure out how to make this option work (maybe) //conn.FireInfoMessageEventOnUserErrors = true; SqlCommand statsComm = null; var queryComm = new SqlCommand { CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query }; if (useParams) { queryComm.Parameters.AddRange(ParamServer.GetParams()); } var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : string.Empty) + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : string.Empty); if (setStatistics.Length > 0) { statsComm = new SqlCommand { CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics }; } //Queue<queryOutput> queryOutInfo = new Queue<queryOutput>(); using var input = new QueryInput(statsComm, queryComm, // this.queryOutInfo, _iterations, _forceDataRetrieval, _queryDelay, worker, _killQueriesOnCancel, _threads); var theThread = new Thread(input.StartLoadThread) { Priority = ThreadPriority.BelowNormal, IsBackground = true }; theThread.Name = "thread: " + i; _threadPool.Add(theThread); _commandPool.Add(queryComm); //queryOutInfoPool.Add(queryOutInfo); } // create a token source for the workers to be able to listen to a cancel event using var workerCTS = new CancellationTokenSource(); _finishedThreads = 0; for (var i = 0; i < _threads; i++) { _threadPool[i].Start(workerCTS.Token); } //Start reading the queue... var cancelled = false; while (!cancelled) { QueryOutput theOut = null; try { // wait for OutInfo items in the queue or a user cancel event theOut = QueryOutInfo.Take(_backgroundWorkerCTS.Token); } catch (Exception ex) { // The exception is InvalidOperationException if the threads are done // and OperationCanceledException if the user clicked cancel. // If it's OperationCanceledException, we need to cancel // the worker threads and wait for them to exit if (ex is OperationCanceledException) { workerCTS.Cancel(); foreach (var theThread in _threadPool) { // give the thread max 5 seconds to cancel nicely if (!theThread.Join(5000)) { theThread.Interrupt(); } } } SqlConnection.ClearAllPools(); cancelled = true; } if (theOut != null) { //Report output to the UI int finishedThreads = Interlocked.CompareExchange(ref _finishedThreads, 0, 0); theOut.ActiveThreads = _threads - finishedThreads; worker.ReportProgress((int)(_finishedThreads / (decimal)_threads * 100), theOut); } GC.Collect(); } }
public void StartLoadThread(Object token) { bool runCancelled = false; CancellationToken ctsToken = (CancellationToken)token; try { ctsToken.Register(() => { // Cancellation on the token will interrupt and cancel the thread runCancelled = true; _statsComm.Cancel(); _queryComm.Cancel(); }); //do the work using (var conn = _queryComm.Connection) { SqlInfoMessageEventHandler handler = GetInfoMessages; for (var i = 0; i < _iterations && !runCancelled; i++) { Exception outException = null; try { //initialize the outInfo structure _outInfo = new QueryOutput(); if (conn != null) { conn.Open(); //set up the statistics gathering if (_statsComm != null) { _statsComm.ExecuteNonQuery(); Thread.Sleep(0); conn.InfoMessage += handler; } } //Params are assigned only once -- after that, their values are dynamically retrieved if (_queryComm.Parameters.Count > 0) { ParamServer.GetNextRow_Values(_queryComm.Parameters); } _sw.Start(); //TODO: This could be made better if (_forceDataRetrieval) { var reader = _queryComm.ExecuteReader(); Thread.Sleep(0); do { Thread.Sleep(0); while (!runCancelled && reader.Read()) { //grab the first column to force the row down the pipe // ReSharper disable once UnusedVariable var x = reader[0]; Thread.Sleep(0); } } while (!runCancelled && reader.NextResult()); } else { _queryComm.ExecuteNonQuery(); Thread.Sleep(0); } _sw.Stop(); } catch (Exception ex) { if (!runCancelled) { outException = ex; } if (_sw.IsRunning) { _sw.Stop(); } } finally { //Clean up the connection if (conn != null) { if (_statsComm != null) { conn.InfoMessage -= handler; } conn.Close(); } } var finished = i == _iterations - 1; //List<string> infoMessages = null; //infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null; /* * queryOutput theout = new queryOutput( * outException, * sw.Elapsed, * finished, * (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray()); */ _outInfo.E = outException; _outInfo.Time = _sw.Elapsed; _outInfo.Finished = finished; QueryOutInfo.Add(_outInfo); //Prep the collection for the next round //if (infoMessages != null && infoMessages.Count > 0) // infoMessages.Clear(); _sw.Reset(); if (!runCancelled) { try { if (_queryDelay > 0) { Task.Delay(_queryDelay, ctsToken).Wait(); } } catch (AggregateException ae) { ae.Handle((x) => { if (x is TaskCanceledException) { runCancelled = true; return(true); } // if we get here, the exception wasn't a cancel // so don't swallow it return(false); }); } } } } } catch (Exception) { if (runCancelled) { //queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null); _outInfo.Time = new TimeSpan(0); _outInfo.Finished = true; QueryOutInfo.Add(_outInfo); } } Interlocked.Increment(ref _finishedThreads); if (_finishedThreads == _numWorkerThreads) { // once all of the threads have exited, tell the other side that we're done adding items to the collection QueryOutInfo.CompleteAdding(); } }