Improve update SQL query performance

I've an SQL database to contain stock bars downloaded from Yahoo!. I'm trying to create some indicators to analyze these stocks (i.e. Simple Moving Average). I am concerned with the performances of my query, which is simply UPDATE @stockname SET SMA = @value WHER开发者_如何学编程E id = @n . To update 2000 rows it takes 2 minutes. I tried with a stored procedure but the result is almost the same.

for (int i = 0; i < closing_prices.Count - length; i++)
{
   double signalValue signalValue = Selector.SignalProcessor(Signal,
                                        closing_prices.GetRange(i, length), length);
    //Write the value into the database
    string location = Convert.ToString(i + length + 1);
    this.UpdateWithSingleCondition("_" + Instrument, columnName,
         signalValue.ToString(), "id", location, "=", sql_Connection);
}

This cycle calls the stored procedure to update the column SMA each time a new value is generated. Is there any possibility to put directly the entire column into the database? I think this can save time. Anyway updating 500 rows in 2 min sounds very slow.

Could you tell me how to improve the execution time of my query?

---

Instead of writing values out one at a time, perhaps you could use a stored proc with table valued parameters to ship the data from your app to the DB in a single op then MERGE the data into your table, saving on a lot of round-tripping.

---

Analyze your performance. You must have SOME bottleneck. Your update count is really low. You should easily be able to do 10-30 updates per second which would translate to a lot more in 2 minutes.... and that is on a stock computer, not even one worth a database (which would mean many fast discs).

Do a performance analysis on sql server and find out your bottlenecks. You have all indices needed?

---

I would create a stored proc that receives a string. This string is an XML or delimeted string.

Then use one of the many string to table functions floating around

• (delimeted string) http://blogs.x2line.com/al/articles/150.aspx
• (xml) http://kennyshu.blogspot.com/2007/12/convert-xml-file-to-table-in-sql-2005.html

and convert the string into a temp table.

Then perform a insert from the temp to the destination table.

This way you make one call to the DB server and avoid chatter. Its a LOT faster than multiple calls.

Avoid table parameters since you cant call em from code.

---

First disable external key constrains. then enable them again:

To disable "ALTER TABLE" "WITH NOCHECK CONSTRAINT ALL"

To anable them, use "ALTER TABLE" together with "WITH CHECK CONSTRAINT ALL".