One of the things I see quite frequently in business is that new,supposedlyoptimal processes are introduced without a full examination of the cost. It is a difficult problem because figuring out the full cost is shockingly difficult. One of the big culprits is offloading work to the crowd.

Twenty years ago nobody filled out their own expense reports. There was some person who was expert at filling out expense reports and you took that person a bunch ofreceiptsand they dealt with it. Magically a few days later your expenses were paid. Those days are gone, instead you file your own expense report through some piece of software.

I am totally incapable of correctly filling in an expense report correctly. Last time I did one it took five tries to get the report right. I’m kind of dumb but I bet that most of the expense reports which go into that system are rejected the first time. But because the system is in place there is no need to employ that expense report specialist who existed 20 years ago. Cost savings! Right? Probably not because most of the people who are now filling in expense reports are earning more than that specialist, they’re taking more time to do the reports and they’re having to redo the reports.

Another great example somebody told me about was adding a card scanner to the office printer. The goal is to prevent people printing things and then leaving them on the printer. Apparently this is a huge cost. Millions of dollars are being wasted byunnecessaryprinting. Now everybody uses their keycard to activate the printer. On the surface this is a great idea but there are hidden costs. People feel like they’re being spied upon, people have to remember to bring their swipe cards to the printer and people can no longer send print jobs to remote printers for somebody else to pick up. There are hidden costs here too.

These are just a coupple example, I see lots of others. Heck, I write lots of others. If you’ve ever seen an initiative with the words “self-serve” in their name then you’ve got a crowd sourcing initiative. It can be great to empower people to solve their own problems through a self-service model but keep in mind that there is a cost. Sure there is no longer a line item in the accounting department’s budget for an expense expert but now that cost is spread out all over thecompany If you get enough of these initiatives then they start to detract from the time people spend on the core functions of the business. Now the cost is not only that you’re paying an expensive engineer to do expenses but that engineer no longer has time to do engineering which is where you earn your money.

Watch out for those hidden expenses!

Visiting this site collects no information about you other than your geographic location. We use this to plot out your location on a map, because that’s kind of cool to see.

When you comment on our site we collect your name and e-mail address. You name we show on the site and the e-mail address we retain. We’ll never send you an e-mail or give you information to anybody, ever. Don’t even worry about it.

If you’re worried drop me a line

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Concern’ type=’textarea’ required=’1’/][/contact-form]

If you’re running an Access front end to a SQL database then there are frequently issues with table locking. From what I can tell when Access encounters a large table or view which is being used to populate a grid it will issue a query to retrieve the entire dataset using a cursor. It will then move the cursor along just far enough to see a bunch of records and hold the cursor open. This has the effect of locking a significant part of one or multiple tables. There are solutions and this blog post will take a look at a couple of them.

The first thing to do is try to identify the query which is causing the problem. In my case I found a reproducible test case in which an update query timed out. In a multi-user system this is the hardest part. I was aware the bug existed but with 40+ people in the database the problem only seemed to show up while I was at lunch and by the time I was back had cleared up. I finally narrowed it down to one screen and to verify it I ran

select cmd,* from sys.sysprocesses where blocked > 0

This query finds all the executing processes on the SQL server which are blocked. The contents of the blocked field is actually the Id of the session which is preventing the query from going through. With that information in hand you can run

with the session Id from the previous query. This will get you the currently running query which is blocking your update. Great, now you have a test case! If you want to know who is running the query you can look up the session Id against the output of

sp_who

But it doesn’t really matter which user it is. Now to fix it.

Fixing with Access

The problem we’re having is that Access, in the interests of showing data quickly, is not pulling the entire data set. You can hook into the form open and have it jump to the end of the result set to have it pull all the records and close the cursor. In the subform with the locking query add this to the form load:

Fixing with SQL Server

The Access based solution isn’t a great one because it can force Access to pull back an entire, huge, result set. This isn’t performant. Okay, Access as a whole isn’t performant but this is particularly bad. Instead we make sure that our form is based on a view instead of a table. If you’ve done anything with Access and SQL Server you’re probably already doing this because of the crippling performance issue with crossing tables retrieved from SQL server. To get rid of the locking contention you can use the table hint with(NOLOCK) in your view definition.

If your view looks like, say

and you’re running into locking issues on tblTags you can change it to look like

