Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Improve nested for each performance

Posted on 2013-11-13
1
Medium Priority
?
232 Views
Last Modified: 2013-11-14
Datatable  dt with three columns
ID
BusDate
Return


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
                              }
                  }
0
Comment
Question by:countrymeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 10

Accepted Solution

by:
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

660 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