CODEWITHSUNDEEP
sql-serverlinqmemorydatatable
Johnny
Johnny

Reputation: 425

Querying DataSet in memory using LINQ vs querying SQL Table directly (dictionary table) - performance

I found no exact answer yet, so:

Imagine a 3-column dictionary table structured following way:

int | string1 | string2 (where int+string1 is pk)

Table has more than 20k rows and it's queried very extensively by its pk to return string2 value

What would be the best approach here?

1. Direct LINQ DB request

pro: query by PK which indeed is very fast

cons: possibly few hundred calls per sec to db server

2. Preload data once to DataTable object

pro: All data is in local .net memory (no need to go every time to db)

cons: usage of LINQ requires calling AsEnumerable() on DataTable which returns enumerable object as DataSource for LINQ query. Is LINQ query on object more efficient here than calling DB? is PK constraint used here?

3. Preload all table to Dictionary<string, string> where key is int.ToString()+string1 and value string2

pro: PK constraint always used and dictionary responds directly with correct value

cons: Is is it better than 1 or 2?

4. Your better idea

My great curiosity awaits answers...

Upvotes: 1

Views: 1889

Answers (1)

Johnny
Johnny

Reputation: 425

Finally I got a moment to write a simple test application, but using the real data, so the results are very relevant in this case.

So - once I preloaded around 4k records to Dictionary and did 100k loops over with result of retrieving the data 00:00:00.950095 !!

Second time I did same 100k loops asking database every single time with following result: 00:02:32.81...

Seems the choice is easy.

Upvotes: 3

Related Questions