Datatable.compute slow with like condition

Question

Hi everyone

I have a problem with my asp.net program. I am doing a Datatable.compute on a very large database with a like condition in it. The result takes something like 4 minutes to show or does a Request timeout. If I do the same request with a = and a fix text, it takes nearly 1 minute to show, which for my use is acceptable.

Here is the line that is so slow:

float test = (int)Datatbl.Tables["test"].Compute("COUNT(pn)", "pn like '9800%' and mois=" + i + " and annee=" + j);

I have been searching for a solution for 2 days.

Please help me.

Answer 1

Are you retrieving the data in your Datatable from a database? Do you have access to the database?

If so, one option is to research methods of moving this lookup and aggregation into the database instead of doing it in your C# code. Once it is in the database, if required, you could add indexes for the 'mois' and 'annee' columns which may speed up the look up considerably. If '9800' is a hardcoded value, then you could even add a denormalisation consisting of a boolean column indicating whether the 'pn' column begins with '9800' and put an index on this column. This may make the lookup very fast indeed.

There are lots of options available.

Answer 2

I found it.

I use the Dataview and send the result to a DataTable. This sped up the process 10 times