Linq to SQL and Realtime Data

Question

I have an application that needs to constantly (every 50ms), call to an MVC action, and pickup/drop off data.

I am using Linq to SQL and MVC because of how simple they are to implement, and I know that they aren't perfect when it comes to performance, but it works relatively well, but the best speed I can get with my current approach is 200ms (without requests overlapping).

Each call to the site will create a new instance of the datacontext, query/insert it and return that data.

Is there a way to have the datacontext static, but submitchanges say every 5 seconds, so that i am pretty much hitting an in-memory version of the data?

Edit:

I built a completely disconnected architecture that contains all the same properties and objects of my context, and I statically declare that object on application_start(), and on every X requests, a thread is spun to attach all the disconnected objects and store it to the database.

This has successfully reduced my round-trip time to only 100ms, a great improvement, but it is sill lacking from what it needs to be for "real-time"

I'm getting to the level of micro-optimization, but I can't seem to push it any faster.

Answer 1

DataContext is meant to be created each time you go to the database. That shouldn't be a bottleneck.

If you're concerned about expensive creation of database connections, that might not be a problem. There is a small connection poll so the connections are reused by subsequent calls.

What you can do to improve performance (I haven't heard a word it is poor right now) is to replace the auto-generated SQL with stored procedures. You will save a little bit on recreation of an execution plan.

Answer 2

CompiledQuery may help with performance. But it will never be faster than classic ADO.NET. If performance is main concern, any ORM is very bad choice.

You can always mix them (Linq2SQL + ADO.NET) to achieve optimal performance.

Answer 3

I don't think that creating a LINQ context/querying is your bottleneck here. There is a slight overhead in using it (just like any ORM), but it shouldn't be significant to you unless you are creating a LOT of contexts and complex query trees.

My guess would probably be that LINQ is not generating the query that you are expecting. It may actually generate a bunch of queries if you are not careful, and try to fetch from several tables. If you want to find out what queries it is running you can use

context.Log = Console.Out; // Or some other stream

You can also use the excellent LINQPad to try out your queries. If this is not the problem you should profile your code using a profiler, I personally like dotTrace.