Powershell and DLL VersionInfo

This will be a quick post, but will hopefully help someone.

Finding a list of DLL files in the current directory along with their versions.

1. The long way:

Get-ChildItem *.dll | Select-Object -ExpandProperty VersionInfo | Select-Object ProductName,FileVersion

2. The short way:

ls *.dll | select -Exp VersionInfo | select ProductName, FileVersion

That's it! Aliases make Powershell easier to use. Just make sure you know how to compose the long way as well. Gotta know where you came from to know where you're going, I always say.

Adam Krieger

Applied Postconventional Creativity and Deconstructed Stored Procedures

Prerequisite to this article is a great view of creativity, created by Arthur J. Cropley and presented by Jurgen Appelo, available here: http://www.noop.nl/2009/09/the-three-phases-of-creativity.html

As with most perspectives I read about, I look for ways to apply it effortlessly. In this case, such an opportunity was available the very next day. I was having an issue with an SQL stored procedure that was pulling too much data and crashing tempDB. Considering the entire database was 9GB, and I had temporarily inflated tempDB's max to a ludicrous 23GB, there was definitely an opportunity for refactoring. It also bears mention that it would take almost two hours to fail.

I've seen all too many of these sprocs during my career:

SELECT id, name, locationName, accountNumber, etc...
FROM companies
JOIN billingAccounts ON billingAccounts.compId = companies.Id
JOIN locations ON locations.compId = companies.Id
JOIN billingPeriods ON billingPeriods.Id = @selectedPeriodId
JOIN billingInvoices ON billingInvoices.PeriodId = billingPeriods.Id
JOIN billingItems ON billingItems.InvoiceId = billingInvoices.Id
WHERE
billingItems.Valid = 1 AND
locations.StillInOperation = 'Yeap' AND
billingInvoices.SignedOffBySomeone = 'CheckWithDave'

This style of sproc writing is hard to read, confusing, not maintainable, and doesn't easily explain the domain or model. SQL comes across as very un-structured in this form. There are some options for diagnosis right off the bat. First of all, you can get SQL Management studio to return the execution plan and look through it for table scans and opportunities to add clustered indexes. These are great options to help you figure out what's going on, but they're not my subject, so I'm going to blatantly ignore them and move on!

My colleague told me an anecdote about a way he had seen complex sprocs rewritten on a past project. The individual doing the rewriting wasn't incredibly familiar with optimal join patterns, and instead decided to break down massive data pulls into step-by-step temp table update statements. The pattern was so non-standard that I immediately associated it with pre conventional creativity. In my experience, there is a widespread common understanding that big data selects mean big swampy JOINs and stacks of WHERE constraints, but it seems that one man didn't have that wall of preconception holding them back.

This has everything I love in a good work story: a guy who knows a guy, applications of cognitive theory, and a new way of using a tool that's already in my tool belt.

The pattern goes like this:
1. Your first select is your 'one-per-row' select. If you're looking for billing items, don't start with the companies table. Start with your subject.
2. Select your 'one-per-row' results into a #temp table. Grab any foreign keys that you'll need for joins. You may join one and only one other table in this initial select.
3. All other columns that you'll want from other tables are casted nulls. These will be filled in later by subsequent update statements that, in turn, join one table at a time and 'fill in the blanks'.
4. Make use of WHERE NOT foo IS NULL when appropriate to keep from blanket-updating your temp table.

My made-up report sproc from earlier would look like the following when refactored:

--Initial Select
SELECT
-- FKs
items.Id AS ItemId,
invoices.InvoiceId AS InvoiceId,
invoices.LocationId,
CAST(NULL AS BIGINT) AS CompanyId,
--Output fields
invoices.InvoiceNumber AS InvoiceNumber,
items.Quantity,
items.TotalPrice,
invoices.InvoiceDate,
CAST(NULL AS NVARCHAR(100)) AS BillingAccountType,
CAST(NULL AS NVARCHAR(100)) AS CompanyName,
CAST(NULL AS NVARCHAR(100)) AS LocationName,
CAST(NULL AS NVARCHAR(150)) AS LocationAddress,
etc...
INTO #BillingItemReport
FROM
BillingItems items
LEFT JOIN
BillingInvoices invoices ON items.InvoiceId = invoices.Id
WHERE
invoices.InvoiceDate = @invoiceDate

