Wednesday, 18 June 2014

Dapper : some minor but useful tweaks

At Stack Exchange we pay a lot of attention to our data access, which is why a few years ago we spawned dapper. In case you haven’t seen it, the purpose of dapper is to remove the mind-numbing and easy-to-get-wrong parts of ADO.NET, making it easy to do proper parameterization, without having the overhead of a micro-ORM. In other words, to make it easy to do this:
DateTime from = ...
int id = ...
int count = ...
var orders = connection.Query<Order>(
    @"select top (@count) * from Orders where CustomerId=@id
    and CreationDate >= @from and Status=@open",
    new { id, from, count, open=OrderStatus.Open }).ToList();

It is unapologetic about expecting you to know and “own” your SQL – entirely good things, IMO (by comparison: most professionals wouldn't program the web tier without knowing what their html looks like).

Inline value injection


With a few exceptions the sql that you pass in is essentially opaque: the only things it has done historically is:
  • check the sql to see which possible parameters can definitely be ignored, because they aren’t mentioned (assuming it isn’t a stored procedure, etc)
  • parameter expansion (typically used for in queries, i.e. where id in @ids, where @ids is something like an int[])
Sometimes, however, parameters bite back. There are times when you don’t really want something to be a parameter – it either changes the query so significantly that a separate query plan would be desirable. You don’t want to use a different command-string per value, as this would force dapper to perform meta-programming per value (since it would be a cache miss on the strategy cache).

Alternatively, often the value will never be changed (but is treated as a parameter for code reason), or will only ever be one value unless some configuration setting is changed, maybe twice a year. Classic values here might be a status code from an enum, or the number of rows (select top 50 sometimes performs quite differently to select top (@rows)).

Because of this, the current builds of dapper introduce value injection. This uses an intentionally different syntax similar to parameters, but injects the value directly into the SQL immediately before execution (but allowing dapper to re-use the strategy). It probably makes more sense with an example:
DateTime from = ...
int id = ...
int count = ...
var orders = connection.Query<Order>(
    @"select top {=count} * from Orders where CustomerId=@id
    and CreationDate >= @from and Status={=open}",
    new { id, from, count, open=OrderStatus.Open }).ToList();

Essentially, anything of the form {=name} will be treated as an inlined value, and will be replaced rather than treated as a parameter. Of course, at this point you’re probably screaming “nooooooo! what about sql injection!” – which is why it restricts usage to integer-based types (including enums). This significantly reduces the risk of any abuse, and of course: you don’t have to use it if you don’t want!

Parameter expansion support for OPTIMIZE FOR query hints


Parameter sniffing can be a real pain. For us, we call this “the Jon Skeet problem”: we have some very different users – some with maybe a handful posts and a few dozen reputation, and some with tens of thousands of posts and over a half-million reputation (which means: a lot of vote records). Let’s assume we want to keep the user-id as a regular SQL parameter: if the first use of a query is for “new user Fred”, the query plan won’t work well for Jon. If the first use of a query is for Jon, it won’t work well for “new user Fred”. Fortunately, SQL Server has a mechanism to tell it not to get too attached to a query-plan based on a parameter – the OPTIMIZE FOR query hint. This can be left open (applies to all parameters), but it is often desirable to use the variant that tells it exactly which parameters we are concerned about. This is easy normally, but recall that dapper offers parameter expansion. So how can we use this query hint with the in query above? We don't conveniently know the names of the eventual parameters...

Because of this, dapper now recognises this specific pattern, and performs parameter expansion compatible with this hint. If you use:
option (optimize for (@ids unknown))

it will expand this out to the correct query hint for the parameters that @ids become during expansion.

Async all the things


The usage of async keeps growing, so dapper has now evolved much better async methods (with a number of useful contributions from users); most of these are self explanatory – simply using the *Async method names, but some key additions:
  • Support for cancellation tokens
  • Opt-in use of pipelining (performing a range of operations on a connection without waiting for the early operations to complete – this requires MARS to be enabled)

Summary

No huge changes, but hopefully useful to a few people. Enjoy.

4 comments:

Tom Robinson said...

Thanks Marc, will definitely check out the async improvements.

code xbox live gold gratuit said...

Get your free codes of xbox live game here on this site and make your payable game absolutely free Thank you code xbox live gold gratuit

Fangyaya said...

louis vuitton borse
san antonio spurs jerseys
asics running shoes
jordan 3 retro
michael kors bags
coach factory outlet
adidas nmd
cheap pandora jewelry
adidas nmd
cheap oakley sunglasses
polo shirts
lebron 13 shoes
abercrombie outlet
uggs on sale
louboutin shoes
louis vuitton
seattle seahawks jerseys
mont blanc pen
ralph lauren
coach factory outlet
burberry sale
rolex watches
moncler uk
christian louboutin pas cher
cheap ray ban sunglasses
uggs for cheap
rolex watches for sale
christian louboutin sale
moncler outlet
coach outlet
juicy couture
cheap toms
uggs for men
louis vuitton bags
air jordan 8
ray bans
copy watches
polo ralph lauren
uggs on sale
oakley sunglasses
20168.22chenjinyan

Eric Yao said...

Coach Outlet http://www.coachstoreoutlet.us.org

Christian Louboutin Shoes http://www.christianlouboutinshoe.eu.com

Valentino Shoes http://www.valentinoshoes.eu.com

Michael Kors Outlet http://www.michaelkorsoutlet.us.org

Coach Factory Outlet http://www.coachfactoryoutlet.us.org

Coach Outlet Online http://www.coachoutletonlinestore.us.org

Coach Purses http://www.coachpurse.us.com

Kate Spade Outlet http://www.katespadefactoryoutlet.us.com

Toms Shoes http://www.toms-shoes.name

Hermes Belts http://www.hermesbelts.us.org

Louis Vuitton http://www.louisvuitton-outletstores.us.com

Fendi Handbags http://www.fendibelt.us.com

Giuseppe Shoes http://www.giuseppezanotti.eu.com

Michael Kors Outlet http://www.michael-kors-outlet.us.com

Stephen Curry Shoes http://www.stephencurryshoes.us.com

Salomon Shoes http://www.salomonshoes.eu.com

North Face Outlet http://www.northfacesoutlet.in.net

Coach Outlet http://www.outletcoach.in.net

North Face Outlet http://www.northfacesoutlet.us.com

Burberry Outlet http://www.burberryoutletstore.us.org

North Face Outlet http://www.north-faceoutlets.in.net

North Face Jackets http://www.outletnorthface.in.net

Skechers Shoes http://www.skechersshoes.us.com

Toms Outlethttp://www.tomsoutletstore.us.org

North Face Outlet http://www.northfacestoreoutlet.us.org

Nike Air Max http://www.nikeoutlet.us.org

Nike Hoodies http://www.nikehoodies.us.com

Marc Jacobs Handbags http://www.marcjacobshandbags.name

Marc Jacobs Outlet http://www.marcjacobsoutletstore.us.com

Jimmy Choo Shoes http://www.jimmy-chooshoes.us.com

Jimmy Choos http://www.jimmychoos.in.net

Burberry Belt http://www.burberrybelt.us.com

Louis Vuitton Belt http://www.louisvuitton-belt.com

Salvatore Ferragamo http://www.ferragamo-belt.us.com

Marc Jacobs Handbags http://marcjacobshandbagsoutlet.blog.com

Lululemon Outlet http://www.lululemonsoutlet.us.com

True Religion Outlet http://www.truereligion-outlets.in.net

Tommy Hilfiger http://www.tommyhilfigeroutlet.us.com

Michael Kors Outlet http://www.outletmichaelkors.eu.com

Coach Outlet http://www.outletcoach.eu.com

Red Bottoms http://www.redbottom.in.net

Kevin Durant Shoes http://www.kevindurantshoes.us.org

New Balance Outlet http://www.newbalanceoutlet.in.net

Adidas Outlet http://www.adidasoutlet.in.net

Coach Outlet Online http://www.coachoutlet-online.eu.com

Stephen Curry Jersey http://www.stephencurryjersey.us.com

Vans Outlet http://www.vansoutlet.us.com

Ralph Lauren Outlet http://www.ralphlauren-outletstore.in.net

True Religion Outlet http://www.true-religionoutlets.in.net

ED Hardy Outlet http://www.edhardyoutlet.in.net

North Face Outlet http://www.northfaceoutlet.eu.com

UGG Outlet http://www.uggoutlet.us.org

UGG Outlet http://www.outletugg.in.net

North Face Outlet http://www.northface--outlet.us.com

Ugg Boots Sale http://www.uggbootssale.in.net

UGGS For Women http://www.uggsforwomen.us.com

Skechers Go Walk http://www.skechersgowalk.us.com

Adidas Yeezy Boost http://www.adidasyeezyboost.in.net

Adidas Yeezy http://www.adidasyeezy.us.com

Adidas NMD http://www.adidas-nmd.us.org

Coach Outlet http://www.coachoutletinc.us.com