StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think part of the performance hit is in the clearing and adding of parameters (along with ineffecient string manipulation). What happens if you change up the structure a little bit like this?</p> <pre><code>SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.Add(new SqlParameter("@name", "")); cmd.Parameters.Add(new SqlParameter("@country", "")); // etc.. for (int i=0; i<1000; ++i) { // etc... cmd.Parameters["@name"].Value = i.ToString() + "Bob" + i.ToString(); cmd.Parameters["@country"].Value = i.ToString() + "Uganda" + i.ToString(); DateTime cmdStart = DateTime.Now; cmd.ExecuteNonQuery(); DateTime cmdEnd = DateTime.Now; TimeSpan len = cmdEnd - cmdStart; } </code></pre> <p><strong><em>Added</em></strong></p> <p>I was also looking at the code wrong, and I just now realized that this is a CommandType.Text, isn't it? </p> <p>Is setting up a true stored procedure on the server, and then calling this by specifying CommandType.StoredProcedure and passing the Stored Procedure name instead of an QL statement an option for you? I realize I'm not answering your base quesiton, but I really don't think the length of the CommandText matters to SQL Server so I'm looking at other possible performance barriers.</p> <p>This is a shot in the dark, and I'm hoping someone with a working knowledge on the internals of how the CommandObject parses text SQL statements with parameters can verify this, but I'm <em>guessing</em> that what happens is that the Command object has to parse the CommandText when it calls ExecuteNonQuery() (or ExecuteScalar(), etc).</p> <p>String manipulation is expensive, and if you're forcing the command object to re-parse the parameters every time, this could be adding in come time as well.</p> <p>Add to this the fact that true stored procedures generally perform better due to compiled execution plans, and you may see some improvements.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload