Alternative Data Structure to DataTable

Question

I need a data structure of some sort to do the following:

One "set" composed of many types such as string, integer, datetime and double. Many sets are added dynamically The sets are retrieved dynamically where information is pulled

Now the obvious solution is to use a DataTable. Define the datatable structure, and add a new row each time you need to add a new set. Pull data from the datatable when you need to.

Actually I have implemented it already using a datatable, but the problem is it is extremely slow for some reason. Since this is done thousands to millions of times performance can be problematic.

Is there an alternative datatable type of data structure with better performance that I can use or should I build my own class using Lists<> ?

Solution 1

Depending on your use case I would recommend using List<object[]> (since you mentioned dynamic schema) as central data structure, but you will need to maintain the schema info yourself if you need it later on.

If you need to bind the UI to the data this approach will add a lot of extra manual work, it's better suited for background processing of large amounts of data.

We have used this approach in the past and were able to save 2/3 of memory and 80% of execution time when bulk handling data compared to data tables.

Solution 2

One alternative way of approaching problems like this: use a sqlite database in memory.

Sounds like a weird thing to do at first, but you can put quite complex structures into tables, and you get the whole power of SQL to work on your data. SQLite is a tiny lib, so it won't bloat up your code. Integrating the DB into your code might be kinda strange at first, put performance should work on huge data sets (since that's what DBs are made for). And if you ever need to save that data to disk, you are already done.

Depending on the details of your problem, it might even be a good idea to move to a bigger db back end (e.g. postgres), but that is hard to tell from here. Just don't dismiss this idea too easily.

Comments

[ceds]

Hey there, thank you for the suggestion! I think think this might just work, I have never worked with object[] before. 80% Execution speed? That is faster but not by much.

[ntziolis]

That was a misunderstanding, it's not running 20% faster, it's running 80% faster due to the existing overhead in the DbDataReader to DataTable code.

[ceds]

Ah ok I see hehe, then it is excellent. I'm going to implement it and see what performance improvement I get. Thanks!