Optimizing query inside foreach loop

[while(!success)]

Hi guys,

I'm hoping you can help me find a more elegant solution to my query inside a foreach loop. Here is the loop in question:

foreach (DataRow row in dt_blah.Rows)
{
    SqlCommand sc = con.CreateCommand();
    sc.CommandType = CommandType.Text;
    sc.CommandText = "select blah blah blah .. where S = @s1 and p = @p1";
    sc.Parameters.AddWithValue("@s1", ddl.SelectedValue);
    sc.Parameters.AddWithValue("@p1", row["Category"]);
    con.Open();
    SqlDataAdapter sda = new SqlDataAdapter(sc);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    con.Close();
}

the problem is that the query relies on the value of each "Category" row in the dt_blah table. I have ran the query as a standalone and it does not even take a second, so I believe i'm safe to say that it is not my query that needs modifying. Have you guys encountered this kind of issue? and if so can you offer any valid solutions?

Thank you for your time and help.

[AleMonteiro]

The problem is making a loot of separeted querys, that's not good. Always go for the least number of database connections possible.

Try like this:

SqlCommand sc = con.CreateCommand();
sc.CommandType = CommandType.Text;

int i = 0;
string sql = string.empty;

foreach (DataRow row in dt_blah.Rows)
{
    // Use ´i´ to keep the parameters names different
    sql += String.Format("select blah blah blah .. where S = @s1{0} and p = @p1{0}", i);    

    sc.Parameters.AddWithValue("@s1" + i, ddl.SelectedValue);
    sc.Parameters.AddWithValue("@p1" + i, row["Category"]);

    i++;
}

sc.CommandText = sql;
con.Open();
    SqlDataAdapter sda = new SqlDataAdapter(sc);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    con.Close();

[Ketsuekiame]

Have you heard of the TPL (Task Parallel Library)?

It means you can do things like:
Parallel.ForEach(dt_blah.Rows, dataRows => { /* for loop logic or pass dataRow to a method of your choice */ });

This will execute as many iterations of the loop in parallel as it can. This has the potential of speeding up the execution of your foreach loop as it looks like there would be no problem executing these in parallel.

Here is an MSDN article that will help :)

[while(!success)]

AleMonteiro, thank you that seemed to speed up my query a bit. I do not understand, however, why you are appending the count to the variable passed to query? Could you please clarify this for me.

Ketsuekiame, I also attempted using your suggestion, but I couldn't get the Parallel keyword to be recognized. I tried using every directive and even used the one described in msdn's documentation (System.Threading.Tasks.Parallel) to no avail.

[while(!success)]

Actually nevermind, I am getting a "Incorrect syntax near the keyword 'and'." when trying to fill my datatable. Please advise! Thank you.

[AleMonteiro]

While, I use the counter to distinguish the parameters. In each iteration of the loop an p1 parameter is added, but if you don't use the counter all p1 parameters will have the same value, and that's not the desired result.
That's why we use the conuter, to add an different value for the p1 parameter in each iteration.

About the incorrect syntax, try adding an ; to the end of the select statement.

And try this code, I think it'll be a littler faster too:

SqlCommand sc = con.CreateCommand();
sc.CommandType = CommandType.Text;

int i = 0;
System.Text.StringBuilder sql = new System.Text.StringBuilder();

foreach (DataRow row in dt_blah.Rows)
{
    // Use ´i´ to keep the parameters names different
    sqlAppendFormat("select blah blah blah .. where S = @s1 and p = @p1{0} ; ", i);    

    sc.Parameters.AddWithValue("@p1" + i, row["Category"]);

    i++;
}

// I removed this from the loop because all @s1 parameters have the same value
sc.Parameters.AddWithValue("@s1", ddl.SelectedValue);

sc.CommandText = sql.ToString();
con.Open();
    SqlDataAdapter sda = new SqlDataAdapter(sc);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    con.Close();

[while(!success)]

AleMonteiro,

Thank you for the response. I see what you are doing now with the variable name. I have the application running without any errors, however, I do not get the desired results. When viewing the DataTable in debug mode, I have found that the table is actually empty in sda.Fill(dt)

Thank you again for all your help.

[AleMonteiro]

Oh, I didn't notice that... but your are making a lot of selects, so they can return in only one datatable.
You have to use an DataSet, like this:

SqlDataAdapter sda = new SqlDataAdapter(sc);
    DataSet ds = new DataSet();
    sda.Fill(dt);

And then use ds.Tables, for each select there'll be an table.