Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Improve nested for each performance

Posted on 2013-11-13
Medium Priority
234 Views
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()

&& 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
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.
0

## Featured Post

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
###### Suggested Courses
Course of the Month12 days, 16 hours left to enroll