LINQ to SQL: First call

Question

I'm using LINQ to SQL to access the database (SQL Server 2005). The first call takes up to 10 seconds to retrieve the data, a second call takes less than a second.

What can be done to improve the performance of the first call to the database? The database action happens in the controller of a asp.net mvc application.

Thanks

Answer 1

Not sure this kind of timeout is LINQ or ASP.NET related. Do you also notice the timeout when using the database with ADO.NET?

Answer 2

I believe what you are experiencing is SQL Server caching the query and is normal. Now if the original 10 seconds is too much, then you need to capture the sql query (I would suggest profiler) and then review it. In the past I would run the sql in the management console with show actual execution plan selected. There are resources on the web to explain how to read it, but it should help you to find the bottleneck. HTH

Edit I mean to say it is normal for long running queries to speed up after they have been run once, since SQL Server caches the query (I believe the execution plan to be exact) for later use.

Wade

Answer 3

I doubt very much Linq-to-SQL is the culprit here. Can you post the T-SQL L2S is generating, along with rows counts and information on indexing?

Answer 4

I think what you're experiencing is the Asp.Net compilation process the first time the page is loaded, not a performance problem with LTS. One way to measure performance is to profile it with the Linq to Sql Profiler. It will tell you exactly what the query is that is being generated as well as execution times for both the query and your code.