Thoughts « tim laqua dot com

tim laqua dot com Thoughts and Code from Tim Laqua


Although SEO is a technical concept, many businesses don't know what it is and they are not ready to take the risk of investing their time and money to achieve a higher ranking in search engines. SEO is not only about how much search engine traffic your site can receive, but also about ranking for relevant keywords in the search results page.

There are many methods, tactics and strategies for improving the effectiveness of your web page in search engines. We will start with an overview of the different SEO methods and methods of increasing your search engine ranking. Next we will go into detail about the different techniques for increasing the effectiveness of your web pages, including techniques for optimizing web pages. We will then discuss the best SEO services you can choose from.

How voice search is influencing page rank on Google

The different methods for increasing the effectiveness of your web pages are the same, but they all have different goals. The goal with each of these methods of increasing the effectiveness of your web pages is to make your pages search engine friendly. That is to say, to have your pages be found by Google before your visitors. Web pages can be viewed by other search engines and it can help to be found by other search engines before your visitors. There is a great deal of difference between how search engines process a search term and how searchers interpret that term. How searchers process search terms is not always what search engines do. A searcher can be searching for something totally different from what a search engine is looking for. So how does search engines know how to show your website in the right place? The same way you know how your website should look to a person who is searching for your product. Your website should be shown where it will be most useful to your searchers. You can use the same basic concepts and guidelines that you use for search engines to do this, such as:

The best place is on your home page this is the place that most of your visitors will see the website from first.

this is the place that most of your visitors will see the website from first. The bottom of your homepage is the best place to show a list of your products or services most people won't have to scroll all the way to the bottom to see it.

most people won't have to scroll all the way to the bottom to see it. This is the most important tip in a very simple but powerful one. The homepage is what gets the traffic and the most important area to get attention on a website. You can use all your other areas to showcase your products and services but if they are too crowded, then the visitors will have trouble finding what they're looking for.

 Make sure you're giving the visitors a reason to click your ad.

In the same way that Google considers the content you provide on your page to drive traffic, the same should be done for your ads. If you don't have anything to offer that's worth clicking on, then you shouldn't run ads for your business. 8) Don't just advertise on Google, run your ads on other search engines as well. While Google is the dominant search engine, Google's dominance is not absolute.

Filed under: Thoughts No Comments

Hanging The TV

One morning as we were eating breakfast, Lindy (my wife) looked over to the other room where we had a TV sitting on a sofa table and commented that it looked "terrible" because of all the wires all over the place. This is basically license to knock down the entire wall and do whatever you like as long as she can't see those wires when you're done. I stopped short of that and just pulled a little part of the wall off.

Here's what it looked like originally (the terrible wires photo):


The Garage Heater: Natural Gas

Now after all this running around with sensors and propane heaters for indoors - there's really no possible way we could NOT install a natural gas heater and add the new top driveway sealer to add more durability. Browsing the Menards ad for the week yielded a 75,000 BTU Beacon Morris unit on sale for about $450. Figuring that's a pretty good deal, we headed over there and gathered the supplies we needed.

Oh the supplies... B-vent to get out the roof, flashing, crazy angle pipe things, a cap, storm collar, black iron pipe for a drip leg on the gas line, 60' of 1/2" copper flex, some flare nuts, some 14/2 wire, a thermostat, some 18/5 thermostat wire, hangars, and various other random things - probably some beef jerky since you always buy beef jerky at Menards.

All in - a whopping $750 bill at the checkout. Seems like a lot, but it's much less than the $2,200 quote I received from a local HVAC shop for a 45,000 btu heater. Fast forward a few hours and we've got ourselves cheap(er) heat! Make sure to call air conditioning services to avoid any health risks at home or damage.

 Scenario 1: 20 degree day, maintain 50 degrees


On for about 8 minutes, off for about 56 - that's a total cycle of 64 minutes and it's on for 12.5% of the time. It's a 75K BTU heater, meaning it will consume 75K * 12.5% or 9,375 btu per hour. 24 hours * 9,375 btu / 100,000 * $0.67 = $1.50. That's $1.50 per day for a 30 degree temp differential.

Scenario 2: 20 degree day, maintain 70 degrees


On for about 7 minutes, off for 11. Cycle of 18 minutes, on for 39% of the time. 75K * 39% = 29,250 btu. 24 hours * 29,250 / 100,000 * $0.67 = $4.70. $4.70 per day for a 50 degree temp differential.

