Threading and SqlCommand.ExecuteNonQuery

[Steve B. [Labo.Net]]

Hi

I wrote a little software that call one stored procedure many times.

It works fine using one single thread, but since I use 10 threads to speed
up the process, I get lots of error, such "the connection is closed", "a
datareader is already open to the connection", etc...

Here is a simple view of my code :


private Thread[] _th = new Thread[10];

private void btnGo_Click(object sender, System.EventArgs e)

{



sqlConn.ConnectionString = ssd.ConnectionString;


if (sqlConn.State != ConnectionState.Open)

{

sqlConn.Open();

}


_insertedItems = 0;

_itemsLasting = (int)nudIterations.Value;

_totalItemToInsert = (int)nudIterations.Value;

for(int i=0; i<_th.Length; i++)

{

ThreadStart ts = new ThreadStart(StartCalling);

_th = new Thread(ts);

_th.Start();

}


ticker.Enabled = true;



}



private void StartCalling()

{

while (_insertedItems < _totalItemToInsert)

{

System.Threading.Interlocked.Increment(ref _insertedItems);

System.Threading.Interlocked.Decrement(ref _itemsLasting);

InsertCall();

//_insertedItems++;

//_itemsLasting--;

}

ticker.Enabled = false;

if (sqlConn.State != ConnectionState.Closed)

sqlConn.Close();

}

private Random rnd = new Random();

private void InsertCall()

{

try

{

// creating parameters

int fromNumber;

lock(cmdGetRandomCallNumber)

{

cmdGetRandomCallNumber.ExecuteNonQuery();

fromNumber = (int)cmdGetRandomCallNumber.Parameters["@CallNumber"].Value;


}

int indicatif = rnd.Next(10);

int ville = rnd.Next(1000);

int phone = rnd.Next(10000);

System.Text.StringBuilder sb =new System.Text.StringBuilder(20);

sb.AppendFormat("{0:00} - {1:000} {2:0000}", indicatif, ville, phone);

int duree = rnd.Next(10000);

System.Globalization.Calendar cal = new
System.Globalization.GregorianCalendar();

int month = 1 + rnd.Next(12);

int year = DateTime.Now.Year;

int day = 1 + (rnd.Next(31)%(cal.GetDaysInMonth(year, month)-1));

DateTime dt = new DateTime(year, month, day);

lock(cmdInsertCall)

{

cmdInsertCall.Parameters["@ToNumber"].Value = sb.ToString();

cmdInsertCall.Parameters["@CallDuration"].Value = duree;

cmdInsertCall.Parameters["@FromNumber"].Value = fromNumber;

cmdInsertCall.Parameters["@CallDate"].Value = dt;

// Executing the SP

cmdInsertCall.ExecuteNonQuery();

}

}

catch (Exception exc)

{

MessageBox.Show(exc.Message);

}

}



Any Idea ...???



Thanks

 

[Steve B. [Labo.Net]]

I wanted to use thread to create next parameters while the command is still
running instead of waiting the command finished before computing other
params ...

But i'll follow your advice and use only one thread

Thanks

steve

Hey Steve

Why do you think using 10 threads will speed it up? If it does anything it
would slow it down? The only reason you would want to use threads is if some
of them were waiting for some ressource. In this case that ressource is the
database connection, but that is what's taking your time - and the database
connections are automaticly pooled to speed that up.

First you should always create open and close your SqlConnection whenever
you get the chance. The built-in pooling will take care of that. Open it
*just* before you call ExecuteNonQuery and close it *imediately* after.
Don't worry about all the opening and closing (see above).

The reason you get the closed/open is probably the threads switching just
after the check for the connection state. You have to lock the thread around
the check and the actual opening, slowing down the application even more.

My advice is to not use threading and instead reuse what you can, except the
opened database states.

Per

Hi

I wrote a little software that call one stored procedure many times.

It works fine using one single thread, but since I use 10 threads to speed
up the process, I get lots of error, such "the connection is closed", "a
datareader is already open to the connection", etc...

Here is a simple view of my code :


private Thread[] _th = new Thread[10];

private void btnGo_Click(object sender, System.EventArgs e)

{



sqlConn.ConnectionString = ssd.ConnectionString;


if (sqlConn.State != ConnectionState.Open)

{

sqlConn.Open();

}


_insertedItems = 0;

_itemsLasting = (int)nudIterations.Value;

_totalItemToInsert = (int)nudIterations.Value;

for(int i=0; i<_th.Length; i++)

{

ThreadStart ts = new ThreadStart(StartCalling);

_th = new Thread(ts);

_th.Start();

}


ticker.Enabled = true;



}



private void StartCalling()

{

while (_insertedItems < _totalItemToInsert)

{

System.Threading.Interlocked.Increment(ref _insertedItems);

System.Threading.Interlocked.Decrement(ref _itemsLasting);

InsertCall();

//_insertedItems++;

//_itemsLasting--;

}

ticker.Enabled = false;

if (sqlConn.State != ConnectionState.Closed)

sqlConn.Close();

}

