Timeouts when calculating stock ratings using LINQ

Question

I'm getting a lot of timeouts with the following code and I'm running this method around 10,000 times so I need to figure out how I can optimize this so the timeouts will stop. I am getting timeout errors when I try to call the fill method and I believe it is because the queries aren't done processing yet. It is a local database and it is running SQL Server 2014.

public static StockRating performCalculations(SymbolInfo info)
{
    var calc = new Calculations(info.Symbol, info.Market);
    StockRating rating = new StockRating();

    try
    {
        using (DailyGlobalDataTableAdapter dailyGlobalAdapter = new DailyGlobalDataTableAdapter())
        using (DailyNyseDataTableAdapter dailyNyseAdapter = new DailyNyseDataTableAdapter())
        using (DailyGlobalDataDataTable sandp500Table = new DailyGlobalDataDataTable())
        using (DailyNyseDataDataTable dailyNyseTable = new DailyNyseDataDataTable())
        {
            // fill the s&p500 info first
            dailyGlobalAdapter.ClearBeforeFill = true;
            dailyGlobalAdapter.FillBySymbol(sandp500Table, Calculations.sp500);

            var sp500StockDataQuery = from c in sandp500Table
                                      select new StockData { Close = c.AdjustedClose, High = c.High, Low = c.Low, Volume = c.Volume, Date = c.Date };

            calc.sp500Data = sp500StockDataQuery.AsParallel().ToList();

                    dailyNyseAdapter.FillBySymbol(dailyNyseTable, info.Symbol);

                    var nyseQuery = from c in sandp500Table
                                    where c.Date >= DateTime.Now.Subtract(TimeSpan.FromDays(60))
                                    join d in dailyNyseTable on c.Date equals d.Date
                                    select new StockMarketCompare { stockClose = d.AdjustedClose, marketClose = c.AdjustedClose };

                    var nyseStockDataQuery = from c in dailyNyseTable
                                             select new StockData { Close = c.AdjustedClose, High = c.High, Low = c.Low, Volume = c.Volume, Date = c.Date };

                    calc.stockVsMarketCompareData = nyseQuery.AsParallel().ToList();
                    calc.stockData = nyseStockDataQuery.AsParallel().ToList();

            // get the pct return data that is needed for calculations
            calc.fillPctReturns();

            if (calc.stockPctReturns.Count > 0 && calc.marketPctReturns.Count > 0)
            {
                // perform the calculations here
                rating.estimatedReturn = calc.calculateExpectedReturn();
                rating.longRating = calc.calculateRating(Calculations.ratingType.long_rating);
                rating.market = calc.Market;
                rating.mediumRating = calc.calculateRating(Calculations.ratingType.medium_rating);
                rating.shortRating = calc.calculateRating(Calculations.ratingType.short_rating);
                rating.symbol = calc.Symbol;
                rating.risk = calc.calculateRisk();
                rating.isMarginEnabled = calc.isMarginEnabled();
                Console.WriteLine("Ratings calculated for " + info.Symbol + " in the " + info.Market + ". " + stopwatch.ElapsedMilliseconds / 1000 + " seconds elapsed.");
            }
            else
            {
                rating = null;
            }
        }
    }
    catch (Exception ex)
    {
        rating = performCalculations(info);
    }

    return rating;
}

Parallel.For(0, activeSymbolsCount, j =>
                            {
                                symbol = symbolTable.AsParallel().ElementAtOrDefault(j).Symbol;
                                SymbolInfo symbolClass = new SymbolInfo(symbol, market);
                                StockRating rating = performCalculations(symbolClass);
                                saveToTable(rating);
                            });

@Heslacher Oh I'm sorry all of the fill methods. This error message about the command time outs only gets thrown at any of the lines where the adapter performs the fill method. I believe my linq queries don't get finished properly and don't close out the threads properly — DarthVegan
– DarthVegan, Commented Jan 4, 2015 at 10:27

Heslacher · Accepted Answer · 2015-01-05 06:15:02Z

Exception handling

