Clearing line-breaks in SQL Server text fields

I was recently doing a data export for a client that included a bunch of ostensibly plain-text data fields that include any number of tabs, carriage returns and line feeds that were mucking up the csv file. And oh, how I fought that data to make it look nice. It was many, many hours past when it should have occurred to me that I finally thought to clean up the fields on export, NOT during import. Such is the problem with tunnel visions.

Anyway, for future reference, and easy way to clean up fields to simply replace the offending characters, using something along the following lines:

SELECT replace([field],char(10),'[replacementchar]') FROM [table]

So, I wanted to replace line feeds (char(10)) and carriage returns (char(13)), so I doubled that to:

SELECT replace(replace([field],char(10),' '),char(13),' ') FROM [table]

And it all worked beautifully. I’m writing & storing it here on the hopes if I run into this again, it’ll be googleable.

I’m in the forest, searching for the shore

Corkscrew tree
Searching for the way through

I’m percolating. Gestating. Mulling. Procrastinating. Whatever you want to call it, I’ve been in this mode for the better part of a week now. This happens regularly to me – something triggers my subconscious and it starts to take up and more and more mental resources. When I get phases like this, I’m sort of hopeless: I can’t remember anything, I’m  as distractable as – SQUIRREL! – my production nosedives. I almost never seem to get a warning that this is about to happen, just suddenly there I am – feeling like I’ve only got half brain-power. Typos go up. I’ll find myself staring off into space for who knows how long.

There’s a plus side to this. When I get like this it’s because I’m figuring something out. It sounds strange to say I don’t know what it is I’m figuring out, but historically, whatever pops into my head on the flip-side is fully formed, ready for me to copy down. I used to write papers this way – wander the streets aimlessly for a day or two, come home, sit down & type for a couple of hours before school, come home with an A paper shortly. Prior to agreeing to have kids I did this. When I wrote the first scheme for the original Pencilcase CMS, back in 1999, I couldn’t work for a week. Then in 1 sitting, I wrote the first version of the CMS over about 14 hours, with little to no edits.

So what have I been thinking about lately?  What’s going on back there? Well, there’s a bunch of stuff going on that are viable candidates for taking over my brain:

  • Moving: We might move away. We might buy  a new place in Vancouver. We definitely want to spend some time away – 3,6,9,12 months, who knows. The plan for that needs to resolve itself.
  • Community: I’ve been thinking a lot about the cross-sections of digital and real-world communities. My experience as a terribly shy human vs. a fairly chatty avatar. How to correlate the two, how to bridge the various communities I participate in on- and off-line.
  • CMS: The current world of CMS’s don’t really match the type of tools many of my clients need. Nor do the social CRMs. Nor does the issue-tracking software we and they all use. But they all form part of a solution to a real issue. And I feel like I’m on the hunt for a lightweight suite to handle lots of basic needs.
  • mobile & responsive design: Having now built a couple of responsive sites, in addition to 2 distinct “mobile” sites in the last few months, there’s a path there that I haven’t quite found. This is closely related to the CMS problem: solving the issue of ongoing site existence & emerging break points & client-control of content and so on.


So I’m in the forest, I’m looking for the path. I keep catching glimpses of the shore out there, where the horizon is clear and present, but I’m not there yet & it’s frustrating.

WherePost: now even more useful!

Since I launched Where Post? on Friday morning, the response has been pretty gratifying. My thanks to David Eaves for his nice post about Where Post? this morning. I launched the app with a total of 28 mailboxes and 1 post office. Since then, 29 contributors have added in another 400-odd mailboxes and about 20 post offices. (NB: ‘contributors’ are identified currently via a combination of a cookie & IP Address – so it’s not exact, but close enough).

I’m please to announce a very useful addition: Every post office in the Google Maps database, everywhere, pulled in from the Google Places API. I had noticed while adding post offices that there was often an envelope icon already in the map where I wanted to add a post office. After some digging this afternoon, I was able to pull in the places API to just get all the places that identify as a post office.

There’s a few oddities to figure out:

  1. Often each post office is listed 2 or 3 times at least in Canada: The french name & english name appear to be 2 places, and sometimes the post office in english, the post office in french and the store containing the post office are all listed. Odd, and I haven’t yet figured a way to filter this, but still pretty nice.
  2. I have a rate limit of 100,000 queries a day. Given that each time you see the “loading mailboxes” message there’s a query to Google, there’s a distinct possibility I’ll reach that. For now not a worry, but definitely a scaling/caching issue to think about in the future.
  3. Integrating with the “nearest” function. Currently, the “nearest” mailbox is simply pulled from an SQL query – which means that post offices, coming in from Google, are ignored. There’s likely a way to merge the two, but nothing’s coming to mind at the moment.

