Is there a bug or limitation in LINQ to SQL which would cause stored procedures to timeout?

LINQ-to-SQL uses vanilla ADO. NET under the covers. At least as far as ADO.NET and SQL Server are concerned, you're not anywhere close to maxing out the number of parameters they can handle.

LINQ-to-SQL is very stored-procedure-friendly, as long as the stored proc isn't doing anything horrible like creating dynamic SQL inside the proc, or returning results from temporary tables How are you creating your Linq-to-SQL mapping for the stored proc? There's a line command tool in Visual Studio's SDK called "SQLMETAL" that creates . Dbml and .

Cs and, if specified by a command line switch, will map stored procedures as well. It does enough analysis on stored procedures to verify that LINQ to SQL will handle them, and will give you error messages for stored procs that don't meet its standards. Try running SQLMETAL and see what kind of messages you get back.

LINQ-to-SQL uses vanilla ADO. NET under the covers. At least as far as ADO.NET and SQL Server are concerned, you're not anywhere close to maxing out the number of parameters they can handle.

LINQ-to-SQL is very stored-procedure-friendly, as long as the stored proc isn't doing anything horrible like creating dynamic SQL inside the proc, or returning results from temporary tables. How are you creating your Linq-to-SQL mapping for the stored proc? There's a line command tool in Visual Studio's SDK called "SQLMETAL" that creates .

Dbml and . Cs and, if specified by a command line switch, will map stored procedures as well.It does enough analysis on stored procedures to verify that LINQ to SQL will handle them, and will give you error messages for stored procs that don't meet its standards. Try running SQLMETAL and see what kind of messages you get back.

Thanks Cylon. I'm aware that vanilla ADO is used under the covers (indeed the exception I'm seeing is an ADO exception) but didn't know that SqlMetal gives helpful output so I'll try that. To answer your question, I'm generating the dbml by dragging and dropping the stored procedure from the server explorer to the data context diagram in Visual Studio 2010.

– Stephen Kennedy Sep 5 at 16:06 I've always found SqlMetal to be a better tool than the drag-and-drop visual tool (which I consider to be demo-ware, not production quality). Linq-to-SQL does have certain constraints for stored procedures, and I know that SqlMetal checks them. – Cylon Cat Sep 5 at 16:11 Tried that, just one warning from an unrelated db object.

– Stephen Kennedy Sep 5 at 16:13.

I'll make the assumption that you've tried to increase the time out on you Linq data context, as you mentioned that most of the posts you found suggested that. However... What trips a lot of people up in cases like this is the web server, particularly if your running this on IIS7. Iv'e had cases in the past where I've spent countless hours chasing things down to find out where somthing is timing out, only to fix it in five seconds by changing the time out values for the server application in IIS7 managment panel.

I'm not saying this is definately your issue, but if you only have the web server set to 30 seconds (Which is the default) you can sit and change time outs in your data context all day long, and you'll never get anywhere. If you want to give this a try, log into the machine the web server is running on, expand the server / website in the left hand pane to drill down to the app your stored proc is run from. At this point you have a few places to look.

If you go back up to Website level EG: "Default Web Site" and click on that, then click on advanced settings to the far right and expand connection limits, you can set the number of seconds for the entire web site in there. If your calling your SP from a classic ASP file, then click on your webapp, and double click on the ASP icon in the center pane, expand "Limits Properties" and change the script time out value to suit. Lastly, if your doing this using some kind of CGI operation, click on your web app then double click on CGI, and change the time out in there.

Last but not least if none of the above help, you could try adding the following XML snippet to your apps web. Config file in the appropriate place, to control the . NET script execution time out: If your not running on IIS7 then you'll need to look into what ever control panel your server uses for similar settings.

Thanks for the detailed answer shawty but unfortunately IIS isn't part of this equation. It's a Windows service. – Stephen Kennedy Sep 8 at 12:16.

I reverted to vanila ADO and was getting the same exception so the answer has to be "no it's not a LINQ to SQL bug". I also refactored the SP per the parameter sniffing articles and it didn't help. As the code has started working again I can only imagine that this was a temporary system issue or database glitch.

For the benefit of others who have similar issues and find this question by search later I'll update further if I find anything more, as a colleague will be testing the code on another system shortly. EDIT: It was a system issue; specifically, my VMWare disk needed defragmenting. (Per advice on meta, I should post my own answer rather than update the question).

I have noted sometimes SQL reports 1 result set, and sometimes more. I'll try find the code I used to determine this. – leppie Sep 7 at 9:20 Try running exec sys.

Sp_procedure_params_rowset 'yoursp' a few times and see if the output remains the same. – leppie Sep 7 at 9:28 Yes, there are some IF clauses. – Stephen Kennedy Sep 7 at 9:46 It's pretty much an atomic piece of code which receives raw data and then works out what to insert and where.

As stated it doesn't use cursors or temporary tables though, just select, insert, declare, and boolean logic. Thanks for the tip re the system procedure. Waiting for colleague feedback at the moment.

– Stephen Kennedy Sep 7 at 9:52 If you have multiple return points, try make that single. – leppie Sep 7 at 9:53.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.