Scenario 3: Back to 50 degrees


I had some crazy idea like i could figure out the heat loss of the garage with data like this - but there are really just a ton of variables in addition to the heat loss. With garage doors USA company you can replace and improve the functionality of your garage. The biggest issue I see is the heat that moves from the objects in the room (that have been heated up to 70) in to the air around them as the air cools. Look at the difference in how long it takes to drop from 59 to 49 the first time, and then again the 2nd time after the air is heated again.

Here's what it looked like installed:

And the thermostat:

And my car in the middle of being waxed - since I had to keep the garage at 70 for a while to gather data, I figured I'd make use of the warm weather. That laptop in the background is gathering the data:

Filed under: Thoughts No Comments

The Garage Heater: Propane Torpedo

As a first followup to my article that looked in to the economics of heating my garage (The Garage Heater), I ordered myself a TEMPer USB temperature sensor to do some fact checking. Note - I use the term fact pretty loosely... more like "numbers I made up using formulas I found on the internet."

The software I'm using to record the data is called ThermoHID ( - a freeware app which is far superior to the terrible software that comes with the sensor. For this test, I went out on a 5 degree day, bought a bunch of propane, and did my best to keep the garage 60 and 85 degrees for three hours or so:

The first thing I would like to point out in the data is the pause in the initial heat up. It's darn cold out - the garage started around 38, which it seems to be able to maintain with just heat leaking in from the house (there's one finished wall w/ a house on the other side), so when we tried to heat it up to 85 the first time, the propane tank froze up and we had to switch to a warm one. That's just what happens when you use a 106,000 BTU propane heater with a 20lb tank. It simply can't support that rate (something about vaporization rates).

There's also an interesting drop in there after a bit - that's when we opened the garage to vent the the stinky air out (propane stinks).

And finally - it's interesting that as time goes by, it takes less time to heat the garage back up and it takes more time to lose the heat. I'm pretty sure this is due to all the "stuff" in the garage(cars, garage slab, some engines, refrigerators, tools, workbenches, etc). Heating up air is one thing, but heating up stuff - that takes more energy and it has its own rate of heat loss. But when your "stuff" loses heat because the air around it is cooler, the heat goes in to the air - the air we're trying to keep hot. Just an interesting observation. I'm also fairly sure that 106K BTU heater is putting out nowhere near that hooked up to a 20lb tank, it simply couldn't have been based on how fast that much energy should be able to heat the garage. Now to move on to the inevitable - the Natural Gas heater.

Filed under: Thoughts No Comments

The Garage Heater