There is nothing stopping you from putting NOLOCK on every table in the view but it will increase the frequency of running into inconsistent behaviour. And you will run into inconsistent behaviour. NOLOCK is a terrible idea in general as it changes the isolation level to READ UNCOMMITTED. It is much better to not use cursors or sort out your transactions but such options are not open to us when using Access as it is a black box over which we have no control. There is plenty writtenout there about why NOLOCK is dangerous but it is the best of several bad options.

The real solution is to stop using Access. Please. Just stop.

Final talk dump from PrDC13! This puppy is about SQL indexes with the always scary smart Michael DeFehr. I’m so terrible at

• SQL server uses 8K pages which are organized in a tree structure
• By the time we get to 3 levels deep there are enough pages that almost all tables fit in that space
• Each level of the index is also a doubly-linked list which allows transversal toneighbourpages
• An index is made up of 3 groups of columns: - key column
• columns in tree
• columns in leaf
• nonclustered index has the clustering key in the leaf and will have it in the tree if not a unique index
• This will give detailed information about the breakdown of the index statistics, numbers of levels and the such

select * from sys.dm_db_index_physical_stats(DB_id(), Object_id(‘tablename’), 1,null,’DETAILED’) ddips

• You can set statistics on by setting

SET STATISTICS IO ON

• Index seek is just a traversal though the index using a binary search
• If the data in an index is properly sorted the you can pull out chunks without having to worry about running a sort operation. This makes the fields indexed super important
• Using keyboard options in the management studio allows you to insert the highlighted portion as an option to some code so you can put “select from” in the shortcuts then you can highlight a table name in the editor and hit the shortcut to select from that table
• Download and use this stored proc for analysing the indexes
• Indexes are always unique, even when they aren’t. Behind the scenes the index will add the primary key into the tree to ensure uniqueness if you don’tspecifyunique
• Included columns in an index include the value from that column in the index page. So if your index is on FishId and you include FishName then when you query a non-clusteredindex for just FishId and FishName then there is no reason to hit the table itself. As soon as you ask for something more than that you have to go to the table itself which involves a seek and then a lookup.
• You typically don’t need all the columns in a table. Using projections is a good improvement.
• Filtered indexes allow for indexing only part of a table. Say you have an append only table and you only want to query on the last day of data then you can restrict the index to only contain rows which match some criteria. This can be set up by just adding a where clause

create index blah on table(column) where active = 1

• There is now support for instantly adding columns with default values through the use of sparse values. Cool.
• Index views have been around for a while and are designed to speed up aggregate queries
• Creating a view with the keyword schemabinding will prevent changing the underlying tables to invalidate the view

This is another brain dump of notes fromPrairieDev Con. this talk is Mark Stafford’s OData talk

• REST isinsufficientlydescriptive for many applications
• There a number of different options to create OData, even 2 on the Microsoft stack. WCF data services or ASP.net MVC WebAPI.
• OData supports more complicated queries than REST by itself. While REST can be used to do more complicated queries it is not designed to do so. As a result there is a lack of consistency in how people implement it.
• The WebAPI method is based on an MVC project. It basically just exposes an IQueryable to the web
• To build the controller you can extend from the EntitySetController<EntityType, EntityKeyType>. From there you just need to override the various methods

• You still need to specify the individual end points
• You can specify the format returned using either the URL or using accept headers
• While the underlying OData library is mature WebAPI isn’t. If there is something which is not implemented yet then you can implement a method called HandleUnmappedRequest
• There is support for retrieving data directly into Excel
• There is some really snazzy ability to do geospacial queries
• There is no real need to have your OData end points map directly to entities in the data model. They can easily map to projections so long as you can provide an IQueryable wrapper for the projection

Thoughts:

Yeah, REST is not great for doing querying. The excel retrieval is brilliant. Also being able to easily dig into queries just using a browser is great. I have some concerns that allowing querying against the database like this opens up the possibility of allowing users to run super inefficient queries and bring your system down. Users can’t be expected to know what queries are going to do that so some sort of throttling or caching would be useful.

This is another brain dump of content from PrDC13. This is David Mosher“˜sexcellentAngular JS talk. Don’t worry it is late on Tuesday when I’m writing this and the conference is almost over. Only a couple more days and we’ll be back to more sensible posts. Likely I’ll base some of my upcoming posts on stuff from this conference on which I would like to expand.

