I love hearing stories of how things came to be in computing. One of my favorite stories is about date in Microsoft Excel. I stumbled across this story while writing some spreadsheet import code.

The first thing you need to know is that Excel stores date as a number of days since January 0^th 1900. So a date with a value of 1 is January 1^st 1900. You can see this by selecting a date field and changing the format to general. You’ll get a number like 41408 which is the 14^th of May 2013. So that’s pretty simple except for leap years. As we all know in a leap year (every 4 years) there is an extra day in the year. However just adding a day every 4 yearsisn’tquite right. Every 100 years we skip a leap year. Every 400 years we have an extra leap year. That’s why the year 2000 was a leap year even though it is divisible by 100 (because it is also divisible by 400). Keep these leap year rules in mind, we’re coming back to them later.

Back in the mid-1980s a little company named Microsoft was trying to build a new spreadsheet program. Spreadsheets were the killer applications of the 1980s, heck they probably still are the most used applications on business computers. VisiCalc was the first consumer spreadsheet and its influence can be seen to this day. The A1 style of addressing cells waspopularizedby VisiCalc, although you can probably trace the origins further back than that. VisiCalc was released in 1979 but by 1983 it had beensupplantedby the vastly superior Lotus 1-2-3. Microsoft had already tried to compete with VisiCalc and Lotus 1-2-3 with a program called Multiplan. It did not go well.

Lotus 1-2-3. I totally remember this.

To be competitive with Lotus 1-2-3 Microsoft needed something new. Excel was the answer to this. To compete with the dominant player, Lotus,they needed to be able to open up Lotus 1-2-3 files, edit them and save them back again as Lotus 1-2-3 files.One of the problems they encountered enabling that was that there was a bug in Lotus 1-2-3 in how it handled dates. Lotus 1-2-3 thought that the year 1900 was a leap year so it had 366 days in that year. To maintain compatibility Microsoft included that bug in Excel. Even to this day you’ll find you can enter February the 29^th 1900 despite the fact that day never existed.

The bug in Lotus 1-2-3 has survived long after the death of Lotus. It’s been 30 years but we still see the influence of one minor mistake. Amazing.

All this means that when you’re converting an excel date to another date format you actually have to take the number of days Excel tells you, subtract 2 (one for the leap year and one for the fact there is no 0^th of January) then add that to 1900-01-01.

A few months back I bought a new car from Ford and it came with the Ford Sync system. For theuninitiatedthis system is evolution of the car radio combined with climate control, navigation and a phone interface. I took a look at a lot of systems when I bought this car because, from my perspective, the technology stuff in the car is the most important part. Years ago the quality of cars from different manufacturers was highly variable but these days they all have wheels, they all have gas tanks which don’t explode when you crash them. Of the systems I examined Sync was head and shoulders above the rest.

What do you get with Sync? First off you get navigation. There is nothing too special about the navigation system. I know it’s crazy to say that considering how amazing it is to have an encyclopedia of maps and locations at your fingertips in a vehicle. However times are a changing and GPS is common place even on $300 phones. On an expensive car I expect no less.

The entertainment system is really good and the one place where I feel like the voice control works well. My version of sync allows for the regular radio frequencies as well bluetooth and USB. And it actually works! Even with my crummy Windows phone which I’ve never managed to get working on another car. I can even tune the radio to a frequency just by talking to it.

Climate control is a total disaster of user interface design. Look at all these buttons!

Climate control disaster

Still it works just fine and I’m pretty sure that there is a button in there somewhere to turn the inside of the car into one of those hurricane booths you see in the mall. I’m going to find it eventually.

The phone interface is the best part though. I mentioned earlier that it works with my phone and in fact it even reads text messages to me. That’s impressive because it doesn’t do that with my wife’s iPhone. I’ve never looked into it but I imagine there are a series of bluetooth protocols which govern how phones and cars interact. Seems strange that there would be a defect which would mean that the iPhone doesn’t work as well as the WP7 phone.

