CODEWITHSUNDEEP
.netsqlsql-serverperformance
Vaccano
Vaccano

Reputation: 82517

Most performant way to write, read and delete (not update) data to a SQL Server database in .net

I need to insert rows rows into a database, then in another thread read those rows and then (in a separate call delete them).

Normally I would just break out Entity Framework for this. But I need this to be fast. Really fast.

A row would be an bigint, bigint and a varchar(max).

Is there a faster way that Entity Framework? If so, what is it?

(I am going against SQL Server 2008 R2)

Upvotes: 2

Views: 247

Answers (2)

driis
driis

Reputation: 164341

Raw ADO.NET is the fastest, but I would choose Dapper as a micro-ORM. It is blazingly fast, and helps you a lot in the development.

However, it is important to know that the primary reason why Dapper or similar would be the fastest, is primarily because of your control, you can carefully craft the best performing SQL queries.

I would not bother to think much about the data access technology, other than you need something, that brings you close to the actual SQL and don't enforce too many abstractions upon you. Use your time to think about the database, indexes layout, transaction processing and query optimization. That is where you will find the primary performance gains.

Upvotes: 5

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

There is some good info on the Dapper ORM page about this.

See section Performance of SELECT mapping over 500 iterations - POCO serialization and below. It only discusses SELECTs, but you can extrapolate from this to a certain degree.

For example, you can see that Entity Framework is pretty much the worst way to go, in terms of query speed.

Method                             Duration    Remarks
Hand coded (using a SqlDataReader) 47ms
Dapper ExecuteMapperQuery<Post>    49ms
ServiceStack.OrmLite (QueryById)   50ms
PetaPoco                           52ms        Can be faster
BLToolkit                          80ms
SubSonic CodingHorror              107ms
NHibernate SQL                     104ms
Linq 2 SQL ExecuteQuery            181ms
Entity framework ExecuteStoreQuery 631ms

Upvotes: 5

Related Questions