Reporting Querys and COUNT(*) vs COUNT(1)

I was working on a reporting query today and used several legacy queries as a base. When debugging and optimizing the query I found the query took 35+ seconds to crunch down to 72 records. Before worrying about indexes, I went over the query syntax and structure and found a COUNT(*) in one of the subqueries. Changing the COUNT(*) to a COUNT(1) turned the 35 second query into a 300ms query. Take a look for yourself:

Query with COUNT(*)

Query with COUNT(1)

While the use of the * in SQL Queries is always a bad idea and considered very poor form, it can be baffling how bad it affect query performance. What other common mistakes do you see that have dramatic effects on queries?

The database in question is a development SQL Server 2005 database. I'd be interested to know if other databases suffer equally.

Getting USB Device Drivers Working for HTC Android Development

I set up a new Eclipse environment today and wanted to use my HTC Thunderbolt for testing. Usually, the way this works is you right click on your project then select your manual run target of your phone. My HTC Thunderbolt was not recognized for some reason.

After digging around for a bit, I found the USB device driver provided by Google does not support some HTC phones out of the box. I have no idea why. However, fixing it is pretty simple.

All you have to do is update the device driver .inf file. It's pretty simple to do this. Here is what you do:

  1. Follow the steps here: to start the process (if you found this blog article, you have likely done this step already)
  2. If you are on Windows, you'll have to get the Microsoft specific USB driver at the Google Windows USB Driver link.
  3. Once you install the Google Windows USB Driver and follow the instructions on that page for your specific OS, your device will not be recognized.
  4. Use the Device Manager to find your phone. Right Click and choose properties, then choose the Details Tab. On the Details Tab, Change the Property selector to Hardware Ids. Write down the (4?) digits in the VID_1234 (where 1234 is likely different for you) and for PID_1234 (where once again 1234 is likely different for you) You will need them later. If this is confusing, check the screenshot at the bottom of this page.
  5. Use a text editor to open [Android SDK Root]\android-sdk\extras\google\usb_driver\android_winusb.inf
  6. Find the section [Google.NTx86] and copy the lines for the HTC Dream. Paste them and change the dream to your HTC phone model.
  7. Then, update the driver specific lines with the VID_1234 number and PID_1234 number you copied above. Mine looks like this:
    view plain print about
    1; HTC Thunderbolt
    2%SingleAdbInterface% = USB_Install, USB\VID_0BB4&PID_0CA4
    3%CompositeAdbInterface% = USB_Install, USB\VID_0BB4&PID_0CA4&MI_01
    4%SingleBootLoaderInterface% = USB_Install, USB\VID_0BB4&PID_0CA4
  8. Copy and paste this code for the [Google.NTamd64] section also.
  9. When finished, try the driver update once again and you should have better luck this time.

This should improve the situation. Hat tip to Kostya Vasilyev on the Android Developers mailing list for the idea.

ColdSpring 2.0 Alpha 1 Released! Your Help Needed!

Mark Mandel posted information about the ColdSpring 2.0 Alpha release and I wanted to make sure it got out to the general public. There is a documentation contest running and your help is requested in trying out the release and helping to identify issues. Make sure you have joined the ColdSpring Users Group as this is the best way to give and get information about ColdSpring.

Mark's Post is below for your reference

ColdSpring 2.0 Alpha 1 is now available for you to download and test!

Major features included in this release:

  • Enhanced underlying architecture for greater extensibility
  • XML Schema For ColdSpring configuration files
  • New BeanDefinition architecture
  • BeanFactoryInterceptors for intercepting BeanFactory lifecyle events
  • BeanProcessInterceptors for intercepting Bean lifecyle events
  • XML Custom Namespaces for defining your own XML dialect for creating and configurating beans
  • Aspect Oriented Programming (AOP) Custom XML Namespaces
  • Greatly extended AOP functionality with AOP expressions
  • ColdFusion 9 ORM Integration classes
  • Utility Custom XML Namespace for creation of data structures
  • Enhanced error reporting
  • Multiple Bean Scope support – beans can be prototype (transient), singleton, as well as request or session scope bound

More details can be found in the release notes, and my blog post:

We are also running a competition to help flesh out the missing pieces of the documentation, with an opportunity to win a copy of ColdFusion Builder!

