0

I am developing a REST like API in which an initial log in call will be made, a database row created, and a "log in session key"(GUID/uniqueidentifier) will be returned to the client. This key will then be used on all subsequent calls to the API as a security check, until log-out. On EVERY API call I plan to ask the database to look up the row using the key and, if the row's time stamp has not yet expired, allow the API to serve what it needs to.

For a simple select statement that may happen hundreds of times per "log in session," would pure SQL preform better than LinqToSQL in this scenario?

ToddBFisher
  • 11,370
  • 8
  • 38
  • 54
  • 1
    Maybe this can help -- I think the short answer is it depends: http://stackoverflow.com/questions/494816/using-an-orm-or-plain-sql – sgeddes Jan 24 '13 at 17:39
  • ? linq-to-sql is an ORM -- am I mistaken? – sgeddes Jan 24 '13 at 17:43
  • Pure SQL will always perform better than Linq2SQL since Linq2SQL has to generate SQL. But Linq2SQL is very convenient to use and performs very will under most circumstances. (I use it for most DB stuff) – Magnus Jan 24 '13 at 17:46
  • @sgeddes My apologies; I suppose by definition Linq-to-sql IS an ORM – David L Jan 24 '13 at 17:47
  • Pure SQL is what I was leaning toward, I just wanted to get other opinions for this specific implementation. Thanks all! – ToddBFisher Jan 24 '13 at 22:20

2 Answers2

1

Pure SQL should perform better than LinqToSQL (if anything because it doesn't need to construct and cache the query the first time), however, it depends on the number of users, the server capabilities, and how fast those "hundreds of times" happen if you should be concerned about it.

Jcl
  • 27,696
  • 5
  • 61
  • 92
1

This is a long article (5 parts) from 2008 that illustrates with proper tweaking, he was able to get Linq-to-sql to perform as well or nearly as well as straight ADO.NET sql calls with less time spent on building the queries. Hope it helps!

http://blogs.msdn.com/b/ricom/archive/2007/06/22/dlinq-linq-to-sql-performance-part-1.aspx

Here is another batch of testing that actually illustrates Linq as the winner in insert scenarios and has a slight edge when reading XML files.

http://www.codeproject.com/Articles/26431/Performance-Comparisons-LINQ-to-SQL-ADO-C#_Toc193731671

David L
  • 32,885
  • 8
  • 62
  • 93
  • `that actually illustrates Linq as the winner in the majority of scenarios`, actually, the article only marks it as winner on insert scenarios in regards of database access – Jcl Jan 24 '13 at 17:52
  • @Jcl. You are correct. I skimmed it a bit too quickly :). I've updated my answer to properly reflect the article – David L Jan 24 '13 at 17:53