• AngularJS is a framework for building applications on the web. They don’t have to be single page but you can certainly use id
• When creating an angular application you start by annotating the html element with ng-app=”someappname”
• the angualr configuration works by doing angular.module(“someappname”, []);
• Angular templates are just html files
• You can put in the ng-view attribute on a div which is the container into
• Templates are cached automatically
• The module is set up and looks like

/4daa347c7108c76faf51e287716954b1bf94be8b

• Model binding is super simple you just need to annotate fields with ng-model=”somemodel.somefield”
• Angular has an internal loop which synchronizes the model with the view. If you make changes outside of the normal way then you need to call $scope.$apply()

/33f9138d6e1d3d0fea466068eb199179c1765cdf

• Functionality can be extracted into services
• There is an issue with minification which can break the dependency injection for Angular. You can either change your functions to be an array listing the dependencies or use ngminto preprocess your code

This is a brain dump of a session given by cat lover Eden Rohatensky.

• CSS is limited because it is designed to be simple
• Simplicity leads to limited expressibility
• CSS preprocessors allow for adding a programming paradigm over top of CSS
• Using a CSS preprocessor allows for writing more maintainable code
• SASS - SASS uses a Ruby preprocessor but you can survive without Ruby as SASS is basically just a DSL
• There are two different sytaxes for SASS: SCSS is close to the CSS syntax where the origianal SASS dialect is whitespace delimited

• Variables are denoted using a $ symbol

• Eden uses Crunch as a tool for SASS

• Supports lists which are a handy construct for creating elements which are similar
• Less - Less is written in JavaScript
• Less uses @ to denote a variable
• Eden uses Compass for LESS
• Both tools support nesting of classes so you can apply the same rules for a set of classes like a:hover and a:visited
• There are some libraries of mixins to deal with things like vendor prefixes. This allows cleaning up

-webki-border-radius: 5px; -moz-border-radius: 5px; -ms-border-radius:5px; -o-border-radius: 5px; border-radius: 5px;

• Lots of functions in both but you end up in
• luma is theperceivedlightness of a color
• The compilers for SASS and Less provide some logic checking to prevent you from writingsyntacticallyincorrect code
• The errors which come out of sass

Thoughts

SASS seems like a more powerful but slightly more complicated language. I wonder if source maps could be used to transition from CSS back to the source files. Gosh I hope there are already tools written to perform SASS/Less compilation as part of the VS build process and that I dont have to write it myself.

This is another brain dump from another session at PrairieDev Con. This session is by the really excellent and hilarious James Chambers.

• ASP.net MVC is built using the idea of convention over configuration.
• MVC is model view controller
• MVC and the tooling for it is now released out of band with Visual Studio
• MVC is built on top of ASP.net so the role providers from a WebFormsapplicationcan be plugged right in
• MVC is built on a bunch of nuget packages. If you have them already on your machine then there is no need to retrieve packages over the net
• There are a bunch of templates to choose from when you start a new asp.net mvc site - empty project is totally empty and almost always a bad idea
• basic at least adds some of the app_start stuff and is a good starting point
• Internet and intranet add authentication
• Authentication is now set up via the AuthConfig which is in App_Start. There is now out of the box support for OAuth
• Bundling reduces the number of requests to the server by smushing CSS and javascript together
• Routing allow for you to map urls to pages
• When creating a new controller it is possible to use scaffolding to create controllers which have some functionality built in
• The default built in data access stuff with the controller templates are built using EF
• You should refactor almost right away as the generated code has hard dependencies.
• The model binder, which can be overwritten, pulls information out of the request and binds it to a given model. It works on best effort so it is possible it won’t be able to bind some fields. In that case it just skips the field.

This is another dump of my notes from a talk at Prairie Dev Con. This is David Paquette’s Entity Framework Code First talk which is, oddly, subtitled Magic Unicorn. Very odd.

David is also a beautiful unicorn

- EF shipped with VS 2008 and was junk

