The No-Dans Club

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

Fri, 20 Apr 2012 10:34:00 -0700

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

Wed, 01 Feb 2012 11:26:00 -0700

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:

Follow the steps here: http://developer.android.com/guide/developing/device.html to start the process (if you found this blog article, you have likely done this step already)
If you are on Windows, you'll have to get the Microsoft specific USB driver at the Google Windows USB Driver link.
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.
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.
Use a text editor to open [Android SDK Root]\android-sdk\extras\google\usb_driver\android_winusb.inf
Find the section [Google.NTx86] and copy the lines for the HTC Dream. Paste them and change the dream to your HTC phone model.
Then, update the driver specific lines with the VID_1234 number and PID_1234 number you copied above. Mine looks like this: ; HTC Thunderbolt %SingleAdbInterface% = USB_Install, USB\VID_0BB4&PID_0CA4 %CompositeAdbInterface% = USB_Install, USB\VID_0BB4&PID_0CA4&MI_01 %SingleBootLoaderInterface% = USB_Install, USB\VID_0BB4&PID_0CA4
Copy and paste this code for the [Google.NTamd64] section also.
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!

Tue, 25 Oct 2011 06:31:00 -0700

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: https://sourceforge.net/apps/trac/coldspring/wiki/NewInColdSpring2.0 http://www.compoundtheory.com/?action=displayPost&ID=537

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: http://www.compoundtheory.com/?action=displayPost&ID=538

Happy testing!

Thanks to all have been involved in this release!

Quirk in MySQL Join Conditions

Mon, 24 Oct 2011 12:30:00 -0700

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.


SELECT *
FROM  organization grouptable 
INNER JOIN (
	SELECT c.CommunityID, o.OrganizationID, d.DivisionID
	FROM
		community c
		LEFT JOIN organization o ON c.communityID = o.communityID 
		LEFT JOIN  division d ON o.organizationID  = d.organizationID
	WHERE c.communityID = 1
	) orgmodel ON (  grouptable.OrganizationID = orgmodel.OrganizationID )
INNER JOIN member m ON orgmodel.OrganizationID =  m.OrganizationID	
LEFT JOIN activity a ON a.memberID = m.memberID 
LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID
AND hasDistance = 1
AND activityDate BETWEEN '2011-08-01 00:00:00' AND '2011-10-24 13:38:14'
ORDER 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. SELECT * FROM organization grouptable INNER JOIN ( SELECT c.CommunityID, o.OrganizationID, d.DivisionID FROM community c LEFT JOIN organization o ON c.communityID = o.communityID LEFT JOIN division d ON o.organizationID = d.organizationID WHERE c.communityID = 1 ) orgmodel ON ( grouptable.OrganizationID = orgmodel.OrganizationID ) INNER JOIN member m ON orgmodel.OrganizationID = m.OrganizationID 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' LEFT JOIN activitytype at ON a.activityTypeID = at.activityTypeID AND hasDistance = 1 ORDER BY activitydate

I hope this helps someone else with their MySQL queries.

Long Live the Conference!

Tue, 16 Aug 2011 07:21:00 -0700

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.

Registration:

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

Tue, 02 Aug 2011 11:16:00 -0700

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

Mon, 25 Jul 2011 13:18:00 -0700

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

Wed, 06 Jul 2011 08:08:00 -0700

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. [More]

Gmail Gmail, how thou hast forsaken me

Tue, 05 Jul 2011 19:58:00 -0700

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

Fri, 01 Jul 2011 07:59:00 -0700

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: [More]

Perfection is the Enemy of the Good

Thu, 30 Jun 2011 09:04:00 -0700

Remember when you used to work at BigCompanyCo as an employee? Remember all the dumb stuff they did there and how you would do it differently when you get in charge? Honestly, that feeling was one of the major drivers for me to start my own company. I felt like I could do it better, cheaper and faster. I was right, of course, (thankfully!), but I must admit I've learned a whole lot along the way. I'm a software developer/program manager by trade and I've spend the last 12 years building products and services for various companies. This means I've got a very strong technical focus and I'm able to craft and deliver very good technical strategies for lots of situations. Like, do you need a quick and dirty reporting application to get insight in to your daily sale composition? Do you need an enterprise quality lease contract risk scoring application? Are you in need of an e-commerce infrastructure that can handle the surge of being on all 5 major television networks to raise money for cancer? I'm your guy.

Specialization at the expense of Generalization

[More]

So you've got a good idea, do you?

Tue, 28 Jun 2011 12:11:00 -0700

There is an old saying, "Ideas are cheap, 'tis execution that pays the bills". It's possible to have a good idea and use poor execution. It is possible to have a poor idea and use good execution. Allow me to pontificate:

Good idea, bad execution

An example of a good idea that was executed poorly is WebVan. Founded in the late 1990's, WebVan promised to let you order groceries online and deliver them to your home. This is a good idea and one I would pay a premium on. Mostly because I'd be able to avoid maniacal coupon clipping crazies clogging up the isles with their grocery carts and coupon flip books. The good part of the idea is in taking friction out of the grocery shopping experience. Many a good business model has been in offering convenience at a slightly increased cost. The bad execution comes in how WebVan allocated it's funds. Since huge tranches of money was raised through Venture Capital and though an IPO, Webvan was not careful with it's funds and collapsed under it's own overbuilt, overspent and underutilized weight. WebVan was voted the #1 Dot-com flop by C-Net in 2008. [More]

