CODEWITHSUNDEEP
c#.netsqlperformanceoptimization
Ankur
Ankur

Reputation: 259

C# code and SQL Server 2008 stored procedure performance comparison

I am developing a high frequency trading application using C#, .NET 3.5 framework and SQL Server 2008 R2. I am subscribing to 70 instruments, for each instrument I am storing 1 minute bars. Each bar for each instrument consists of Open Price, High Price, Low Price, Close Price, Volume.

I am pushing all the one minute bars for each instrument in one common table where the primary key is StockID.

Now, for some calculation. I will need to construct 5 min bars, 10 mins bars, 15 mins bars etc. from the 1 min Bars. Example: I will loop though last 5 rows of 1 min bars and construct 1st bar with bar size = 5 min. And within that 5 min bar I will need to find Open Price, High Price, Low Price, Close Price, Volume. Similarly, I will need 180, 5 min bars. So, I will need 180 * 5 = 900 rows of 1 min bars. (From 900, I will construct 180 rows each of 5 mins)

  1. If I do a simple Select statement from C# code to get all 900 one min data and then loop and create 180 data with 5 min bar in my C# code and in each 5 min bar get open price, high price, close price, volume.

  2. Or, I write a SQL stored procedure to do the same above and return open price, high price, low price, close price, volumes for all 180 data of 5 min bars to C# code.

My question is, which will be much faster 1) or 2) and which will be more robust 1) or 2)

Hope, I have framed my question and scenario in detail for experts to guide me.

Upvotes: 1

Views: 1073

Answers (2)

alex.b
alex.b

Reputation: 4567

  • Stored Procedure solution is faster. At least because there is no need to transfer all needed data between sql server and c# app;
  • But, stored procedure is less rebust and much hard to maintain, I think.

Upvotes: 3

Wim
Wim

Reputation: 1096

You are thinking in terms of "loops". On a relational database you should think in terms of "sets". Working with loops in a stored procedure is often done with cursors. It is slow (like a 50 - 100 times slower than working set oriented) and the locking mechanism associated with cursors may lock out other processes. That's why you should always avoid using them.

The fastest solution is working with a stored procedure. There can be no discussion about that. The biggest difference is that it cuts out all network traffic. SP's run directly on the database, there is no network traffic slow-down. Debugging them can be a pain though, but I have heard Visual Studio 2010 offers some improvements on that.

The next thing you should think about is HOW to implement the stored procedure: You can go the slow locking way of loops and cursors or the fast set oriented way.

Based on your description, you might gain a lot in performance by keeping some data in two tables: one your regular table with all data, the other a "window-table" that only holds the data that you need to populate those 5 min, 10 min and 15 min, ... bars. If you can create a 15 min bar with the data of the three last 5 min bars, then store those 3 last 5 min bars in the window-table, when a 4th 5 min bars arrives, kick out the oldest 5 min bar and insert the new 5 min bar. When you can create the new 30 minute bar based on the last 2 15 min bars, then store those 2 15 min bars, .... Try to keep this window-tabel as small as possible, so it can stay in memory all the time.

Upvotes: 5

Related Questions