Details can be found here:

Happy testing!

Thanks to all have been involved in this release!

Quirk in MySQL Join Conditions

I found a quirk in a join condition today that caused too many records to display. Look at the following query and notice the two AND clauses after the LEFT JOIN to activitytype.

view plain print about
2FROM organization grouptable
4    SELECT c.CommunityID, o.OrganizationID, d.DivisionID
5    FROM
6        community c
7        LEFT JOIN organization o ON c.communityID = o.communityID
8        LEFT JOIN division d ON o.organizationID = d.organizationID
9    WHERE c.communityID = 1
10    ) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
11INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID    
12LEFT JOIN activity a ON a.memberID = m.memberID
13LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
14AND hasDistance = 1
15AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
16ORDER BY activitydate

This query runs and returns 2918 rows. However, when I audit this data, I get rows back that are outside of the time bounds specified in the BETWEEN clause: ( AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14' ). There is no activityDate column on the table activitytype. There is an activityDate column on the activity table however. This means the query is parsed and executed without MySQL throwing an error, but the expression is not used to limit the number of joined rows. The correct recordset (428 rows) is easily obtained by moving the join condition to the correct join statement.

view plain print about
2FROM organization grouptable
4        SELECT c.CommunityID, o.OrganizationID, d.DivisionID
5        FROM
6            community c
7            LEFT JOIN organization o ON c.communityID = o.communityID
8            LEFT JOIN division d ON o.organizationID = d.organizationID
9        WHERE c.communityID = 1
10        ) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID )
11    INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID    
12    LEFT JOIN activity a ON a.memberID = m.memberID AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
13    LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
14    AND hasDistance = 1
15ORDER BY activitydate

I hope this helps someone else with their MySQL queries.

Long Live the Conference!

Last year, one of the major tech conferences, CFUnited, closed it's doors for good. This left a hole in the learning and networking opportunities for ColdFusion developers. I gained much from going to CFUnited over the years. It helped me personally and also professionally. The opportunities to learn from the best minds in the business and develop my skills have been some of my fonder memories of my career.

I would like to invite you to take part of a Technology Conference my group is organizing on Sept. 17-18. The price is minimal, only $60 for 40 sessions and 2 hands on classes! This is our third conference and we guarantee you will have a good time.

Space is very limited so register as soon as you can!

The conference will cover a wide variety of web development and design topics including Web and Mobile technologies. See the schedule here: NCDevCon 2011 schedule

Registration for the event will be $60 which includes:

  • Entry to the weekend event and all presentations
  • Lunch (both days)
  • Coffee, drinks and snacks (both days)
  • Conference shirt

Also available are 2 hands on sessions. We will walk you through building your first web application and also building your first mobile application. These hands on courses are included in your admission fee.


Our EventBrite Registration Link

Can't go? You can still help!

We have a nice NCDevCon flyer for you to forward to your co-workers or hang in your break room. We'd appreciate it!

Thinking Like a Cupcake Kingpin

As you know, my company ChallengeWave is a tool for healthy challenges at work. at ChallengeWave, we've got a number of very large prospects (VLPs) in our sales pipeline. These prospects represent many millions of dollars of revenue. Each sale is complicated and requires approval at numerous levels. Each sale also requires a significant budgetary appropriation. These factors increase the sales cycle, or the length of time it take to close the deal and start receiving funds.

Dealing with complex sales is a work of art. It requires skills in information gathering, positioning and patience. Many months pass before we get to an implementation. This is not only frustrating, but it causes delay in verifying our newest capability with users. We've been looking at ways to increase our feedback loop with our customers. We've chosen to do this by finding customers with smaller sales cycles.

Now, the good things about smaller markets is the shorter line to decision makers and budget wranglers. We can get feedback on our business much quicker. We can validate our results without going through a 9-18 month sales cycle.

This presents us with an interesting problem. As we compete with companies many times our size, our nimbleness and ability to deliver customized solutions is a large asset for us. This asset isn't of great interest for smaller markets who may not even have a wellness strategy at all. Much less, have complicated systems to integrate with. We need to carve out a compelling, simplified offering to help small business.

Cupcake Kingpin