As always, if you have any suggestions, comments or anything else, please let me know!

Introducing: Where Post?

Where Post? is a small web-app I wrote over a couple of evenings this week to serve a very particular purpose: To help me, and anyone else, find their nearest mailbox.

The site should work on iPhones, Windows Phones & Androids. It’s meant to run as an app, so you can install it to your home screen for the greatest effect.

There are currently 2 ways of adding new mailboxes – as time permits, I’ll add more:

  1. In the app: to add a new mailbox, click on the “+” at bottom right, then click on the map where you know there’s a box. If you like, add some notes like “next to the garbage can” or “across the street from the pink house” to help people find it.
  2. Instagram: You can also take a picture of a mailbox on instagram, tag it #wherepost and include a location. A mailbox, with the photo you took, will be added at that location. Your photo’s caption will become the notes for the mailbox. I think it’s a fun use of the instagram API.

Of course, you can also simply find directions to the nearest mailbox to you. Just click on the magnifying glass, and Where Post? will provide you with walking directions to the nearest box (within 2km).

The app is very much a work-in-progress – to come is the ability to add in Post Offices, as well as pick-up/drop-off locations for the various courier companies, so that eventually, it’s a one-stop place to go to find where to send something from. Any and all feedback is much appreciated. In particular, if you know how to change the cursor icon in Google Maps v3, I’d love to know how.

So please have a look, play with it and send me any feedback you might have!

Strange Safari rendering bug, please help!

We do work for GSPRushfit, including managing their cart. Currently, I’m having trouble with a bizarre bug in their cart.

When customers update their Shipping Country, a few things happen behind the scenes:

  1. If the country has a few shipping options, a <select> appears next to the Shipping title. Otherwise, the shipping method is displayed.
  2. The shipping/taxes & totals get updated.

On Chrome, Firefox, IE, Opera, Safari older than 5.0 and Safari for Windows, this works totally fine.

However, on Safari 5.0+ on OS X, the displayed text doesn’t update. What we’re doing is updating the “name” of the shipping method, via jQuery. After some ajax calls, there’s a little function that simply updates with an .html(); edit. Here’s what it should do:

Shipping to Europe on Most Browsers

When I update my country to “South Africa” in Firefox, I get:

Shipping Internationally

But when I do this in Safari 5.0+ on a Mac, I get this:

What Safari Shows

As you can see – somewhat borked. It doesn’t clear the old text and update the new text. If you highlight with a mouse, the updated text shows correctly. Viewing source code, all has been written. It’s purely a rendering issue. In the displayed case above, this isn’t the end of the world. However, alongside the shipping “name” we also update more pertinent information such as Shipping Amount, taxes & of course the order total. The source code is all  updated, so if/when people actually click “place order”, all that is correct. But understandably we’re seeing a fairly high abandonment rate from Safari users.

To make matters worse, this code works perfectly on both my local dev environment (Mac, Apache) AND our staging environment (Windows 2008 Server, IIS). It’s only on production that its failing. Things I’ve isolated as NOT being the cause:

  • SSL: Same bug appears whether over SSL or not.
  • jQuery version: I’ve updated jQuery, rolled back jQuery.
  • CSS/JS served locally vs. from CDN: same.

We have a variety of rules for caching/not caching on the server to help speed up the site. I’ve turned on/off a variety of these with no success – but that remains the major difference between dev, staging & live.

Has anyone seen this? Could you, if you have 5 minutes, please try and recreate the issue and let me know if things work for you or not? To recreate:

  1. Using Safari, go to
  2. Click on the big red “Click here” at top right.
  3. Click on “Add to Cart”
  4. Set your country to, say, Canada in the Shipping section.
  5. Change your country to “Norway”
  6. Change your country to “South Africa”

With each country-change, both the info next to where it says “Shipping” should change. Above, at right, the shipping & Handling total should update too.

Please let me know in comments if this works for you.

A Milestone, of sorts

Earlier this week, the NPA launched their new website for the fall 2011 civic election campaign. With this, I’ve reached a personal milestone: Over the past decade, I’ve built election campaign websites for each of the Vancouver civic parties – COPE in 2002, then Vision‘s in 2005 and now the NPA this year. I like to think it shows a professional non-partisan manner than is to be commended. You might say it’s pure capitalism at work (or worse yet that I’m a sell-out). Regardless of your opinion, given how entrenched political parties and their service providers seem to be, I’m quite proud that these various groups have all chose me &/or my company over the years to provide them with professional, quality web-related services.

