Ado.net Performance tips and tricks by Don Keily

I attended a class by Don Keily. 

It was a good class, confirming a lot of what I felt to be true, reminding me of some things, as well as teaching me several things.  My notes are here: 03_adonetperformancetipstricks  but I put below what I got out of the session.

Ado.net performance tips and tricks by Don Kelly

 

·         From a performance standpoint linq to sql is evil

·         Don’t do data paging on the page. Do it on the server

·         Change the form of data rarely, if ever

·         Don’t let ado.net let you be lazy

·         Command builder causes several trips to the server

·         The more tiers the slower the performance

·         Avoid mapping object to tables and columns

·         Don’t build deep object hierarchy unless you need it (don’t create a customer object if all you need to do is modify their name)

·         Do check out performance tag on ado.net team blog (http://blogs.msdn.com/adonet/default.aspx  )

o   http://blogs.msdn.com/adonet/archive/2008/03/27/ado-net-entity-framework-performance-comparison.aspx

o   Shows you a chart…a breakout of where ado.net and the entity framework are different and the cost of the different calls

·         Do use data caching at every opportunity

o   Application

o   User specific

o   Keep track of update frequency and update caches differently based on need

o   Update it as close to the application as possible

o   Minimize transformations

o   Expire it sanely

·         Be aware of the cost of moving data between layers.  And know your options

o   Dataset

§  Lots of features and bloat

o   Typed dataset

§  More of a programmer convenience

o   Datareader

§  fast

o   Xml

§  Bloat.  Text and bloated.

§  Not the most performance way to move data

o   Custom class

§  Define own class carefully design the serialization so it goes into a small footprint.

§  You only want to serialize the dat athat is relevant

·         Reduce round trips (make batch sql statements with 2 statements in one)

·         Use connection pooling.  (no need to fine tune, just use it)

·         Don’t use builder objects

·         Set nocount on (stops dbs from sending the number of rows that were affected)

·         Close things and don’t wait for the garbage collector to do it (why? If you don’t garbage collector has to make two passes)

·         Use the USING keyword (using (sqlconn conn = new conn)………do stuff

·         Don’t have to explicitly OPEN or FILL data adaptor.  Just use da.fill(dataset) and you don’t have to open or close connection

·         Don’t have to explicitly open for update method

·         Let ADO manage connection pool (share connections, avoid per-user logons, don’t pass connections around)

·         Use paging liberally

·         Use executenonquery over executescalar (former doesn’t build a recordset)

·         For wide rows or blobs use commandbehavior.sequentialaccess.  It works like a datareader for columns (once you get the 5^th field you can’t get previous)

·         Never build a commandbuilder at runtime