Artful Champions

There's an old saying, "every social war is a battle between the very few on both sides who care and who fire their shots across a crowd of spectators." Analysis is very much like that. You have perhaps two or three managers who have different opinions about what your objectives should be, and a couple large groups of customers who care in the collective sense, but who individually just view your project with the valid skepticism of the small part it plays in their lives.

To really nail down the ethos, and for a large project to succeed, you need to have (or manufacture) a champion for each of the user, customer, and business interest groups. These champions needs to sincerely stand up for the benefits and beliefs of their group, and be gregarious enough to express them.

As you cycle through the balance points you then build up goodwill amongst the champions, and this enables you to negotiate concordance on the more difficult decisions.

This can be an especially hard sell: first, when a champion is less than sincere in standing up for their group. Secondly, when a champion changes employment, through their own choice or otherwise. And thirdly, when you can't get sufficient permanent heavy artillery support from a senior manager to resolve those instances where negotiations fail.

A solid 25% of full-metal Analysis involves the psychic steps to avoid these three pitfalls.

Artful Coworkers

Employees may spend a fair amount of effort (especially in larger corporations where you find a concentration of regulatory specialists) mining the veins of gold sequestered away between the seams of operations and regulations. Sometimes this is explicitly an objective of management, at other times it is an implicit cultural undercurrent.

How you approach and handle this probably is more influenced by your ethos and personal beliefs than any of your other working relationships.

Society's regulations have both a written and an intentional form; the two are similar but not the same. To me, the intent of the law carries more weight than the letter of the law. You or your coworkers may disagree with the intent of the law, but unless you have a very clear and deep understanding of how the law came about you run a serious risk if violating its intent.

Resolving differences of opinion on this matter should be one of the first things you do before starting any large project that has a potential to move any boundary that affects this balance.

A full 65 to 75 percent of the politics of analysis happens on the boundary between employees and customers. Their interests are misaligned in any business because the distribution of profit margins either goes to one (in the form of wages) or to the other (in the form of competitive pricing). And you are developing a project that will impact sales or profit margin, because otherwise you wouldn't be working on it.

How this boundary moves however is none of your business, it is beyond your concern. Yet at the same time your analysis will sink into the mucky depths of oblivion unless you manage the politics of this boundary to appeal to both sides.

To the employees you must present an image that your creation will make their life easier without jeopardizing their jobs. To the customers (vis a vis the management) you need to present an end point that affords them greater efficiencies and value for their dollar. You may be unable to guarantee that you can sufficiently meet both targets, but it should be your objective nevertheless.

Artful Query

Every so often I get called upon to troubleshoot the performance of some SQL queries (that I didn't design). Frankly I view this activity as both a waste of time and an indication of severe design failure. Yes it /is/ possible to optimize queries post hoc, and if you must work your way through this task I will give you some guidance shortly. It certainly isn't, however the optimal way to get work done.

First let me explain why this is a design failure. A critical task during development is building a test environment mocked up to full scale. If you fail to do this then you won't fully appreciate how to best design your queries to begin with.

Databases are all about reading data from disk. Yeah sure there is some writing too, but this is typically dwarfed a couple orders of magnitude by the task of reading the data. Databases are heavily IO bound.

Two things contribute to the time it takes to read table data or index pages: the "width" of each row and the quantity of rows. The width of a row is determined both by the number of columns as well as how much data each field contains.

Do you want fast reads? Use narrow tables and indexes (or use vertical partitioning). To limit the quantity of row reads, use horizontal partitioning or appropriate cluster indexing on tables with millions of rows. And guess what, you can't choose the best partitioning approach during development until you scale up.

Understand the above and you will have good baseline strategies for designing queries in the first place. Hence given the chance to "optimize" existing queries, a far better approach (if you have the time) is to refactor the application to take advantage of this knowledge.

Persist sub-queries to narrow temporary work tables. Target your query sequence to limit the selection criteria to the typically minimal rowset before finally joining in the "wide" information.

If you can't afford a full refactoring though, then you will have to resort to basic database query optimization techniques. Learn to understand the "explain plan" and to identify the time resource hogs. Use nolock hints where possible. Experiment with other hints to modify join order. Direct reads by a key are fast, full scans are slow, so build indices on columns used in joins.

Yeah you can soup up your Toyota Corolla queries to run at 160 miles per hour, but it would have been far better to design your queries like a Ferrari from the start.