For this latest project, we were purely the technical team – I’ll have no hand in the ongoing messaging or marketing. Design & project management was provided by our frequent collaborators at Myron Advertising + Design.

At the provincial level, this year I’ve also completed the BC trifecta: I’ve built sites for each of the BC Liberals, BC NDP, and waaaay back in the 90s, the BC Green Party.

So I’m an experienced campaign website builder. If you need a website for your campaign, let me know.

User Control over the Granularity of Location Services

I use a lot of location services on my phone: when I tweet, more often than not, I include my location. I love geo-tagging my photos, so I generally include my location when using instagram, Hipstamatic, etc. & I regularly check in on Gowalla & Foursquare. So I’m not averse to sharing my location in general. I actually quite like it. That being said, I often wish I could be less specific about where I am. I don’t think it would be too hard to add a little slider, or some interface, to provide some scale.

By default, we send data for a point. But what if I could choose to send data at a variety of scales: point, neighborhood, city, region, province/state.

I suppose the particular use-case for this to avoid sending the exact location of my house – I do it, somewhat inadvertently, but I could imagine not ever wanting to do it. But still, letting people know that I am currently in South Vancouver (neighborhood), or Vancouver (city), or Lower Mainland (region), or BC (province/state), rather than my location within 100 metres should be perfectly acceptable data points – and gives me some control over the specificity of my data points.

In the above example, it is up to the app developer to provide scale/fudge-factor options. But we could abstract this farther, and make it a device-wide setting. My phone, via GPS, can always tell where I am. What if I could, device-wide, say “When in these areas, broadcast my location with much less specificity. That way, when I’m actually at home, it could automatically just send, say “Vancouver”, rather than my location. And by letting me choose where I want to reduce specificity, I still have the control – I set it up in my settings or preferences.

I suspect there’s a variety of implementation details that I haven’t really thought through, but I do think that this is an issue that if not the device (/OS) makers need to address, than app-developers do. Let me participate in location services, but at my security level – not what you’d ideally want. It’s a users-first approach to location, rather than data-first.

Optimizing site speed: a case study

One of our clients runs an eCommerce site selling workout videos on behalf of George St.Pierre, the UFC Fighter, called GSPRushfit. The videos sell well, but they’re quite rightly always looking at ways to increase sales. A few weeks ago, we ran a study to see how fast the site loaded, and how that affected conversion rates (sales). I wrote a post about how we measure that a couple of weeks ago. The result of this was that we could see that page loaded reasonably well, but not fantastically. Across all pages, the average load speed was 3.2 seconds. What was eye-opening was that pages that loaded in 1.5 seconds or less converted at about twice the rate of pages loading 1.5-5 seconds. There was  a further dip between 5-10 seconds. So with this data in-hand, I started to look for ways to increase page load speed. I came up with a laundry list of things to do. Most of these are suggested by YSlow:

  1. Remove inline JS/CSS: We didn’t have a lot, but there was some unnecessary inline scripting. These were moved into the existing CSS & JS files. I think I added about 50 lines of code. Not a lot, but helpful. There’s still some inline JS & CSS that’s being written dynamically by coldfusion, but all the ‘static’ code was moved into one.
  2. Minify Files: This doesn’t do a lot, but does compress files slightly. I think I was able to reduce our JS file by 30% & our CSS by 15%. Not a lot, but still helpful. I use an app called Smaller, which I’m a big fan of. While YSlow suggests you combine files, I chose not to – the reduction in requests didn’t offset the problems for us in doing this.
  3. Reduce Image Size. The site is fairly graphically intensive – large background images & lots of alpha-transparency PNGs. When I started, the homepage was loading just under 1.2MB in images, either as CSS backgrounds or inline. Without (to my eye) any noticeable loss of quality I was able to re-cut those same images to about 700KB in size.
  4. Use a CDN: The site loads video, which we call from a CDN. But the static assets (CSS, JS, Images) weren’t being pulled. This was originally because the CDN doesn’t support being called over SSL. But it only took a little scripting to have every image load from the CDN while not on SSL, from the local server while over SSL. This, as you’d expect, greatly improved the response time – by about 0.2 seconds on average.
  5. Query Caching: this one is likely a no-brainer, but the effects were stunning. All the content is query-driven, generated by our CMS. But it doesn’t change terribly often. So I started caching all the queries. This alone dropped our page load time by nearly a full second on some pages. And to maintain the usefulness of a CMS, I wrote an update to clear specific queries in the cache when new content was published.
  6. GZip: Again, likely something I should have already been doing, but to be honest, I had no idea how to accomplish this on IIS. So I figured that out and requested that the server gzip static assets (JS, CSS & HTML files).
  7. Far-future expires headers. Because very few of our images change frequently, I set an expiry date of 1 year in the future. I likewise set a cache-control variable of the same time frame. Which, should, in theory, reduce requests and allow clients revisiting to just use their local cache more. I of course added a programmatic way to clear that cache as well for when we change content or edit the JS or whatever.
  8. Clean up markup: While I was doing all the rest, I also cleaned up the markup somewhat – not a lot, but again, we were aiming to eliminate every extraneous byte.

