|By Tom Peer||
|April 8, 2002 12:00 AM EDT||
[Ed. Note: We've run articles on this in the past, but - we can never say it enough!]
Cutting ColdFusion code is fun, and therein lies ColdFusion's biggest problem. It's too easy to get carried away writing code to do things that should be happening in the database itself - or often shouldn't be happening at all. It's also just too easy to use. You can get up and running far too quickly, and get into trouble even quicker. Sometimes experienced developers get carried away trying to do too much; beginners often start to run before they can walk.
When I came across a site where a simple query of news stories was taking a quarter of a second, I wasn't sure that writing their own object-caching mechanism in ColdFusion was entirely the right approach. I called a timeout - a morning to go over the basics. One simple run of the Query Analyzer diagnosed the problem: a date field was being used in a sort clause without an index.
The query time was reduced to around 15ms - around 6ms database time plus the ODBC overhead. If you use SQL Server, the Query Analyzer is a tool you should learn to love. As a basic rule, any query that does more than select a single record using a primary as the criterion will probably benefit from some sort of index.
Using it couldn't be simpler - simply open a window, enter your SQL query text and then press Ctrl + I to run an index analysis (see Figure 1). In SQL Server versions 7 and up it's become an invaluable tool. You can also take a look at the path analysis and turn on the "Show stats time" option in the Query->Current Connection Options dialog.
It's useful, but in a publishing application there's only so much you can do to tune database performance. You can get too worried about optimization - you're pushing about large text strings and that takes time. Hard-core optimization is more relevant in financial and engineering applications where the database is summarizing and calculating vast numbers of small records.
You're more likely to achieve better results if you concentrate your efforts on your ColdFusion code. ColdFusion offers two powerful ways of caching queries: the "cached within" feature and caching in a shared scope. Many developers aren't aware that you can cache any ColdFusion object in the application scope; structs and queries can just as easily be cached as scalar variables. You can cache queries that are unlikely to change in application.cfm:
<cfif NOT IsDefined("application.courses")>
<cflock scope="application" type="exclusive" timeout="10">
<cfquery name="courses" datasource="#application.dsn#">
SELECT * FROM COURSES
ORDER BY COURSE
<cfset application.courses = courses>
<cflock scope="application" type="exclusive" timeout="10">
(The locking after the query is vitally important; see article 20370 in the ColdFusion knowledgebase.)
By putting the actual query and assignment above into a separate include file and calling it whenever the table is affected, you can create your own full-fledged cache server. Alternatively, you can convert the query to a struct keyed by the primary key of the database table and put the struct into the application scope. There are custom tags in the developer's exchange to do this, but it's almost quicker to do it yourself.
Don't be scared of using the application scope. Memory's cheap and downtime isn't. If your entire database is only half a gigabyte and you have 2GB in your server, why not load it into the application scope?
Usually it's best to mix this approach with the cached within approach. Say you have a database of two thousand suppliers, listed by region, together with the services they offer. You want to use the "startrow, endrow" parameters of <CFOUTPUT> to show fifty at a time, but this means fetching the entire listing in the query, which takes nearly 150ms.
In this case you could put the fields you need for the listing in an application-level query and then, on a separate "view" page, select the main details in a separate query that you apply the cached within parameter to. This parameter of <CFQUERY> is one of ColdFusion's best features. It can send your sysadmin into a blind panic when he or she sees CF taking up 800MB ("It's got a leak!"), but it's so simple to use compared to rival offerings.
One technique I use all the time, caching formatted output, is a little more contentious. Say, for instance, you have fields in your supplier database for TEL, FAX, EMAIL, WEB - any of which may be blank or need formatting. In a listing page of fifty records this can take time. On one of my pages it was taking too much time just to do a simple list. My solution? Rather than copy the whole query into the application scope, I created a new query (see the Query-New() function) with fields for the primary key and formatted display.
Even more contentious: sometimes I put the formatted output back into the database. I do this all the time where the database is storing XML and I need to convert to HTML. Converting a page on the fly takes around 400ms (assuming XSL doesn't get stuck in an endless loop) and displaying a cached query takes less than 1ms.
This may sound like a lash-up (I less than impressed an old college friend who followed a slightly more formal career), but this is how the really expensive application servers work. Sure, they won't store large chunks of HTML in the database; they'll store serialized Java objects, but at least the HTML will still work when somebody changes the code. (Application servers store objects in a format that depends on the object definition [your code]. When you change that definition, the entire object store can be rendered unusable. It's very problematic, and I've seen plenty of implementations of expensive object servers where almost all the data is stored in plain SQL tables.)
Note: With reference to Ben Forta's February CFDJ article ("Faster and Safer Database Queries," Vol. 4, issue 2), you can't cache a query that uses a QUERYPARAM tag. You can, though, achieve the same security against malicious URLs by ensuring that all your tables have a numeric primary key and then validating any variables used in SQL queries with an "IsNumeric()" test. It's one more reason why all database tables should have a numeric primary key.
No matter how cheap memory gets, you do still need to apply a certain amount of discipline to your coding. One of the most common causes of database problems is fetching too much data from the database. Using derived tables and other advanced SQL constructs, you should be able to avoid fetching vast amounts of query data and looping over it.
A typical derived table query looks something like this:
SELECT TOP 1 A.NEWS_ID, A.HEADLINE, A.STORY FROM NEWS AS A,
(SELECT NEWS_ID FROM NEWS_CATEGORIES_JOIN
WHERE CATEGORY_ID IN (#cat_list#)) AS B
WHERE A.NEWS_ID = B.NEWS_ID
ORDER BY A.PUBDATE DESC
This will select a record from a correctly built many-to-many join (of which much more later) when a GROUP BY can't be used (i.e., with long text fields). This is just a simple example (a subselect could be used equally well here and run as quickly); derived tables are most effective when some sort of calculation needs to be performed, perhaps a summation or a count. If you find the idea of working with tables that don't exist a little frightening, you can often achieve the same affect by using a view, a stored query that can be queried and joined just like a standard table (see Figure 2).
Often underused, views are a great tool for making both your SQL and your CF code as simple as possible. They're second nature to anyone who's come to SQL Server from Access (where they're known as queries), but experienced programmers who have been using old-style databases often prefer to do everything in one query. Using views is usually quicker than performing joins or summaries at runtime. I saw a job advertisement recently that said "must be able to write join queries without using visual tools" and I thought: "Why?" There's no benefit, save a few techy kudos.
If you do need to loop over entire tables, try to avoid fetching the data into ColdFusion. Database cursors (described by Ian Rutherford in "Using MS-SQL Stored Procedures with ColdFusion," CFDJ Vol. 4, issue 2) will run much faster than dragging data into ColdFusion, processing it, and then running an update. It also won't take up a ColdFusion thread or valuable network capacity.
Speed Isn't Everything
As important as optimization is, sometimes there's too much focus on saving a millisecond here or cutting down white space there at the expense of properly structured code and databases. The key to software engineering, as in all engineering, is to keep it simple, and the main aim in a ColdFusion development should be no different from any other project: to keep the code to a minimum.
It's this discipline of keeping things simple that's the most important part of building reliable and scalable sites. It may seem strange, but sometimes code that runs slower can make for better Web sites. Take, for example, what for me is the litmus test of good database design: the use of many-to-many joins. A typical database application might be a listing of schools and the courses they offer. The original data, as always, is in Excel, looking something like the example in Figure 3. The experienced developer will recognize the need for a separate table of courses and a third "join" table indicating which schools have which courses.
The temptation, though, is simply to import it into the database and use it as is, relying on ColdFusion's list handling to determine which courses are offered by which schools. That would be bad, but what would be even worse would be some attempt to code the courses offered as separate database fields (see Figure 4).
Why you shouldn't do this is hard to explain, although it's simple: in a SELECT query it'll run two to three times as fast as using a many-to-many join, and it's easy to program using ColdFusion Studio. You just point the wizards at the right table and five minutes later you're done. But by doing something like this, you have hard-coded "data" into the "structure" of your system. To build truly scalable systems, you need to keep a very definite division between the structure and the data. The Golden Rule is this: if your ColdFusion code is completely separate from your database, then your system will scale. If you've hard-coded data as fieldnames - you just can't do this - your data is inextricably tied up with your application.
Using ColdFusion, it isn't always possible to adhere to the strict three-tier model (database, application, presentation), but creating a division between the structure and the data is still a must. You should be able to edit the data directly without using your ColdFusion application and still see the changes reflected on your site.
So, for instance, were you to add a new course to the table of courses, your site should display that course wherever relevant. Often this doesn't happen; data fields are hard-coded and the layout of the pages is dependent on the data structure.
Sometimes you might use the ColdFusion application to enforce certain database rules - for instance, that a salesperson can have a maximum of only 10 customers. This is fine. In fact, database theorists call such a rule an application rule for the very reason that it doesn't affect the underlying data. If a salesperson had 11 clients it wouldn't affect the data in any way. But if the only way you could add or remove clients was through the ColdFusion-based Web interface, then something would be amiss.
A simple example like the one shown in Figure 4 doesn't really illustrate the necessity of relational design. Once you're bug-free, the example will run faster than a relational design, and it will scale. It's simple and, above all, that's what matters.
You wouldn't have to get much more complicated before a flat file system like this would start to break down, though. It's not just raw performance that's the issue - the extra complexity of the code and the difficulties in making changes will all contribute in different ways to a lack of scalability. If a system relies on a single overworked developer to make simple changes, then it won't be scalable no matter how optimized the code.
As with any rule, the one about keeping the data separate from the code is easily broken. It's quite possible to make such a convoluted mess of triggers, derived tables, cursor loops, and other database constructs that no amount of processing power will save you. Keep it simple, though, and it will scale.
Easy in Theory
Although it makes things simpler in the database system, relational design can, if you're not careful, make things more complicated in the Web application. Using separate tables for many-to-many joins requires extra INSERT queries, and SELECT functions often have to employ grouping, subselects, or derived tables.
Perhaps the principal reason many-to-many joins don't get used is that most database editing systems don't have a standard component for implementing them. Access doesn't (you have to create a subform or use some VB), and if you're using HTML forms, you unfortunately can't use the "query" attribute of <CFSELECT> as it doesn't support multiple values for "selected". You can, however, use a simple loop:
<cfselect name="course_id" multiple="Yes">
DE("selected"), DE(""))# value="#courses_id#">#course#
Alternatively, you can use checkboxes; another effective device for editing many-to-many joins is a swap box. It's especially useful when you have many possible options to choose from, and it's more intuitive than multiple select boxes where the user has to control-click (see Figure 5).
There are several custom tags to do this in the developer's exchange, and my own <CF_SWAPBOX> is available from my site (see "Resources" section). It has almost identical syntax to CFSELECT, and also supports multiple values for "selected".
One thing you can't do much about is the complexity of inserting and updating the "join" tables (the third table used for many-to-many joins). You can cut down the number of queries needed by using an "insert into" with a subselect, for example:
INSERT INTO COURSES_SCHOOLS_JOIN
SELECT COURSES_ID, #form.SCHOOLS_ID#
WHERE COURSES_ID IN (#form.courses_id#)
but this won't run any faster than using multiple inserts in the same query. Nor can you run this as a stored procedure (you can't pass a list of integer primary keys as a VARCHAR and then use it in a subselect), nor can you use the <CFQUERYPARAM> tag. It's best just to run a separate insert for each row of the join table.
Perhaps, though, the biggest obstacle to correct database design isn't a technical one, but the very familiar problem of client demands. Before you can build a sound database, you need to convince the client that that's what's needed. The temptation for clients is to think of their Web site like a magazine: "I want to put that over there, that over there." Convincing clients they need to "think data" is the hardest part of any project.
In the most difficult project I ever worked on, the editor of the site was so incensed at no longer being able to work on the HTML pages himself that he got hold of the administrator password and replaced all the ColdFusion code with HTML pages he'd "saved as" from his browser. All the dynamic functions duly broke, but because the site still looked the same as it did before, the client never really understood what had happened. As far as he was concerned, my code didn't work. The editor was there to edit pages and that was what he was doing.
The concept of changing a page not by editing the page itself, but by editing a separate data system, is a surprisingly difficult conceptual hurdle for many people. It's such a simple concept that it's easy to forget how revolutionary it is. Those who have come from a print media background aren't going to buy into the database idea until they've had that hallelujah moment and seen just what an impact information systems can have. Until that time they're going to find using a database frustrating and too inflexible.
If your client isn't of the database mind-set when you start a project, it's vital that you help him or her see the light before you start programming. Otherwise you'll end up putting in hooks and hacks to try and satisfy client demands ("sometimes we might want to do something different"). They'll come back to haunt you.
I've found that the most important ingredient of a successful site isn't the design or my code, but getting the client used to the idea of editing a database. A client who comes to you not with ideas for "new pages" but a "new section" and a sketch of the underlying data is worth more than any amount of milliseconds saved from a database query.
I recently attended and was a speaker at the 4th International Internet of @ThingsExpo at the Santa Clara Convention Center. I also had the opportunity to attend this event last year and I wrote a blog from that show talking about how the “Enterprise Impact of IoT” was a key theme of last year’s show. I was curious to see if the same theme would still resonate 365 days later and what, if any, changes I would see in the content presented.
Nov. 27, 2015 11:00 PM EST Reads: 411
Cloud computing delivers on-demand resources that provide businesses with flexibility and cost-savings. The challenge in moving workloads to the cloud has been the cost and complexity of ensuring the initial and ongoing security and regulatory (PCI, HIPAA, FFIEC) compliance across private and public clouds. Manual security compliance is slow, prone to human error, and represents over 50% of the cost of managing cloud applications. Determining how to automate cloud security compliance is critical to maintaining positive ROI. Raxak Protect is an automated security compliance SaaS platform and ma...
Nov. 27, 2015 06:00 PM EST Reads: 413
We all know that data growth is exploding and storage budgets are shrinking. Instead of showing you charts on about how much data there is, in his General Session at 17th Cloud Expo, Scott Cleland, Senior Director of Product Marketing at HGST, showed how to capture all of your data in one place. After you have your data under control, you can then analyze it in one place, saving time and resources.
Nov. 27, 2015 04:00 PM EST Reads: 174
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi’s VP Business Development and Engineering, explored the IoT cloud-based platform technologies driving this change including privacy controls, data transparency and integration of real time context with p...
Nov. 27, 2015 02:00 PM EST Reads: 411
The Internet of Things (IoT) is growing rapidly by extending current technologies, products and networks. By 2020, Cisco estimates there will be 50 billion connected devices. Gartner has forecast revenues of over $300 billion, just to IoT suppliers. Now is the time to figure out how you’ll make money – not just create innovative products. With hundreds of new products and companies jumping into the IoT fray every month, there’s no shortage of innovation. Despite this, McKinsey/VisionMobile data shows "less than 10 percent of IoT developers are making enough to support a reasonably sized team....
Nov. 27, 2015 12:00 PM EST Reads: 464
Internet of @ThingsExpo, taking place June 7-9, 2016 at Javits Center, New York City and Nov 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 18th International @CloudExpo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo New York Call for Papers is now open.
Nov. 27, 2015 12:00 PM EST Reads: 542
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York and Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound cha...
Nov. 27, 2015 11:45 AM EST Reads: 538
Just over a week ago I received a long and loud sustained applause for a presentation I delivered at this year’s Cloud Expo in Santa Clara. I was extremely pleased with the turnout and had some very good conversations with many of the attendees. Over the next few days I had many more meaningful conversations and was not only happy with the results but also learned a few new things. Here is everything I learned in those three days distilled into three short points.
Nov. 27, 2015 11:00 AM EST Reads: 315
As organizations realize the scope of the Internet of Things, gaining key insights from Big Data, through the use of advanced analytics, becomes crucial. However, IoT also creates the need for petabyte scale storage of data from millions of devices. A new type of Storage is required which seamlessly integrates robust data analytics with massive scale. These storage systems will act as “smart systems” provide in-place analytics that speed discovery and enable businesses to quickly derive meaningful and actionable insights. In his session at @ThingsExpo, Paul Turner, Chief Marketing Officer at...
Nov. 27, 2015 10:45 AM EST Reads: 399
DevOps is about increasing efficiency, but nothing is more inefficient than building the same application twice. However, this is a routine occurrence with enterprise applications that need both a rich desktop web interface and strong mobile support. With recent technological advances from Isomorphic Software and others, rich desktop and tuned mobile experiences can now be created with a single codebase – without compromising functionality, performance or usability. In his session at DevOps Summit, Charles Kendrick, CTO and Chief Architect at Isomorphic Software, demonstrated examples of com...
Nov. 27, 2015 10:45 AM EST Reads: 384
In his keynote at @ThingsExpo, Chris Matthieu, Director of IoT Engineering at Citrix and co-founder and CTO of Octoblu, focused on building an IoT platform and company. He provided a behind-the-scenes look at Octoblu’s platform, business, and pivots along the way (including the Citrix acquisition of Octoblu).
Nov. 27, 2015 10:00 AM EST Reads: 491
In his General Session at 17th Cloud Expo, Bruce Swann, Senior Product Marketing Manager for Adobe Campaign, explored the key ingredients of cross-channel marketing in a digital world. Learn how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects).
Nov. 27, 2015 09:15 AM EST Reads: 292
The Internet of Everything is re-shaping technology trends–moving away from “request/response” architecture to an “always-on” Streaming Web where data is in constant motion and secure, reliable communication is an absolute necessity. As more and more THINGS go online, the challenges that developers will need to address will only increase exponentially. In his session at @ThingsExpo, Todd Greene, Founder & CEO of PubNub, exploreed the current state of IoT connectivity and review key trends and technology requirements that will drive the Internet of Things from hype to reality.
Nov. 27, 2015 07:45 AM EST Reads: 424
Two weeks ago (November 3-5), I attended the Cloud Expo Silicon Valley as a speaker, where I presented on the security and privacy due diligence requirements for cloud solutions. Cloud security is a topical issue for every CIO, CISO, and technology buyer. Decision-makers are always looking for insights on how to mitigate the security risks of implementing and using cloud solutions. Based on the presentation topics covered at the conference, as well as the general discussions heard between sessions, I wanted to share some of my observations on emerging trends. As cyber security serves as a fou...
Nov. 27, 2015 07:30 AM EST Reads: 323
Continuous processes around the development and deployment of applications are both impacted by -- and a benefit to -- the Internet of Things trend. To help better understand the relationship between DevOps and a plethora of new end-devices and data please welcome Gary Gruver, consultant, author and a former IT executive who has led many large-scale IT transformation projects, and John Jeremiah, Technology Evangelist at Hewlett Packard Enterprise (HPE), on Twitter at @j_jeremiah. The discussion is moderated by me, Dana Gardner, Principal Analyst at Interarbor Solutions.
Nov. 27, 2015 04:15 AM EST Reads: 712
Too often with compelling new technologies market participants become overly enamored with that attractiveness of the technology and neglect underlying business drivers. This tendency, what some call the “newest shiny object syndrome” is understandable given that virtually all of us are heavily engaged in technology. But it is also mistaken. Without concrete business cases driving its deployment, IoT, like many other technologies before it, will fade into obscurity.
Nov. 27, 2015 04:00 AM EST Reads: 338
With all the incredible momentum behind the Internet of Things (IoT) industry, it is easy to forget that not a single CEO wakes up and wonders if “my IoT is broken.” What they wonder is if they are making the right decisions to do all they can to increase revenue, decrease costs, and improve customer experience – effectively the same challenges they have always had in growing their business. The exciting thing about the IoT industry is now these decisions can be better, faster, and smarter. Now all corporate assets – people, objects, and spaces – can share information about themselves and thei...
Nov. 27, 2015 04:00 AM EST Reads: 230
The Internet of Things is clearly many things: data collection and analytics, wearables, Smart Grids and Smart Cities, the Industrial Internet, and more. Cool platforms like Arduino, Raspberry Pi, Intel's Galileo and Edison, and a diverse world of sensors are making the IoT a great toy box for developers in all these areas. In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists discussed what things are the most important, which will have the most profound effect on the world, and what should we expect to see over the next couple of years.
Nov. 27, 2015 02:30 AM EST Reads: 462
Discussions of cloud computing have evolved in recent years from a focus on specific types of cloud, to a world of hybrid cloud, and to a world dominated by the APIs that make today's multi-cloud environments and hybrid clouds possible. In this Power Panel at 17th Cloud Expo, moderated by Conference Chair Roger Strukhoff, panelists addressed the importance of customers being able to use the specific technologies they need, through environments and ecosystems that expose their APIs to make true change and transformation possible.
Nov. 27, 2015 02:00 AM EST Reads: 521
Microservices are a very exciting architectural approach that many organizations are looking to as a way to accelerate innovation. Microservices promise to allow teams to move away from monolithic "ball of mud" systems, but the reality is that, in the vast majority of organizations, different projects and technologies will continue to be developed at different speeds. How to handle the dependencies between these disparate systems with different iteration cycles? Consider the "canoncial problem" in this scenario: microservice A (releases daily) depends on a couple of additions to backend B (re...
Nov. 27, 2015 01:00 AM EST Reads: 431