So for all this is the sync system good? Yes! Is the system amazing? Nope. There is a long way to go with these systems. What kills me is that I’m going to keep this car for a decade by which time the built in system will be so comically out of date I’ll be able to sell the interface as being the same one used on the Nina and the Pinta(not on the Santa Maria, though, that ship used PalmOS).

This sort of thing is difficult for car companies. The steering wheel in a 1982 Volvo is about the same as the steering wheel in a 2013 Volvo. Nobody is coming back to Volvo and asking for a steering wheel update. They have no experience with the rapid product cycles of technology.

If I was designing a car computer I would make it modular andplug-able. Heck, I would make it a tablet. So long as there was a consistent interface to the car (USB) then the human interface is pluggable. Why is nobody doing that? There must be smart technical people inside car companies who have come up with this approach. I wish that somebody would let them free to innovate. It had better be done before all the smart folks move over to Tesla.

When I was a teenager I worked for one week at a local supermarket as a cashier. I only lasted a week because I found a “better” job at Tim Hortons. As it turns out it was probably a far worse job but it did have the advantage that when you worked the closing shift all the donuts would have to be thrown out. From time to time a clean garbage bag full of donuts would make its way into somebody’s car instead of the dumpster. Anyway this article isn’t about how that is a greatmetaphorfor illegally downloading music, its about incorrect metrics.

The supermarket, called Superstore, had a goal of getting people through the checkout as quickly as possible. This was anoblegoal as nobody, except the English, likes spending time in a queue. They had identified that a checkout consisted of a few steps: unloading the cart, ringing the items up, paying, and packing. As this was a discount store the customer unloaded their own cart and packed their own groceries. The customer was typically the limiting factor on the speed of paying for the groceries too. This only left ringing the items up as an optimization point over which they had control.

At the end of every shift we would be given a print out of the number of items a minute we were scanning. The goal was to spend no more than 2 second scanning an item so that you would end up with 30 items/minute as your average. These numbers were all rolled up and the store would report back to corporate with an average number. 30 items/minute seems pretty low for just passing a barcoded thing in front of the scanner. I had forgotten all about this metric until I was in a Superstore last week and they had left out a graph with the averages for that store.

22.45 items/minute.

I was greatly amused to see that the metric was still in force and even more amused to see the number was so low. See in my one week at the job I had averaged well over 30 items/minute, I think it was closer to 40 items/minute. It wasn’t because I was a cashier savant or because I entered into atranscendentstate and became a scanning machine. It was because I knew how to game the system and break the metrics.

First I took advantage of the multiple items button. If you buy 10 tins of cat food the cashier is suppose to hit 10 QTY and then scan the item. This is a quick way to ring in many items and is designed to get people out of the store quicker: a great idea. However in the metrics it was only counted as one scan. If, instead, you scanned each item individually it would count as 10 items. I would scan the same item 10 times in rapid succession. In this way I could get 10 scans in about 4 seconds.

The second trick was the Total button. This is the button which is pushed at the end of the transaction and denotes moving from the ringing in phase to the payment phase. Because we cashiers couldn’t control the length of time a customer would take to pay us we weren’t measured on this phase. However you could get out of the payment mode and back into the ringing in mode by just pressing Exit. I took advantage of this by hitting the Total button any time I had an item with a code on it that I had to look up or a stack of items to move from one side of me to the other. It acted as a sort of pause button.

Any scanning averages they got from me were just about useless. What’s worse is that I was not at alloptimizingfor customer experience, I was optimizing for the benchmark.

Any time you set up key performance indicators(KPIs) in a business you have to be aware that in so doing you might not beoptimizing for the things you want. You may very well be teaching people to optimize for your KPI. If your KPI is not very well thought out then the result could be a less efficient businessprocess. I’m not saying don’t measure, measurement is key. What I’m saying is continually examine not just the results of your measurements but also the process you use to take measurements.

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.