UPDATE
report
SET
CompanyId = locs.CompanyId,
LocationName = locs.LocationName,
LocationAddress = locs.BillingAddress,
FROM #BillingItemReport report
LEFT JOIN Locations locs ON report.LocationId = locs.Id

UPDATE
report
SET
LocationName = translations.LocationName
FROM #BillingItemReport report
LEFT JOIN LocationTranslations translations ON translations.LocationId = report.LocationId
WHERE NOT translations.LocationId IS NULL -- In case the translation doesn't exist

Etcetera, until you've filled in all of the casted null columns and have your whole report.

Have you ever gone out to dine at a cloth-napkin-type establishment and were offered a 'deconstructed' salad? It's quite possible to see the ingredients of the salad by looking at a tossed version, but you may have to look under a few leaves, and you probably won't be able to figure out exactly how much of each component was used. A deconstructed salad lays it all out on the plate so you can see exactly how much lettuce and dressing you're getting for sixteen dollars. This is also one of the many instances in life that you'll end up paying someone to not perform a service.

Deconstructed Stored Procedures have some of the same outcomes. The model is clear as day. The JOIN order and progression is dead simple because we're only allowed to lace in one table at a time. The casted nulls remind us of our final select and prevent mid-select CASE statements that can further muddy up the works. Last and most certainly not least, it's extremely fast because we're only ever pulling a tight stream of explicitly defined data from the base.

I hope that you've learned something. Thanks for reading.

Adam Krieger

A Better Opposite to Irreplaceability

I awoke from an afternoon nap this weekend, and I immediately started to think about irreplaceability. That which comes from a professional, commonly a very technical one (let's call him Frank), who produces value in such a way that the maintenance or longevity of that value relies on the maintenance and longevity of that individual's employment. The tools in his toolbox are convolution, knowledge hoarding, and condescension.

In a hypothetical software company, Frank designs the delivery method for the software. It's got all of the bells and whistles, but he's the only one who knows how to use or make changes to it. Other developers have looked at the code, sure, but it's written in a way that feels intentionally deceptive. He used non-standard libraries, implemented a personal naming standard, put the source in some far-off branch (possibly even under a different source control solution), and didn't comment a thing. It even looks like his code is missing a piece, and when prompted, it turns out it was sitting in a directory on his workstation.

"Hey, Frank, could you show me how your IDeliverStuff implementation works?" you ask, but you already know that Frank is too busy to help. He's got a mile-long list of tasks which mainly consist of maintaining his other projects that no one else knows how to use. He's isolated himself within a bubble of propriety, and you wonder if his documentation ever existed in any form.

You ask anyways, though, as you want to know. Frank responds by making you feel like you're wasting his time. He never makes eye contact, he postures away from you, and he changes the subject to a personal project.

If Frank were a machine part, he'd be a completely non-standard, expensive, over-engineered whatchamacallit, that takes 16-18 weeks to fly in express from Baden-Württemberg. He is irreplaceable. He makes well over six figs, though, so he must be doing something right, right?

I conject that, for all the bleeding-edge solutions that Frank has put in place over the years, Frank is doing more harm than good. I suppose that this is not a very difficult opinion to gain support for, but what is the alternative? Replaceability? That hardly sounds good. Frank was way too selfish, but does that mean I should invite the possibility of overlooking me for a promotion? Should I welcome the opportunity to downsize me?

A better opposite to Frank is embodied by someone whom is replaceable, but also essential, and infuential. Frank's replacement, so to speak, builds up those around him. His documentation is thorough. Everyone knows the state and course of his projects. He is the shining gear at the heart of the machine. Every machine needs one.  He seems to make the rest of the machine work cleaner and with less wear.

What's stopping the management from taking that Shining Gear and removing it from the machine? Nothing. When it's time to start a new machine, or revive a machine that's breaking down, Shining Gear is the first they turn to.

Irreplaceability is a cliff. It's built solidly, but falling down is a shattering experience for everyone involved. Past that point is replaceability, which instincts tell us is a bad place to be. Somewhere even further is the state where Shining Gear lives: Interchangeability.

Possibly not the best opposite to irreplaceability, but definitely better.

Adam Krieger