CODEWITHSUNDEEP
sqlsql-servert-sql
Antonio De Negri
Antonio De Negri

Reputation: 3

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 WHERE 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?

Upvotes: 0

Views: 273

Answers (4)

TomTom
TomTom

Reputation: 62093

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?

Upvotes: 1

Slappy
Slappy

Reputation: 4092

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

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.

Upvotes: 0

nerkn
nerkn

Reputation: 1982

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".

Upvotes: 0

spender
spender

Reputation: 120400

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.

Upvotes: 1

Related Questions