• ORM is tough, please don’t write your own
• There are 3 flavours of EF model first, database first and code first.
• Model first using a visual designer to build up a model of the data. Kind of like LINQ
• Database first will generate you a model from an existing database
• Code first you create POCOs first and use conventions to figure out things like the key and foreign key references
• Conventions - virtual IList or IColleciton creates a foreign key to the collection of Somethings.
• virtual Something creates a one to one mapping from the current object to a Something
• any property called Id becomes a primary key
• EF will create a database using the name of the DbContext if there is no configured database
• Good idea to initialize collections in the constructor. Using a HashSet is more efficient than using a List because hash set optimizes for having no duplicates.
• A DbContext provides a mapping from the objects to the database. Each entity is just set up as an IDbSet in the DbContext.
• The LINQ queries return IQueryables which are a built up collection of lambads which are applied in a delayed or lazy fashion
• It is important to convert the IQuerable to an array or list before passing the data to the view as the DbContext may be out of scope before the view is rendered
• You should dispose your DbContext as that returns the connection to the pool. This is best done using a handy, dandy injection framework. In this case Ninject.
• To return a single object you might call _dbContext.Somethings.Find(id) you can also use Single or First which have slightly different failure criteria.
• To add an entity use an Add on the context and then call SaveChanges to synchronize the DbContext with the database
• Tidbit: There are no performance implications to using varchar(max) except that varchar(max) cannot be indexed so it can’t be easily searched.
• When editing an entity you need to mark the state as modified _dbContext.Somethings(entity).State = EntityState.Modified.
• You can specify a database initializer where you can have it delete and recreate a database or add default data
• You can profile your applications using MiniProfiler.MVC which is a lightweight profiler from Stackoverflow. To hook in EF include MiniProfiler.EF
• You can to eager loading by specifying .Include(“Something.Comments”) this would load the comments collection for Something
• Projections can be used to trim down what is returned form the database
• Paging is implemented using Skip and Take

Thoughts:

EF is a lot of magic but it seems like pretty well thought out and easily overridable magic.

I’m told that YUI is pronounced Y U I and not yoooii, which I feel is a realdisappointment. This post contains information gathered fromJeff Pihach’s YUI talk at Prairie Dev Con. It is basically going to be a stream ofconsciousness with little ordering. Sorry.

• YUI has quite a bit of support from various companies. Yahoo is, obviously, a big user. That yahoo uses a technology is not really a selling feature for me. They don’t even let people work from home.
• YUI loader does server sideconcatenationof modules to allow you to load dependencies into your YUI application. It is dependency aware so it will properly order your includes. There is support for minification and even for doing debug statement removal.
• Has support for a CSS selector based DOM selection and manipulation engine.

Y.one(‘.foo .bar’); //selects just one element which is returned as a Y.Node Y.all(‘.bazzs’);//returns a node list which is an array with some extra prototypes on it

• Y.Node supports such methods as

setStyle() addClass() hide() show() …

• There is binding for events which support binding before (using on), after (after) and instead of (delegate) events. Kind of handy.
• You can specify the context under which an event handler will run. Probably a bad idea.
• YUI is heavily modular so if you don’t need gesture support or simulate DOM events just don’t load those modules. Modules can be rolled up into “rollups”
• YUI has some ability to create modules and classes for you. They have an augment function which is about the same as the jQuery .extend method. The classes look to have the ability to bind to change events on properties. So basically every object is an observable.
• There is a plugin model which implements the mixin pattern. Is mixin a pattern? Maybe it is a language feature.
• YUI base provides a simple basic object and pretty much everything extends that
• Y.Widget createsreusableGUI objects
• Y.Array.each is a handy foreach thing for arrays.
• There appears to be an extension method which allows for overriding 3rd party controls through monkey patching
• Y.App is a rollup which provides for an MVC style framework. Has built in support for routing.
• ModelSyncRest is a framework which allows for making changes to a bunch of data using REST. It is very quick to set up CRUD sort of pages using a REST backend

Thoughts:

I’m not in love with having different selectors for a list of elements and a single node. That is one of the great selling features of JQuery. Having twothree different object extensibility methods is a bit complicated. I’ve never been convinced by mixins so I’m a bit one sided on that.

Why is enumerating an array so complicated, huh JavaScript? Old JavaScript engines are the bane of the Internet.

I think that YUI has some potential but that the bar to entry is pretty high. I’m not fully convinced that the advantages are so big that they cover the cost of dealing with the cost. I find myself still asking Y.Why? (I spent half the talk thinking up that joke)