Being a life long Minnesota resident, running propane heaters in the winter to keep the garage warm is incredibly common... and incredibly smelly. Eventually your eyes start burning from all the stuff in the air. Add in some degreaser and it gets really enjoyable in there. This age-old problem led me to garage door repair service and they helped me figure out how to keep it warm in there without the trusty propane heater (or kerosene - that's equally terrible). The only two viable options are Electric and Natural Gas. I don't really care which - I just want to pick the most cost effective one. If you ask anyone, the answer is always natural gas. But why? How much more efficient is natural gas? I care because natural gas requires a gas line, while electric would just require another (big) circuit (the natural gas heaters require a circuit as well, just way more logically sized). Ever wonder how a flexible heating element rated in watts translate to the BTU rated natural gas heaters? I did. I also wondered how much all this propane is costing... and why I have to run the heater so frequently.

First things first, I looked at my gas bill and it's billed in therms. That's kind of the standard unit i'll use, a therm is 100,000 btu (and wherever I say BTU, i really mean BTU per hour). How's that translate to electricity? Well, 1 watt = 3.414 btu so one therm 29,291 watts. Then I went and looked at my electric bill and it's billed in Kilowatt hours so now I can just multiply by my per kWh cost ($0.10).

Electric costs roughly $2.92 per therm
Natural Gas cost roughly $0.67 per therm

That's a difference of $2.25 per therm consumed. Let's say I think I need a 75K BTU heater. That's 75% of a therm, so for every hour I run it, it would cost $1.69 more for electric. Now let's assume the Natural Gas setup cost $1,000 more to install - it would take 591 hours to eat up the cost difference.

Ok, got that sorted out, now what about this propane stuff, what's that cost? First we have to sort out how much is in the tank (just using 20lb tanks) and divide by the fill cost. Turns out one gallon of propane has 91,600 BTU. But I buy propane in pounds, so one gallon is 4.24 lbs - meaning there's 4.6 pounds required per therm. On the high side, i'm paying about a buck a pound (if they actually filled the tanks up to 20lbs).

Propane costs roughly $4.60 per therm

That's a whole bunch - definitely need to stop using propane. Now let's try to figure out how much heat I actually need rather than just using the 'ol "bigger is better" logic. It takes 0.018 btu per cubic feet to raise the air 1 degree fahrenheit and my garage is 34' x 22' x 9' = 6,732 cubic feet. If I did the math right, it would take 121.176 BTU per degree we want to heat it.

Now for the heat loss part...

Filed under: Thoughts Continue reading

Our Life Fitness X5 Elliptical: Part 1

I want to start off by saying that this is the greatest home elliptical I've ever used. I'm 6' and my wife is 5' 4" - the adjustable stride really goes a long ways (pun intended) towards allowing both of us to enjoy this piece of equipment. I'm not familiar with the Basic console, but I can attest that the Advanced console's auto-adjusting workouts are very handy at 6AM when you're really not interested in pressing buttons - or really thinking at all. This unit gets used almost daily (by Lindy - I fell off the wagon some years ago) and we've owned it for about 6 years now. Here are the stats on this mamma jamma:



We haven't had a SINGLE problem with it... until now. After our most recent move, to be exact. Now - we've been in our new place for about 4 months and this is the first time I noticed it was having an issue. Wait - but didn't I say it was used daily? Oh, it is. But Lindy is they type that doesn't really care about the flashing lights on the screen, she actually finds them to be distracting. So what she does is turns the unit on, hits all the buttons to get it in to manual mode, and lays a DVD case across the entire screen, cause she really doesn't care what it says. Well, I decided I should jump back on the wagon for some strange reason earlier today and my little auto adjusting program wasn't working. The Level area of the display simply says "OFF" and no amount of button pressing will wake it up.


So the only good news here is I get to give Lindy crap for always using the elliptical on Level 1 and being a slacker. Bad news, I gotta fix it since the warranty ran out like 3 years ago.


Upgrading from SQL 2008 R2 Enterprise to SQL 2012 Business Intelligence

With all the new licensing considerations in SQL 2012, everyone's busy trying to figure out exactly what the cheapest license they can get by with is. For one of our instances, we decided rather than Enterprise, we could run the Business Intelligence edition to save some cash. That upgrade path is explicitly listed in the Supported Version and Edition Upgrades documentation.

What happened?

We clicked through the upgrade wizard as usual and it got through about 85% and then prompted an error:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

We clicked ok, then it finished and the results showed that the SQL engine and a few other features failed the upgrade. Great.


Slowly Changing Dimensions with MD5 Hashes in SSIS

We recently moved away from the 3rd party Checksum component (and all 3rd party components) in SSIS and I wanted to share the pattern we settled on for maintaining our Type 1 Slowly Changing Dimensions (SCDs). There are two things we wanted to address with our new pattern. First, our previous implementation wasn't performing as well as we needed it to or generating reliable checksums. The second was that we wanted to get away from dependencies on custom assemblies in general. To illustrate the pattern, we're going to build a SCD package off the Adventure Works DW DimCustomer table and skip over the actual source of the business keys and attributes by selecting directly from the completed dimension for now.

First, we assume that our dimension already exists (and we were using some other checksum or MERGE to maintain it). We have to add a column to store the MD5 hash:

ALTER TABLE dbo.DimCustomer ADD

Second, we need a staging table to store updated/changed rows. Script out the current dimension as a CREATE, remove all unneeded constraints and indexes, and create a staging table as a heap:

CREATE TABLE [dbo].[Staging_DimCustomer_UpdatedRows](
	[CustomerKey] [int] NOT NULL,
	[GeographyKey] [int] NULL,
	[CustomerAlternateKey] [nvarchar](15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[NameStyle] [bit] NULL,
	[BirthDate] [datetime] NULL,
	[MaritalStatus] [nchar](1) NULL,
	[Suffix] [nvarchar](10) NULL,
	[Gender] [nvarchar](1) NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[YearlyIncome] [money] NULL,
	[TotalChildren] [tinyint] NULL,
	[NumberChildrenAtHome] [tinyint] NULL,
	[EnglishEducation] [nvarchar](40) NULL,
	[SpanishEducation] [nvarchar](40) NULL,
	[FrenchEducation] [nvarchar](40) NULL,
	[EnglishOccupation] [nvarchar](100) NULL,
	[SpanishOccupation] [nvarchar](100) NULL,
	[FrenchOccupation] [nvarchar](100) NULL,
	[HouseOwnerFlag] [nchar](1) NULL,
	[NumberCarsOwned] [tinyint] NULL,
	[AddressLine1] [nvarchar](120) NULL,
	[AddressLine2] [nvarchar](120) NULL,
	[Phone] [nvarchar](20) NULL,
	[DateFirstPurchase] [datetime] NULL,
	[CommuteDistance] [nvarchar](15) NULL,
	[MD5] [varchar](34) NOT NULL)

Now in to SSIS - We will be building:

  1. Execute SQL Task to Truncate our Staging table(s)
  2. Data Flow Task to Insert new rows and Stage updated rows
  1. OLE DB Source to retrieve our source data
  2. Script Component to Generate Row Numbers
  3. Conditional Split to Evenly Distribute Rows
  4. Script Component to Generate MD5 Hashes
  5. Union All to Squish it all back together
  6. Lookup to get the existing MD5 Hash (if it exists)
  7. Conditional Split to separate Unchanged and Changed rows
  8. RowCount Transformation
  9. OLE DB Destination for Changed rows
  10. OLE DB Destination for New rows
  • Execute SQL Task to Update changed rows
  • Completed Control Flow

    Completed Data Flow


    Uncivil UNIONs

    If you asked any SQL Server Developer or DBA what UNION does, they would tell you it combines two result sets. Then if you were to ask them if they knew it removed duplicates without the ALL argument, they would say yes. That's academic, but I dare you to go look at their and your code and see how often UNION without the ALL argument is used. UNION is evil. Why do I say that? Because DISTINCT is evil and blocking operations are evil so it follows that UNION is evil.

    Would you be OK with the following query as the source for a fact table?

      SELECT DISTINCT [c1],[c2] FROM [t1]
      SELECT DISTINCT [c1],[c2] FROM [t2]
    ) a

    No? Then why are you OK with this?

    SELECT [c1],[c2] FROM [t1]
    SELECT [c1],[c2] FROM [t2]

    Personally, I tend to use UNION to combine data from two different sources. For example, if I wanted to combine the sales transactions from two JDE instances - the query might look like:

    SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
    SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]

    Each line here in a sales detail represents a line on an order, so assuming you selected all the PK columns in your query, a UNION won't remove any rows within either set, but when combined - it's possible the same PK could exist in both systems. Now you could split hairs and say it's highly unlikely the rest of the row would be identical, but that's not the point - it's wrong to ever DISTINCT a set of transactions intended for creating a fact table. Sure, in this particular case you could add a "SourceSystem" column that would stop that, but then you still have to accept that the UNION is blocking and expensive. Just because a DISTINCT doesn't modify results doesn't mean it didn't do any work - it actually did a great deal of work. Now consider that we're usually not selecting 2 columns, we're selecting 10-20. Do you want to wait for it to figure out all the rows are distinct when you already knew that? Further, if they're not distinct you probably screwed up a JOIN somewhere.

    As far as coding standards regarding UNION go, I would suggest never using UNION without the ALL clause and doing your DISTINCT elsewhere if that's actually what you intended. Does that seem silly? Yes - but I would wager you won't ever use the 2nd one outside of building Dimensions.

    SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
    SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]
      SELECT [c1],[c2] FROM [JDE1].[dbo].[F42119]
      SELECT [c1],[c2] FROM [JDE2].[dbo].[F42119]

    And don't get me started on unioning subqueries with GROUP BY's - Why don't you just throw half your processers in the garbage, light tempdb on fire and put all your data in heaps.

    Tagged as: , 3 Comments

    Pattern for Conditionally Sending SSRS Subscriptions

    This is a pretty common request - send the report if there's an issue, don't send it if there's not an issue. This is a simple pattern for doing that using Data Driven Subscriptions. Here, we're not using the data driven subscription to dynamically populate recipient and parameter information, we're just using it to suppress the sending of the report if the report has no value.

    To do this, we only need one thing - a query that returns one row if the report should be sent and zero rows if the report should not be sent. Here is an example - Let's say I want to send a report out if a JDE Address Book record with an AT1 of 'DC' has been updated after a certain julian date.

    First, write a query to return rows meeting that criteria:

    Tagged as: , Continue reading