A Manly Weekend

This weekend, instead of posting like I thought I would, I ended up performing a series of necessary tasks to improve my domestic situation.  First, I did my semi-annual toss-out expedition, eliminating roughly two bags worth of stuff.  Today, I fixed the lighting situation in my living room—it used to be pretty dark because of how spacious the room is—with a couple full spectrum lights.  They do seem to light up the room better than my previous lamps, though I will have to get used to the fluorescent-like lighting.  Unfortunately, those lamps are 4′ tall, so that wouldn’t do at all for me.  As a result, I ended up going and getting 5-gallon paint buckets to put them on.  The base fits almost perfectly on the bottom of these buckets, and I hid them behind a chair and a table, so you can’t see the redneck rigging job I did…

On top of that, I also improved[?] the bike basket situation.  I have a front basket that I’m using on the side, and I used velcro and weather stripping to make the fit there tighter.

Then, if that weren’t enough, I also have plans to shorten a couple of belts (because they’re a couple inches too big for me) and hang up a pair of portraits.  But right now, I’m relaxing with a franchise of Madden 10.  Tony had better be correct that this is a potato of games…

No Time…

Well, looks like I won’t get to finishing up the stuff I wanted to do.  I’m learning SSIS via a work example, so that’s my current homework.  In addition, I’m looking over a dissertation draft, meaning that what little time I had is gone as I race to get everything done before Shabbat, as I won’t have any time on Sunday…

There should be a couple of posts this weekend, though, as I still have enough of a guilt-based ethic to keep going every day (even though it’s been more sporadic lately).

Quick Notes

I’ve been a bit busy at work and haven’t had time to finish up my table-valued parameter posts, but hopefully that will happen tomorrow.  I’m also looking at an In The Papers to thin down my stack here.  In the meantime, a few notes…

- High-speed rail does not pass cost-benefit analysis. I’m shocked…  One day, people may finally realize that government planning simply doesn’t do a good job.  If people want to use a particular means of transportation, they will signal it by their willingness to pay, and profitable routes will, in a free market, draw entrepreneurs to satisfy this demand.  Having government throw hundreds of millions to billions of dollars into something that people don’t want is a waste of resources.

- John Stossel points out that light rail doesn’t do any better. Maybe someday I’ll figure out the left-wing fascination with rail…

- Banks are advertising that they don’t need bailed out. As noted previously, this was a) obviously going to happen, b) a normal market function, c) exactly the correct thing to do, and d) detrimental to government planning.  Again, one day, people may finally realize that government planning simply doesn’t do a good job.*

- Jeffrey Hummel thinks that the US government will default on its debt. Arnold Kling thinks that a wealth tax will occur by threat of military force.  I disagree—any politicians who tried that would find their campaigns DOA.  The amount of taxation necessary to pull the American government out of its debt would be high enough that most people would be affected (and politicians who propose tax hikes tend not to remain popular) and money would flow out of the politicians re-election funds and into the funds of their competitors.  Arnold also helpfully provides his priors.

- Let’s destroy property—it’s good for the economy! Excuse me a moment; I have to go fight off anarchist urges…

* – I may have to start using macros like TMQ…

Table-Valued Parameters

A work project I just picked up yesterday was trying out whether table-valued parameters would work for our current setup.  I was reading a few articles about them and was quite intrigued.  They are new in SQL Server 2008, so we can’t use them until the server upgrade is complete, but I have 2008 installed on my local machine, so I started to play around with it.

Right now, we use templates to generate a stored procedure to get records.  Supposing we have a view called vwWhatever, we generate dynamic SQL based on the value of variables that we pass in.  One example might look like:

Select * from vwWhatever
WHERE 1 = 1
AND BoardNumber in (select value from dbo.fnDelimitedValues(@BoardNumber, @Delimiter))
AND BoardNumber not in (select value from dbo.fnDelimitedValues(@BoardNumber_Not, @Delimiter))

