CODEWITHSUNDEEP
c#performancedatatableado.netdataview
مسعود
مسعود

Reputation: 689

Increasing the query Performance on DataTable in C#

The code is trying to lookup values from lookup table. To improve the performance of the code I decided to give the look up function a sorted table instead of the original table. As I heard that creating a dataview helps improve the performance of select, but I am not convinced by the implementation as I am converting the data view to a table again. So I don't understand how it is helping. There was almost no difference in the performance, but maybe for larger databases it may differ. Can someone provide a suggestion to me If I am doing it right or wrong or how to improve performance.

Creating Data View

        List<DataView> dvTablesLookup = null;
        List<DataTable> dtTablesLookup = null;


        dvTablesLookup = datatablesLookup.Select(dt => new DataView(dt)).ToList();
        dvTablesLookup.ForEach(x => x.Sort = sortLookup);
        dtTablesLookup = dvTablesLookup.Select(dv => dv.ToTable()).ToList();

Calling Function

result.AddRange(this.GetValueFromLookup(filter, lookupValueField.StringValue, dtTablesLookup[i]));

Function for lookup. The function takes a lookuptable as argument. 

private object[] GetValueFromLookup(MultipleKeyConditionBuilder filter, string lookupValueField, DataTable datatableLookup)
    {
        object[] result;
        DataRow[] rows = datatableLookup.Select(filter.Condition);
        result = new object[rows.Count()];
        for (int i = 0; i < rows.Count(); i++)
        { 
            result[i] = rows[0][lookupValueField];
        }

        return result;
    }

Upvotes: 1

Views: 1225

Answers (1)

مسعود
مسعود

Reputation: 689

Using Dictionaries I realized there were a lot of repetitions in my data and same query was being executed repetitively giving me the same result. So I decided to use dictionaries, it improved the performance significantly from few minutes to few seconds.

Using Views: The benefits of View solely depends on data and query conditions. As the purpose of query was to lookup, view didn't benefited me at all instead for larger records it was taking even more time.

Upvotes: 1

Related Questions