So, as you can see, we sort of threw the kitchen sink at it to see what stuck. In retrospect, I wish I had made these updates one at a time to measure what sort of an impact (if any) each one had. There’s only a couple I can see clear before & after differences, which were mentioned above. So for everyone out there, which of these were the most effective?

  1. Caching Dynamic Content: Even on a CMS-driven site, most content doesn’t change constantly. But if you can eliminate trips to the DB server to make a call, that’s time saved. Even if you cache a recordset for a few minutes or even just a few seconds, on a high-traffic site you can see some real impressive gains. We cache queries on this site for 10 days – but can selectively update specific queries if a user makes an edit in the CMS – sort of a best of both worlds right now. This somewhat depends on having a powerful server – but hosting hardware & memory are pretty cheap these days. There’s no reason not to make use of it.
  2. Crushing images: When building the site, I did my best to optimize file size as I exported out of Photoshop. but with a few hours in Fireworks I was able to essentially cut the size of the images in half with no real visual impact. A hat-tip to Dave Shea for the suggestion of using Fireworks.
  3. Pushing Content to a CDN: this is the head-smacking no-brainer that I don’t know why wasn’t already part of our standard workflow on all sites. As I wrote above, we gained about 0.2 seconds by doing this – which doesn’t sound like a lot, but it’s noticeable in practice.

The nice thing about this exercise was that it shook up how I built sites, how our internal CMS runs and how I configure both our IIS and Apache servers to all run slightly more efficiently. I suspect that I could eke out a few more milliseconds by playing more with the server settings itself, but I’m satisfied for now with how this worked out.

Measuring Page Load speed with Google Analytics

UPDATE: Google now has a built-in tool for doing this. Simply add the following: _gaq.push([‘_trackPageLoadTime’]); and hey presto: Page-load-speed tracking. See this page for more info, including an important caveat.

With Google now including Page Load speed in their page-ranking algorithm, many of our clients started asking us how fast their sites load. There’s lots of developer tools that can help with this – I use (alternately) the Google Page Speed tool and Yahoo! YSlow during development to tweak page load times. But this doesn’t help our clients very much.

There’s a chart you can use on Google Webmaster Tools, but overall, I don’t find Webmaster Tools particularly end-user friendly. As it’s name implies, I’m not sure that it is supposed to be. A couple of our more technical clients use it, but most don’t use it, or don’t really get it. The chart that Google Webmaster Tools spits out can be found under “Labs”, then “Site Performance”. It looks like this:

Google Webmaster Tools Page Speed Chart
Google Webmaster Tools Page Speed Chart

Which is nice, and gives a clear delineation of what’s good, and what’s not. As you can see, this site rides right on the edge of being “fast”. When a client asked if I could tell him how fast individual pages are loading, this gave me the idea of using Google Analytics’ Event Tracking feature to log how fast a page loads. What’s nice about this is that most of our clients “get” Analytics, and are used to going there to see their stats. So additional stats there works for them.