Getting to an understanding of this problem and defining a strategy has taken many months. When running a daily business one gets mired in details and it can be tough to see the forest for the proverbial trees. One trick I use to help me step back from details and focus on the big picture is to imagine I'm a cupcake kingpin in the cupcake business. (My years of making cupcakes for my nieces makes me an expert :) ) So I ask myself, if I ran a cupcake business, how might I handle this problem?

The first thing we must do is rephrase the issues in cupcake-ese. This helps us to over-simply the details and look at the bug picture from an outsiders perspective.

My Problem in Cupcake-ese

In cupcake-ese, the problem is we have a new, unproven recipe and we need to see if people like it enough to buy it.

How would you approach this if it was your cupcake business?

  • Would you hire an army of SEO consultants to build link backs and press releases?
  • Would you hire a market research firm to ascertain which part of the Gartner Magic Quadrant you fit into?
  • Would you just discontinue your current offerings and just offer your new product to the public at large? ( Ha Ha, you may laugh at the absurdity of this, but that is what happened with New Coke in the '80s)

My Solution In Cupcake-ese

The way to solve this problem for the cupcake business is to just make up a few batches and hand them out on the street. If people vomit in the nearest garbage can after ingesting your newest cupcake treat, your mix needs adjusting. However, if the test subjects come back with their friends, you have a winner.

So, we at ChallengeWave have a new cupcake recipe and we are looking for suckers a trial group. Group members will get 2 months of ChallengeWave for their use. Employees will be able to track their activities, challenge other employees, compete on teams and compete against other companies.

In exchange for the free service, ChallengeWave wants unvarnished feedback and help with case studies or press releases as appropriate. If you think your company might be interested in giving ChallengeWave a shot and your company is:

  • 10-50 employees
  • flexible
  • somewhat motivated (especially to change their lifestyle)
  • somewhat competitive
  • computer literate
  • team oriented

If your organization is interested in applying for a trial of ChallengeWave for your organization, let us know.

Have you considered the cupcake kingpin approach to problem solving? Have you solved problems with the cupcake (or similar) method? Tell me about it in the comments....

On What Is Important

An article showed up in my inbox today titled Startup Weekend pep talk: It ain't the code. The background of this article is a pep talk delivered by Jason Cohen, of Smart Bear Software to the audience at Startup Weekend in Austin. I happen to agree strongly with the meat of this article and want to share it with you.

Rather than paraphrase the article and add my own opinions, I'd like for you to read it and apply the points it to your own start up company.

Read: Startup Weekend pep talk: It ain't the code

To Free or Not to Free

It's pretty common for web based services to offer a free sign-up account as a trial period or self-directed demonstration. An advantage of offering free sign-ups is to give a potential customer a good look at the system in order to make a buying decision. For services with automated enrollment and simple service structures, free sign-ups can be a good idea. Converting a free sign-up customer to a paying customer helps lower sales costs. At ChallengeWave, we've decided not to offer a free sign-ups. Let's talk a little bit about why that is.


Gmail Gmail, how thou hast forsaken me

  • Oh Gmail, Gmail
  • How thou hath forsaken me
  • Thou makest me to switch accounts where there used to be no problems
  • Thou loggest me out at inopportune moments when I've emails to write
  • Thou ignorest my pleas to open different accounts in multiple tabs
  • I beat my breast and tear my clothes in anguish...
  • I regreteth having multiple email accounts running off thine servers...

Alas, there is no answer....

( yes I know all about enabling multiple accounts. I've done that and gmail still sucketh. I want it back the way it was. Back when each tab seemed to be a sufficient separation....)

The worst entreprenurial sin

In my opinion, the worst entrepreneurial sin is building a product without a revenue stream. Sure there are strategies to build a web application, go viral and sell to GoogFaceYahooSoft.... but most of my readers live outside of the reality distortion field of Silicon Valley and must actually build a business that makes money.

A business is

A business is defined as the exchange of a good or a service for a profit. If you do not know who will give you a profitable sum for your good or service, stop what you are doing right now and go figure it out.

By figure it out, I mean get some real details. For example, advertising is a choice for a revenue model. Advertising can be a successful strategy and it can also be the lazy man's version of revenue planning. If your monetization strategy is "Advertising", you should get a good understanding of your value as an advertising partner. Some things to think of: