Multi threading of process in C#

Question

I have the following code that runs to start my table calculations (the table calculations fire off a few queries returning thousands of rows). When my app just runs one instance, things are fine, but 2 or more then the server slows done and I start to get errors.

Should I turn this code into threads? How is that done?

private static object _lock = new object();

private void RunTable(string outputType, string _outputDataType) {

        Server.ScriptTimeout = 300;

        string returnCode = string.Empty;
        lock (_lock)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDll"].ToString()))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql.ToString(), connection))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 300;
                    returnCode = (string)command.ExecuteScalar();
                    Dispose();
                }
                Dispose();
            }
        }

First: if you're seeing errors, then tell us what error you're seeing. Second: how much data are you reading (i.e. can you load all the data in RAM)? — Kiril, May 23 '12 at 18:53
For quick, simple and efficient multithreading, you can make use of the BackgroundWorker Class http://msdn.microsoft.com/en-us/library/system.componentmodel.backgroundworker.aspx but my guess is that you may have some issues with the way you query the database, or perhaps you need some SQL tuning. — Mihai Todor, May 23 '12 at 18:54
when you say "one instance / two instances" you mean processes or threads? — eyossi, May 23 '12 at 18:54
the errors are here: http://stackoverflow.com/questions/10712860/deallocation-of-sqldatareader — cdub, May 23 '12 at 18:54
What is `Dispose()` doing there by itself? Surely that is not correct! The good thing with `using` is you don't even need to manually call the `command.Dispose`/`connection.Dispose` (see what I did there?) methods in this case. Omit it. — , May 23 '12 at 18:57
@chris The errors you showed in the other question don't seem to correspond to any of the code you have here. — Kiril, May 23 '12 at 19:02
@lirik yes but as you see in the other ticket, the error happens on this line" returnCode = (string)command.ExecuteScalar(); — cdub, May 23 '12 at 19:03
possible duplicate of [SQL server and .NET memory constraints, allocations, and garbage collection](http://stackoverflow.com/questions/10712055/sql-server-and-net-memory-constraints-allocations-and-garbage-collection) — Kiril, May 23 '12 at 19:14

score 1 · Answer 1 · answered May 23 '12 at 18:59

1

First: if you're seeing errors, then tell us what error you're seeing.

Second: how much data are you reading (i.e. can you load all the data in RAM)?

Third: if you can't load all the data at once, then try using the SqlDataReader to continuously read from the database.

Regarding the multithreading: it really depends on where is your bottleneck. If your bottleneck is in reading from the database, then you won't gain much by multithreading it (especially if your database does not allow concurrent access). You can use threads to process the data once it has been obtained from the database and that works particularly well when you have to use an SqlDataReader since you're reading record by record.

answered May 23 '12 at 18:59

Kiril

39,672
31
167
226

the errors are fully listed in another ticket as seen in the comments above – cdub May 23 '12 at 19:02
OK, so somebody has already answered your question here: http://stackoverflow.com/questions/10712055/sql-server-and-net-memory-constraints-allocations-and-garbage-collection How many times are you posting it? You have another method that gets the `SqlDataReader` and you're keeping it open... fix that problem and let us know if it gets better. – Kiril May 23 '12 at 19:10
i have SqlDataReader reader and it works but I do reader.Close() after ALL of them and still get that error. Its something with multi processes and not dealocating the memory – cdub May 23 '12 at 19:43
Try to reproduce the problem with a very simple example and eliminate all the "external" issues which are not relevant to the problem. Multithreading could be a problem if another thread tries to create an `SqlDataReader` before the currently open one is closed. Make sure that you synchronize properly when using multiple threads and multiple `SqlDataReaders` (i.e. only one should be open at a time). – Kiril May 23 '12 at 19:56

Multi threading of process in C#

1 Answers1