Manage DataTable with lots of information

Question

Manage DataTable with lots of information

Navigation

#1 by (4 votes)

3

I have here a "small" problem with a DataTable .

From a SQL query I'm getting a huge amount of records (> 1,000,000 ), and although the query is relatively fast, loading DataTable from Fill() is extremely slow and, in some cases, it generates an error of System.OutOfMemoryException because the process where the code is being executed exceeds the 4Gb (mandatory compile on x86).

My question is if there is any way to reduce the loading time of DataTable and at the same time prevent memory from exceeding the limit.

Please note that this information will be used later for XML serialization.

desempenho c# .net winforms datatable

asked by anonymous 31.08.2018 / 15:32

1 answer

How do I put an html tag inside the php code to format the text there? Relationship between bean and view

score 4 · Accepted Answer

The DataTable was an interesting idea, but very poorly implemented, it really carries too much memory.

Then came the need to do a real ORM, and Microsoft did Entity Framework than is heavy, annoying to use at first, is slow, but works much better. And EF Core is not that bad either. I'm not a fan of him for several reasons, but it's an evolution.

Curiously, in the beginning, it had better technology for most cases, which is DataReader , if you just need to read data, if you do not need to be online with the database it is always the right solution. In fact even if you need to write and be online % is only feasible for simple things, and just because the problem is not very noticeable, not because it is good.

Direct access to the database provider or use a simple ORM such as Dapper is the quickest option you can get. The first one has no extra layer, it only gives you access and you make all access in hand. It gives less work than you imagine in most cases, even though you have to take care of certain things. Dapper generates objects for you and achieves incredible performance anyway and delivers more, which can be very useful, especially if you are going to serialize.