Blogger Blog Post Title Optimization

I've blogged with Blogger for a few years now, and overall it's worked pretty smoothly, but the blog post title can be a bit awkward to work with.

You'll notice that if your post title is particularly long, the resulting URL will get truncated once the post is published to a maximum length of roughly 39 characters. Spaces will be replaced with dashes, stop words like "to", "and", "of" will be filtered, and punctuation will be omitted.

Principles of SEO demand that blog posts are crafted such that search engines will return links to them as relevant hits for a given search. In addition, it is helpful to make it so that the blog post title is similar to the actual URL of the post.

In a Blogger blog, you can optimize your blog post titles by first deciding on a title which is both meaningful and concise. The title should reflect the content of the post, to make it easier for search engines (particularly Google) to categorize what you're blogging about, and decide how high up in the search results to place it. 

For example, if the title of your post is "How To Make Lots Of Money By Blogging On Random Topics" (which is 54 characters without quotes), try to distill the essence of your post into a title which meets or is under the 39 character limit, perhaps "Make Money Blogging Random Topics" (33 characters) or "HowTo Make Money Blogging Randomly" (34 characters). 

Ideally, you want the post title to meet that character limit for Blogger post titles, but sometimes it's difficult, particularly with stop words and spaces eating of valuable post title real estate. 

To mitigate this, you can try the following steps:

1. Create a blog post with as brief a title as possible.
   
2. Publish the post with the brief title.
   
3. Immediately after publishing, Edit your post and modify the title to your original, more verbose version.

After doing this, you'll discover that Blogger keeps the concise title's wording in the URL, but in the post itself it will have your verbose title. 

Again, it's probably best that the title be the same in both the URL and the actual post title, but if you just can't do your post justice by pruning it below 39 characters, you can at least ensure that the post URL meets that requirement and yet retains value from an SEO standpoint.

Optimizing SQL Views Profit++, Fun--

While working feverishly towards meeting a looming deadline on some SQL code, a problem arose. A particular UI that relies on a particular stored proc to perform a particular task was bombing. 

I brought up SQL Profiler and retraced the user's steps, and found the culprit, a convoluted view I was joining to as a step within the update seemed to be taking way too long to retrieve data.

The view started out relatively simple, a SELECT over several busy tables joined together along with a few scalar function calls and a very few subqueries nestled within some CASE statements. It evolved, thanks to lack of time and impatience and poor choices on my part, into a bit of a behemoth; it took roughly 1:30 to return the full result set. This didn't seem like a big deal at the time, however, because I planned to use it to pluck out specific entries, for this purpose it returned results in less than a second or two, which was acceptable.

Using it in a join though was a relatively new, hastily chosen design decision, and apparently it was proving a very poor choice since it was causing the UI to time out. I began by optimizing the view, minimizing the interaction with the busy tables as much as possible, avoiding subqueries unless absolutely necessary, and creating some new scalar functions to replace previously funky logic. No joy, however, as this only shaved around 10 seconds off the runtime for a SELECT, too little, I thought.

So began the quest to create a table-value function instead, full of optimizations like stuffing frequently used values into variables and creating a subset of the main tables in a table variable (impossible from within a view) and minimizing the use of scalar functions and even resorting to applying the WITH (NOLOCK) and eliminating string searches wherever possible...

It turns out, however, that the entire two paragraphs of chronicled woe were completely unnecessary. Here's a SELECT from within the stored proc which joins with the aforementioned view, notice anything odd?

SELECT TOP 1 @Fruit = 
        
  CASE 
      WHEN v.Operation IN ('APPLE', 'BANANA', 'GRAPE') THEN v.Operation
      ELSE NULL
  END

FROM dbo.ViewOfPain v
WHERE v.ID = ID 
ORDER BY v.InputDate DESC

  

Let's see, looks like I'm just trying a simple SELECT from the view with a CASE statement, checking the ID against the ID, yeah, that looks fi-- O SHI

Notice the missing '@' sign before the ID in the where clause??! I did, after spending a few hours writing a table function that I turned out not to need. The problem wasn't with the view, but with this bit of code in the stored proc, it was doing the equivalent of a SELECT on the entire view plus a bit more overhead to assign a value to the @Fruit variable. I ran this statement by itself in query analyzer, and indeed, it took well over 1:30 to execute!

Below is the code as it should've been, which returns almost instantaneously.

SELECT TOP 1 @Fruit = 
        
  CASE 
      WHEN v.Operation IN ('APPLE', 'BANANA', 'GRAPE') THEN v.Operation
      ELSE NULL
  END

FROM dbo.ViewOfPain v
WHERE v.ID = @ID
ORDER BY v.InputDate DESC

I'd spent hours optimizing and then finally creating a new table function to replace a view that turned out not to be the culprit. If I'd spot-checked my syntax in the stored proc, it's likely I could've picked out this omission of the '@' much sooner, but I was so tired and far gone into troubleshooting that I was getting increasingly desperate. 

The moral of my story is, take a break, unhook yourself from the problem, and review it again later with a fresh mind.