You are doing some very creative exception handling here. If an exception occurs, no matter what kind of, you are just calling the method again. Do you think there is some magic going on, cleaning the exceptions by calling the code again ?
If you need to call the method again you shouldn't do this from within the method, but from outside at the calling point.
You should also do this only in cases of exceptions where you can assume that by giving some time they could be resolved. But you shouldn't do this infinite. You should use some sort of counter and stop if it has reached a value/border.

"Fill" exception

The linq seems to be unrelated to the exceptions caused by the "Fill" methods. If the exceptions occur if any of the fill methods is called, then there is no linq involved. You should better check how you have implemented these methods.

EDIT

I guess the problems are inside the .FillBySymbol() method. Without seeing this method it is hard to tell the cause of the problems but I assume that your sql query is just returning to many data.

General

You should extract the evaluating of the date withing 60 days from now to a separate variable.

DateTime searchDate = DateTime.Now.Subtract(TimeSpan.FromDays(60));

var nyseQuery = from c in sandp500Table
                where c.Date >= searchDate 
                join d in dailyNyseTable on c.Date equals d.Date
                select new StockMarketCompare { stockClose = d.AdjustedClose, marketClose = c.AdjustedClose };

You have some code duplication which could be removed.

This

var sp500StockDataQuery = from c in sandp500Table
                          select new StockData { Close = c.AdjustedClose, High = c.High, Low = c.Low, Volume = c.Volume, Date = c.Date };

and this

var nyseStockDataQuery = from c in dailyNyseTable
                         select new StockData { Close = c.AdjustedClose, High = c.High, Low = c.Low, Volume = c.Volume, Date = c.Date };

together with the call to .AsParallel().ToList()

If both DailyGlobalDataDataTable and DailyNyseDataDataTable share a base class or an interface whith the properties AdjustedClose, High,Low,VolumeandDate` this can be extracted like

private static List<StockData> GetStockData(commonBaseOrInterface table)
{
    return (from c in table
            select new StockData { Close = c.AdjustedClose, High = c.High, Low = c.Low, Volume = c.Volume, Date = c.Date }
            ).AsParallel().ToList();;
}

this can then be called like

calc.sp500Data = GetStockData(sandp500Table);
calc.stockData = GetStockData(dailyNyseTable);

Naming

Based on the naming guidelines methods should be named using PascalCase casing.

Nit picking

Your indention of the code could be better.

First off thank you for your suggestions. My class just taught me that you are never supposed to repeat code and I didn't notice that improvement that you suggested. I'm trying to get better with my naming and indentation but I'm not sure how to best handle the time out exception. I included the method that I'm calling the method from so if I get an exception should I return null and then try again from the calling method or something like that? — DarthVegan
– DarthVegan, Commented Jan 4, 2015 at 10:43
@user3610374 I'm assuming that you're using Visual Studio for an IDE? Pressing Ctrl+E then Ctrl+D will automatically fix the indentation (mostly). — RubberDuck
– RubberDuck, Commented Jan 4, 2015 at 12:27

Community · Accepted Answer · 2017-04-13 12:40:46Z

Your performance issue isn't due to the LINQ queries that you have presented here, per se. Rather, the problem is the fact that you need to run this method 10000 times. Any code that runs SQL queries in a loop that executes more than a handful of iterations will result in misery.

The solution is to devise one query using SQL or LINQ that states all of what you are trying to accomplish: what stock symbols you are interested in and how to rate them. Then, all of the calculation happens in the database server, without having to execute thousands of queries. That is, after all, the purpose of SQL: to tell the database server how to manipulate and retrieve exactly the information you want.

Since you didn't include your Calculations class, I can only offer generic advice. For examples of how a well-crafted SQL query can eliminate nearly all of the the client-side code, see:

all that my code is trying to do is download the data that you see above in the linq queries and it saves it to a container item in the calculator class. All I'm executing is the join statement to get matching dates on both tables and then saving that info to the calculator class as a list of the stockdata class — DarthVegan
– DarthVegan, Commented Jan 4, 2015 at 10:30

Stack Exchange Network

Timeouts when calculating stock ratings using LINQ

2 Answers 2

You must log in to answer this question.

Linked

Hot Network Questions

Timeouts when calculating stock ratings using LINQ

2 Answers 2

You must log in to answer this question.

Linked

Related

Hot Network Questions