In this view, we pass in two variables which are varchar(max):  @BoardNumber and @BoardNumber_Not*.  These are fairly self-explanatory:  we are looking for some set of board numbers—say, 1, 2, 3, and 4.  We might also want to exclude certain boards.  The sets of information are delimited by some character called @Delimiter, and fnDelimitedValues is a user-defined table-valued function which splits the string into component pieces.

This is slow for a couple of reasons.  First, splitting strings in T-SQL is slow, and the CLR isn’t that much better.  Calling that user-defined function for every parameter will slow things down.  In addition, in and not in are very bad for performance, and we’d ideally like to translate those into inner/outer joins.

That’s exactly what Table-Valued Parameters let us do.  Instead of the above query, we can translate it into the following:

Select we.* from vwWhatever we INNER JOIN @BoardNumber boardnumber on we.BoardNumber = boardnumber.Value
LEFT OUTER JOIN @BoardNumber_Not boardnumber_not on we.BoardNumber = boardnumber_not.Value
WHERE 1 = 1
and boardnumber_not.Id is null

Now, @BoardNumber and @BoardNumber_Not are user types.  I made an extremely simple user type:

Create Type CodeGeneratedType
(
Id int NOT NULL,
Value varchar(max) NOT NULL,
Primary Key(Id)
)

I originally had Id as an identity integer, but later problems caused me to change it to a non-identity and let ADO.Net handle those values. The concept here is simple: instead of a delimited string that gets passed to the stored procedure, we create DataTables in the data objects and pass them to the stored procedure to allow for inner/outer joins.  The “good” values should be inner joined—we only want to get the ones which show up in that list—whereas the bad values are outer joined and we’re looking for null records, as we want everything but the “@BoardNumber_Not” set.  I made value a varchar for conversion purposes, as it works for most of our data types, but I’m thinking about creating a more detailed set of user-defined types to optimize for integers, decimals, datetimes, varchars, etc.  I don’t know if this would be a worthwhile endeavour, so I’ll have to do some tests to find out.

By doing this, I was able to turn a stored procedure which required roughly 1,000,000 reads (yeah, it was that bad) into an equivalent stored procedure which required just 35,000 reads and roughly a quarter of the time.

Tomorrow, I’m going to try to finish this with a second post on the data object layer, to show what I did there to get my select query to work as anticipated.

* – There can be a number of other values on that table, but our dynamic SQL only includes the “AND [variable] in (…)” bit if the appropriate string is not null, which it is by default.

A Few Notes (Glad Tidings Edition)

- Who are the uninsured? They mostly fit into (at least) one of the following categories:  already covered under Medicaid/CHIPS, non-citizens (many of whom, I would gather, are here illegally), those who are young and healthy, and those who are wealthy enough to afford insurance but choose not to purchase it.  Take those out and you have a much smaller core of individuals for whom you could make the argument that some kind of insurance would be beneficial and for whom taxpayer dollars ought to be spent.  I’d still disagree with that argument, but it’s better than passing along the “there are 950 million uninsured Americans who could die any minute!!!!!!!” hyperbole.

- Thank goodness our federal government is protecting us from the horrors of garage sales. Joe Biden’s on the job!

- I’m sure glad we don’t have a President who makes stupid verbal errors and spits out incomprehensible things. Or maybe he was actually speaking French…

- I’m sure glad that mean ol’ Congressional Budget Office was totally wrong on projected deficits from The Obama Experience. But hey, what’s $2 trillion among friends?

- I’m sure glad Democrats in leadership positions have forgotten that a good chunk of their party don’t even come from San Francisco or (gasp!) New Jersey. (Actually, I am glad about this; that limits what they can do).

- I’m sure glad the ACLU is spying on American citizens. There’s something ironic in the whole story.

- I’m sure glad Democrats, most of whom have never held a real job in their lives, are qualified to determine what form of compensation denotes “excessive” and that health care companies should just spit out all of their documents for propaganda research purposes.

- Finally, Bob Murphy points out one of the problems of regressions. Running a regression against one data sample doesn’t tell you the full truth.  It’s a shame that Krugman the Political Hack murdered Krugman the Economist in his sleep roughly a decade ago…