I'm going to write about Entrepreneurialism at nodans.com

Tue, 28 Jun 2011 11:27:00 -0700

I use this blog to write about things that interest me. I've been growing ChallengeWave.com for the last few years and lately I'm interested in doing the right things to see our company grow. ChallengeWave is a service to help employees start and stick with healthier lifestyles. Healthier employees are less expensive and more productive that less healthy employees. ChallengeWave works by giving tools to all users to build an online community providing entertainment and accountability in shared lifestyle goals. Many people over the years have been helped by my technical writings at nodans.com and have been kind enough to share their gratitude. These days, the majority of what I'm learning has to do with founding and growing a health technology related start up and I'd like to be able to help others with entrepreneurial tendencies. I help other small companies from time to time with their growing pains. I'm fortunate enough to have several world class business mentors to help me with the growing pains of ChallengeWave. If you have specific things you would like me to write about, please send me a message. As always, you are free to agree or disagree with my opinions and to tell me all about it in the blog comments.

If you are interested in a demonstration of ChallengeWave for your organization, let us know.

Job Opportunity Near Boston for Mid-Level CF Developer

Wed, 13 Apr 2011 10:22:00 -0700

I'm posting this job opportunity for a friend. This opportunity is for a mid-level ColdFusion developer who is entrepreneurially minded and wants to be a key member of a growing and exciting team. Slackers need not apply! If you are interested, Contact Me with your resume text and contact info and I'll pass the information on to the right person.

Job Description

We need a mid-level programmer to aid in the development on our Coldfusion platform. The system was developed in 2005 as a Content Management, Ecommerce, and Ticketing system. The Company has been growing very quickly over the past 2 years, with explosive growth in 2010. In order for the Company to continue to grow they must increase their efficiency and build more automated processes into the platform. This programmer will report to the team lead and will be responsible for delivering well-written, quality software as a team member both on time and within scope.

Responsibilities

Function both independently as well as within a team, participating in on-going prioritization, management, development, testing and delivery of internal and external-facing web applications
Consult with management to identify requirements and priorities for web product development, as well as opportunities to improve Paid's performance of this function. Administer, tune and troubleshoot any of the applications in our web applications portfolio
Estimate, justify, and communicate budget requirements for projects
Aid in post-project documentation
Keep abreast of industry trends and developments. Research new technologies and provide recommendations

MINIMUM QUALIFICATIONS

3+ years experience with ecommerce platforms & content management systems
Experience dealing with large-scale order processing and manifesting systems a plus
Direct experience in high-volume, large-scale, web-based applications
3+ years building web applications in a code managed, multi-tier development environment with process release management in place
1+ years experience with SQL Server specifically, including experience with: Relational Database design, along with creation of stored procedures and user-defined functions
3+ years experience with ColdFusion specifically, including experience with: Coldfusion specific development - CFCs, custom tags
Understanding of ColdFusion Server development environment (mappings, application architecture, variable scoping, client/session management, error handling, transactions, etc.)
Proficiency with Subversion
Ability to handle multiple competing priorities in a fast-paced environment
Position available in our new Westborough, MA offices. While the role will require interaction with the actual day-to-day processes and individuals performing those tasks, we are flexible and allow for day(s) of telecommuting per week.

Direct candidates only at this time. (No agencies please)

Filter Directory Contents By Multiple File Extensions

Thu, 23 Dec 2010 08:56:00 -0700

Query of Queries really helped me out this morning. The Model-Glue team is working on a ColdFusion Builder extension and we need to get a list of files to inspect. There could be many types of files in a project that aren't relevant to our purposes and I wanted to filter only the ones I need. My Directory query contained lots of SVN specific files, which make for a good example of what we want to filter, before doing our inspection work.

Original Directory Query

What we need in this case is to filter anything that isn't a CFM, CFC or XML file. The CFDirectory tag will allow only a single filter, so what do we do? In ColdFusion, I would use listlast( filename, ".") to look at the file extension but that would mean I'd have to unpack the query and either make a new one, or pack it in another datastructure. Waste of code, I tell ya. I ended up using Query of Queries and a LIKE statement to filter.

New Directory Query

The Code I Used for the Query of Queries



	SELECT * 
	FROM arguments.sourceDirectoryQuery
	WHERE lower(NAME) LIKE '%.cfm'
		OR lower(NAME) LIKE '%.cfc'
		OR lower(NAME) LIKE '%.xml'

The secret sauce is the lower() function on NAME, and the LIKE conditional with a wildcard % operator. I found this through the Query of Queries documentation and I also learned about a few other wildcard operators as well.

List of Wildcard Operators Supported In Query of Query LIKE conditional

The underscore (_) represents any single character.
The percent sign (%) represents zero or more characters.
Square brackets ([ ]) represents any character in the range.
Square brackets with a caret [^] represent any character not in the range.
All other characters represent themselves.

Simple and maintainable. Just how I like it. Thanks #ColdFusion!