With Google’s visual guidelines in place, I set about making this happen. I decided to name the Event Category “PageLoad”. I then added 5 labels to group the results:

  1. Fast (less than 1500 MS) (because Google, according to Webmaster Tools, considers anything 1.5s or faster “fast”.
  2. Acceptable (less than 3000 MS)
  3. Middling (less than 5000 MS)
  4. Slow (less than 10000 MS)
  5. Unacceptable ( 10000 MS or longer)

These groupings are completely arbitrary – I could have set them at any time span. Those just seemed reasonable to me, based on knowing that most of our sites have an average load time of 2800 MS, based on our internal tools.

So then I had to track it. The code is as follows:

var pageLoadStart = new Date();

window.onload = function() {
var pageLoadEnd = new Date();
var pageLoadTime = pageLoadEnd.getTime() - pageLoadStart.getTime();
// let's set some second (1000s of ms) segments
if (pageLoadTime < 1500)
    loadStatus = 'Fast (less than 1500 ms)';
else if (pageLoadTime < 3000)
    loadStatus = 'Acceptable (less than 3000 ms)';
else if (pageLoadTime < 5000)
    loadStatus = 'Middling (less than 5000 ms)';
else if (pageLoadTime < 10000)
    loadStatus = 'Slow (less than 10000 ms)';
    loadStatus = 'Too Slow (more than 10000 ms)';
var myPath = document.location.pathname;
    myPath +=;
// round the time to the nearest 10 ms.
pageLoadTime = Math.round(pageLoadTime / 10) * 10;
// send the GA event
try {
    _gaq.push(['_trackEvent', 'Page Load', loadStatus,myPath,pageLoadTime]);
} catch(err) {}

Some Notes:

  • I have the first line (var pageLoadStart = new Date();) at the very top of my header, right after the <head> tag. The window.onload() function sits in the footer.
  • I round my values to the nearest 10 MS – but this might be too many values, too noisy. So you could round to the nearest 100 MS or even nearest second if that worked  better for you.
  • the document.location.pathname just passes the page’s address to Google, so I can see which pages are slow, or when particular pages or slow.
  • You can only send integers to the Event Tracking widget.
  • the _gaq.push() line is where I send this to Analytics. You might have your Analytics tide to a different variable, in which case change the _gaq to whatever you use. For more on how event tracking works, see the docs

What my client then sees in Google analytics is a chart like this (note – I’m using the new version of Analytics – yours might look different):


PageLoad chart
PageLoad chart

As you can see (this is for the home page of the site), the results are ALL over the place. Which is troubling, but that’s for another day to figure out why it sometimes loads sooooo sloooowwwwlyyyyy. (We’re currently running an A/B test wherein a video auto-plays or not. My best guess is that because the page doesn’t finish loading until after the video starts to play, that explains the slow loading. But maybe not).

Regardless of the actual performance of this particular page, you can see how this is a nice little chart for clients to see – nothing too technical (I could possibly be even less technical by not including the MS numbers in the labels), but provides them some easy insight into how their site is doing.

Caveat: This chart obviously doesn’t include any pre-processing factors – connection speed, server-side scripts taking a long time, etc. But it’s still pretty useful, I think.

Coldfusion Java.lang.ThreadDeath

Of  late, we’ve stared seeing a raft of errors of type “java.lang.theadDeath” on our Coldfusion-based servers. In every case, these errors were generated by the CFSchedule User Agent, meaning that scheduled tasks were triggering them. Of more interest, they were, with 1 exception, being thrown by a scheduled CFPOP call to handle email newsletter bounce-backs by our eNewsletter delivery system. The scary part is that, more often than not, our server would start to hang shortly after getting a bunch of these and we’d have to restart the service. Not ideal on servers that require very uptime on account of some exceedingly busy eCommerce sites.

After casting about fruitlessly on Google & Bing for some answers to this, I buckled down to investigate the errors more closely myself. And I believe I have figured it out:

Each of our websites has a Cron handler: We add in any number of scripts we wish to call, and interval between & whether it is allowed to run concurrently with anything or not. The central ColdFusion scheduler service then simply calls this handler for each site at regular intervals (generally, we call the handler about every minute. The tasks themselves range from every 2 minutes to monthly). This handler runs multi-threaded – that is to say that, if a task is allowed to run concurrently with other tasks, it’ll spawn a thread for each task, run it, then tie up the threads afterwards.

So here’s where I think this issue arrives from: According to the very scant documentation and blogs I could find, this error-type will be handed off when a thread is killed off by the server after being queued for too long, awaiting execution, or for the loser in race conditions. We had our server to allow for 10 concurrent threads. Which is pretty small, but seemed ok. My guess is the connection to the mail server when popping messages runs threaded itself. So we were spawning massive number of threads. Further to that, our allowed timeout on mail server connections was longer than our allowed request time or thread-queue time. So threads were constantly timing out, or being killed off because they were queued for too long – which would then spit that error.

Given that we have oodles of RAM, I’ve both upped the number of concurrent threads we allow, as well as reduced the mail-server connection timeout to be less than both our request-timeout and queue-timeout. Testing throughout today appears to have solved this, but I’ll have to watch & see over the next few if indeed this is now solved.