private Random rnd = new Random();

private void InsertCall()

{

try

{

// creating parameters

int fromNumber;

lock(cmdGetRandomCallNumber)

{

cmdGetRandomCallNumber.ExecuteNonQuery();

fromNumber = (int)cmdGetRandomCallNumber.Parameters["@CallNumber"].Value;


}

int indicatif = rnd.Next(10);

int ville = rnd.Next(1000);

int phone = rnd.Next(10000);

System.Text.StringBuilder sb =new System.Text.StringBuilder(20);

sb.AppendFormat("{0:00} - {1:000} {2:0000}", indicatif, ville, phone);

int duree = rnd.Next(10000);

System.Globalization.Calendar cal = new
System.Globalization.GregorianCalendar();

int month = 1 + rnd.Next(12);

int year = DateTime.Now.Year;

int day = 1 + (rnd.Next(31)%(cal.GetDaysInMonth(year, month)-1));

DateTime dt = new DateTime(year, month, day);

lock(cmdInsertCall)

{

cmdInsertCall.Parameters["@ToNumber"].Value = sb.ToString();

cmdInsertCall.Parameters["@CallDuration"].Value = duree;

cmdInsertCall.Parameters["@FromNumber"].Value = fromNumber;

cmdInsertCall.Parameters["@CallDate"].Value = dt;

// Executing the SP

cmdInsertCall.ExecuteNonQuery();

}

}

catch (Exception exc)

{

MessageBox.Show(exc.Message);

}

}



Any Idea ...???



Thanks

 

[AlexS]

If you want to use threading you should use its own separate connection in
each thread. Then you won't have your clashes and races.
It will speed up processing a bit but not much (percents not times) if
single CPU and standard disks.

In your case possibly better would be - when calling several times same
procedure - to have 2-3 threads only, which will get each list of calls to
be processed and will keep their connections open until complete. Or to
create batch of calls, which will be executed against DB as one SQL
statement or procedure.

HTH
Alex

Hi

I wrote a little software that call one stored procedure many times.

It works fine using one single thread, but since I use 10 threads to speed
up the process, I get lots of error, such "the connection is closed", "a
datareader is already open to the connection", etc...

Here is a simple view of my code :


private Thread[] _th = new Thread[10];

private void btnGo_Click(object sender, System.EventArgs e)

{



sqlConn.ConnectionString = ssd.ConnectionString;


if (sqlConn.State != ConnectionState.Open)

{

sqlConn.Open();

}


_insertedItems = 0;

_itemsLasting = (int)nudIterations.Value;

_totalItemToInsert = (int)nudIterations.Value;

for(int i=0; i<_th.Length; i++)

{

ThreadStart ts = new ThreadStart(StartCalling);

_th = new Thread(ts);

_th.Start();

}


ticker.Enabled = true;



}



private void StartCalling()

{

while (_insertedItems < _totalItemToInsert)

{

System.Threading.Interlocked.Increment(ref _insertedItems);

System.Threading.Interlocked.Decrement(ref _itemsLasting);

InsertCall();

//_insertedItems++;

//_itemsLasting--;

}

ticker.Enabled = false;

if (sqlConn.State != ConnectionState.Closed)

sqlConn.Close();

}

private Random rnd = new Random();

private void InsertCall()

{

try

{

// creating parameters

int fromNumber;

lock(cmdGetRandomCallNumber)

{

cmdGetRandomCallNumber.ExecuteNonQuery();

fromNumber = (int)cmdGetRandomCallNumber.Parameters["@CallNumber"].Value;


}

int indicatif = rnd.Next(10);

int ville = rnd.Next(1000);

int phone = rnd.Next(10000);

System.Text.StringBuilder sb =new System.Text.StringBuilder(20);

sb.AppendFormat("{0:00} - {1:000} {2:0000}", indicatif, ville, phone);

int duree = rnd.Next(10000);

System.Globalization.Calendar cal = new
System.Globalization.GregorianCalendar();

int month = 1 + rnd.Next(12);

int year = DateTime.Now.Year;

int day = 1 + (rnd.Next(31)%(cal.GetDaysInMonth(year, month)-1));

DateTime dt = new DateTime(year, month, day);

lock(cmdInsertCall)

{

cmdInsertCall.Parameters["@ToNumber"].Value = sb.ToString();

cmdInsertCall.Parameters["@CallDuration"].Value = duree;

cmdInsertCall.Parameters["@FromNumber"].Value = fromNumber;

cmdInsertCall.Parameters["@CallDate"].Value = dt;

// Executing the SP

cmdInsertCall.ExecuteNonQuery();

}

}

catch (Exception exc)

{

MessageBox.Show(exc.Message);

}

}



Any Idea ...???



Thanks