I have a List with some data and a database table which contains the same data. I want to get some data with complex LINQ query. Which set do you think I should use to perform faster. I mean SQL vs C#.
I used list way, because I think connection to database takes some time. I don’t know how to benchmark this?
>Solution :
It’s impossible to say for sure without knowing more about your situation.
Typically accessing data that’s already in-memory will be much faster than connecting to the database, because I/O operations are orders of magnitude slower than memory access.
But if you have a large data set, and your query only includes a small amount of data in the results, it’s likely that the database could take advantage of indexing to get just the data you’re requesting much more quickly. Leveraging the database this way consistently could help you avoid loading the entire data set into memory in the first place, reducing memory pressure overall.
Ultimately, if you’ve already got it working, and it’s working fast enough to not have a noticeable delay, I’d probably stick with what you’ve got rather than hyper-optimizing it.
Some things you definitely don’t want to do:
- Don’t use
.ToList()or.AsEnumerable()to load the entire set of data out of the database and then apply your query to all that data in-memory, every time the data is requested. - Don’t convert the in-memory list to an IQueryable (
.AsQueryable()) and then apply your query logic to it. This just forces a lot of compilation actions at run-time.