Memory Leakage using datatables

Question

I have situation in which i'm compelled to retrieve 30,000 records each to 2 datatables.I need to do some manipulations and insert into records into the SQL server in Manipulate(dt1,dt2) function.I have to do this in 15 times as you can see in the for loop.Now I want to know what would be the effective way in terms of memory usage.I've used the first approach.Please suggest me the best approach.

(1)

for (int i = 0; i < 15; i++)
{
  DataTable dt1 = GetInfo(i);
  DataTable dt2 = GetData(i);
  Manipulate(dt1,dt2);
}

(OR)

(2)

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
for (int i = 0; i < 15; i++)
{
  dt1=null;
  dt2=null;
  dt1 = GetInfo();
  dt2 = GetData();
  Manipulate(dt1, dt2);
}

Answer 1

In theory, the first example is more efficient, because in the second example you start by creating two DataTable objects that won’t get used. However, these two DataTable objects contain little data, so the overhead in memory usage is minimal and almost impossible to measure.

Trying to optimize this example for performance seems to me premature optimization, what of course is a bad thing. You should go for the most readable solution first, and only when it is too slow, optimize it.

In your case however, the first code snippet is definitely more readable than the second one, because it is less code, the scope of variables is much shorter and you don't needlessly reset the instances to null.

Answer 2

From the code you've shown, the new DataTable() calls, and the assignment statements to null out dt1 and dt2 again, are meaningless. The only part you're interested in is what's returned by GetInfo(i) and GetData(i). You are allocating empty DataTable objects when you don't have to.

I'm curious whether it's possible in your case to rethink a bit of your design. When you are working with 30,000 records, it's less-than-ideal to do multiple database operations for each record. I've often found that the biggest performance hit with database-centric operations is caused by issuing multiple select statements when I could have done it in one statement. Thinking in terms of set or batch operations, is it possible you could do just one or two database calls to get back a list of items, and iterate over them? If you are troubleshooting a performance problem, this would be a good place to start.

Before you go making changes, measure and verify what operation takes the longest, by putting in timing code, and monitoring the application to see how much memory it's actually using. Focus your efforts on optimizing the slowest part. I hope you find this helpful =) I don't mean it to be in any way patronizing.