Share via

Parallel SqlDataReader and reader.Read()

  • Article

Question

Thursday, September 10, 2015 12:36 AM

I want to do a consult in Parallel mode, or which is the better way to do the consult to SQL.

The consult spends 3 minutes doing 7 cycles from 7 stocks, but I need that spend less time, maybe by doing the 7 consults at the same time and return the result.

My problem with the code is that reader.Read() maybie shock between the consults.

List<List<clsExistenciaA>> ListaExistencia = new List<List<clsExistenciaA>>();
SqlConnection objConexion = new SqlConnection();//conexion a sql
try
{
    objConexion.ConnectionString = @"Database=DataDB;Data Source=local;User Id=sa;Password=20011$;MultipleActiveResultSets=True";
    objConexion.Open();
}
catch { MessageBox.Show("No se puede conectar al servidor"); }

try
{
    string almacen;
    int i = 0;
    foreach(clsExistenciaA A in AlmaceneS)
    {
         //Parallel.ForEach(AlmaceneS, clsExistenciaA =>
         //{
         //    lock (AlmaceneS)
         //    {
         //        almacen = clsExistenciaA.CodigoAlm;
         almacen = A.CodigoAlm;

         SqlCommand comm = new SqlCommand();//crea command
         comm.Connection = objConexion;// se agrega la conexion al comando
         comm.CommandType = CommandType.Text;// se define el tipo de comando
         string sql = "";

         sql = "select c_codigo_alm, v_nombre_alm from invalmacen (nolock) where c_pedido_alm='almacen'";
         comm.CommandTimeout = 1000;
         comm.CommandText = sql;// se agrega la consulta a el comando
         SqlDataReader reader = comm.ExecuteReader();// se ejecuta el comando en un data reader

         ListaExistencia.Add(new List<clsExistenciaA>());
         string piezaN = "0";
         string kiloN = "0";

         while(reader.Read())
         {
             if (reader["pieza"].ToString() != "")
             {
                piezaN = reader["pieza"].ToString();
             }
             if (reader["kilos"].ToString() != "")
             {
                 kiloN = reader["kilos"].ToString();
             }

             ListaExistencia[i].Add(new clsExistenciaA()
             {
                 pro = reader["pro"].ToString(),
                 nompro = reader["v_nombre_pro"].ToString(),
                 pieza = Math.Round(double.Parse(piezaN), 2),
                 kilos = Math.Round(double.Parse(kiloN), 2),
                 CodigoAlm = almacen,
              }); // se agregan los datos a un Ilist

          }

          reader.Close();

     }
     ////}
     //});
    return ListaExistencia;

All replies (12)

Saturday, September 12, 2015 6:54 AM ✅Answered

SqlConnection cannot be used from multiple threads, you'll need to move the connection code inside the foreach block if you want to use Parallel.ForEach.

Monday, September 14, 2015 5:13 PM ✅Answered

The final solution I think that I got to optimize the query, because this query is just an example, the real query is so large.

Thursday, September 10, 2015 7:15 PM

Nobody? I wanna cry

Saturday, September 12, 2015 6:35 AM

Hi Juan,

>>My problem with the code is that reader.Read() maybie shock between the consults.

This forum is discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools. Based on your code, I see the code is OK to me. But from above messgae, I cannot understand what you mean about  "shock between the consults". Could you clarify more details?

Best regards,

Kristin

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Saturday, September 12, 2015 3:15 PM

I did in this way too, but not found, I tried all the forms that I can imagine, but the query only do one consult by one, everyone waiting for his turn, I think

Saturday, September 12, 2015 9:35 PM

Well, I'm not really sure what you are trying to do but I'll note a few problems:

  • The commented out code has a lock (AlmaceneS) in it, that simply defeats the purpose of Parallel.ForEach.
  • ListaExistencia is accessed by multiple threads, that's incorrect as List<T> is not thread safe.
  • The query has no parameters so all threads will run the same query and obtain the same data, I doubt that this is what you need.
  • The almacen variable is defined outside the foreach loop but it probably needs to be defined inside.
  • The i variable isn't modified anywhere, it's always 0

Monday, September 14, 2015 3:34 PM

With lock the code run fine but with the 3 minutes, without it only run 1 query at the same time without any order.

The query change the parameter in c_pedido_alm='almacen'.

The variable almacen is iniciated outside but get a new value inside the foreach loop.

The variable i is only used when read add the values in List in [0] array.

Monday, September 14, 2015 4:10 PM

"With lock the code run fine but with the 3 minutes, without it only run 1 query at the same time without any order."

What's for sure is that if you put a lock there you defeat the whole purpose of Parallel.ForEach as all the work will be serialized. So that lock must go.

But there's at least one place where a lock as needed - "ListaExistencia.Add(new List<clsExistenciaA>());". Since lists aren't thread safe you need to serialize the add using a lock.

And it appears you need another lock for "ListaExistencia[i].Add(new clsExistenciaA()..." because i is always 0 so all threads end up adding to the same list.

"The query change the parameter in c_pedido_alm='almacen'."

Either I don't understand what you are trying to say or you don't understand what the code is doing. That query doesn't have any parameters that can change.

"The variable almacen is iniciated outside but get a new value inside the foreach loop."

And that only works correctly in the foreach case. If you use Parallel.ForEach you'll end up with multiple threads using the same variable and that's bad. More generally, variables should be defined in the innermost possible scope. If the variable isn't used after foreach then there's no reason to define it outside, with or without Parallel.

"The variable i is only used when read add the values in List in [0] array."

So there's no need for it and it only generates confusion.

Monday, September 14, 2015 4:37 PM

If I dont use lock, spend the same 3 minutes.

Yes, the query change the "almacen" value with the loop with almacen = clsExistenciaA.CodigoAlm;

I think that SQL server dont acept parallel consults at the same time because every consult of the 7 queries wait hi own turn.

Monday, September 14, 2015 5:00 PM

"I think that SQL server dont acept parallel consults at the same time because very consult of the 7 queries wait his turn."

Well, that's another matter. SQL Server certainly can handle multiple queries but how effective is at that depends on various factors from server hardware to the specific queries that are run and table data.

Apparently you're connecting to a local server. One thing you may want to check is who using more CPU time - the SQL Server process or your own application. Also check the disk usage. In short, try investigating where the bottleneck is.

Monday, September 14, 2015 5:20 PM

Eh, a large query, that explains it :).

Monday, September 14, 2015 5:44 PM

Yes, 1 time spend 30 seconds, 7 times spend 3 minutes Dx haha

Thank you so much