Improve nested for each performance

Posted on 2013-11-13
Medium Priority
Last Modified: 2013-11-14
Datatable  dt with three columns

listReturns(Type string,  Value int)
Daily 1
Weekly 7
Bi Weekly 14
Monthly 30

For very row in dt I need to calculate the daily weekly, bi wekly and monthly returns by finding the Return i1 day ago, 7 days ago or a month ago.
Then divide the value with that of the given date.

For every ID I need to loop four times for each return type.
The nested for each takes an hour for 71000 Rows in dt. I am asuming my linq query to get the tri value is taking some time

                  foreach (DataRow item in dt.Rows)
                    foreach (var mt in listReturns)
                                  decimal tri = (from r in dtHistoricalPrices.AsEnumerable()
                                   where r["ID"].ToString() == item["ID"].ToString()
                                   && Convert.ToDateTime(r["BusDate"]) == Convert.ToDateTime(item["BusDate"]).AddDays(-mt.Value)
                                   && r["Return"] != DBNull.Value && r["Return"].ToSting() != string.Empty
                                   select Convert.ToDecimal(r["Return"]).FirstOrDefault();
                                    decimal dreturn =       (Convert.ToDecimal(item["Return"]) / tri) - 1;         
                                  DataRow dr = dt2.NewRow();
                        dr["ID"] = item["ID"];
                        dr["BusDate"] = item["BusDate"];
                                    dr["ReturnType"] = mt.Type;
                                    dr["ReturnValue"] = dreturn
Question by:countrymeister
1 Comment
LVL 10

Accepted Solution

joriszwaenepoel earned 2000 total points
ID: 39647259
Are you sure the Convert.ToDateTime(r["BusDate"])  is really necessary?  That needs to be executed again and again, for every row in dtHistoricalPrices, for every value in listReturns, for every row in dt.

Datasets and datatables are easy to work with, but they have a lot of overhead.  In a lot of cases you can improve the performance drastically if you avoid using datasets.  Try getting the data from the database in a collection of objects, with correctly typed properties for each column.  

You could start with the dtHistoricalPrices.  Create a HistoricalPrice class, Make a collection with an object of that type for every row in dtHistoricalPrices, and then do your LINQ query on that collection.  The performance will probably be a lot better.
If necessary, you can do the same thing with the dt datatable.

To start easy, you can convert the data from the datatable to the custom objects, but I would consider using a DataReader to get the data from the databse and create that collection of objects, and avoid the use of datasets and datatables.

Hope this helps.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

SingleRun is a tool that ensures that only one instance of an application is started, running it again brings the application to focus.
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
If you are looking for an automated tool which can generate reports for Outlook emails and other items from PST file, then you can go for Kernel PST Reporter tool. The reports which are created by this tool are helpful to analyze and understand PST …

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question