Click here to close now.

Welcome!

Apache Authors: Roger Strukhoff, XebiaLabs Blog, Yeshim Deniz, Pat Romanski, AppDynamics Blog

Related Topics: ColdFusion

ColdFusion: Article

Back to Basics: When sites run into trouble, it's usually the database that's the problem, not ColdFusion

Back to Basics: When sites run into trouble, it's usually the database that's the problem, not ColdFusion

[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")>
<cfquery name="courses" datasource="#application.dsn#">
SELECT * FROM COURSES
ORDER BY COURSE
</cfquery>

<cflock scope="application" type="exclusive" timeout="10">
<cfset application.courses = courses>
</cflock>
</cfif>

(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">
<cfoutput query="application.courses">
<option #iif(ListFind("#current_vals#",courses_id),
DE("selected"), DE(""))# value="#courses_id#">#course#
</cfoutput>
</cfselect>

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
(COURSES_ID, SCHOOLS_ID)
SELECT COURSES_ID, #form.SCHOOLS_ID#
FROM COURSES
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.

Resources

  • To download <CF_SWAPBOX>: www.articlemanager.com/download.cfm
  • A good guide to relational database design: Hernandez, M.J. (1997). Database Design for Mere Mortals. Addison-Wesley.
  • More Stories By Tom Peer

    Tom Peer has been in electronic publishing of one sort or another for ten years, including a stint as manager of New Scientist Online (www.newscientist.com). He specializes in taking printed publications online and has recently completed the online edition of The World Handbook of Stock Exchanges (www.exchange-handbook.com).

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    @ThingsExpo Stories
    Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
    For IoT to grow as quickly as analyst firms’ project, a lot is going to fall on developers to quickly bring applications to market. But the lack of a standard development platform threatens to slow growth and make application development more time consuming and costly, much like we’ve seen in the mobile space. In his session at @ThingsExpo, Mike Weiner is Product Manager of the Omega DevCloud with KORE Telematics Inc., will discuss the evolving requirements for developers as IoT matures and conduct a live demonstration of how quickly application development can happen when the need to comply...
    Container frameworks, such as Docker, provide a variety of benefits, including density of deployment across infrastructure, convenience for application developers to push updates with low operational hand-holding, and a fairly well-defined deployment workflow that can be orchestrated. Container frameworks also enable a DevOps approach to application development by cleanly separating concerns between operations and development teams. But running multi-container, multi-server apps with containers is very hard. You have to learn five new and different technologies and best practices (libswarm, sy...
    SYS-CON Events announced today that DragonGlass, an enterprise search platform, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. After eleven years of designing and building custom applications, OpenCrowd has launched DragonGlass, a cloud-based platform that enables the development of search-based applications. These are a new breed of applications that utilize a search index as their backbone for data retrieval. They can easily adapt to new data sets and provide access to both structured and unstruc...
    Converging digital disruptions is creating a major sea change - Cisco calls this the Internet of Everything (IoE). IoE is the network connection of People, Process, Data and Things, fueled by Cloud, Mobile, Social, Analytics and Security, and it represents a $19Trillion value-at-stake over the next 10 years. In her keynote at @ThingsExpo, Manjula Talreja, VP of Cisco Consulting Services, will discuss IoE and the enormous opportunities it provides to public and private firms alike. She will share what businesses must do to thrive in the IoE economy, citing examples from several industry sector...
    With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo in 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 change in personal an...
    The security devil is always in the details of the attack: the ones you've endured, the ones you prepare yourself to fend off, and the ones that, you fear, will catch you completely unaware and defenseless. The Internet of Things (IoT) is nothing if not an endless proliferation of details. It's the vision of a world in which continuous Internet connectivity and addressability is embedded into a growing range of human artifacts, into the natural world, and even into our smartphones, appliances, and physical persons. In the IoT vision, every new "thing" - sensor, actuator, data source, data con...
    IoT is still a vague buzzword for many people. In his session at @ThingsExpo, Mike Kavis, Vice President & Principal Cloud Architect at Cloud Technology Partners, discussed the business value of IoT that goes far beyond the general public's perception that IoT is all about wearables and home consumer services. He also discussed how IoT is perceived by investors and how venture capitalist access this space. Other topics discussed were barriers to success, what is new, what is old, and what the future may hold. Mike Kavis is Vice President & Principal Cloud Architect at Cloud Technology Pa...
    Disruptive macro trends in technology are impacting and dramatically changing the "art of the possible" relative to supply chain management practices through the innovative use of IoT, cloud, machine learning and Big Data to enable connected ecosystems of engagement. Enterprise informatics can now move beyond point solutions that merely monitor the past and implement integrated enterprise fabrics that enable end-to-end supply chain visibility to improve customer service delivery and optimize supplier management. Learn about enterprise architecture strategies for designing connected systems tha...
    There's Big Data, then there's really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. In her session at Big Data Expo®, Hannah Smalltree, Director at Treasure Data, discussed how IoT, Big Data and deployments are processing massive data volumes from wearables, utilities and other machines...
    SYS-CON Events announced today that the "First Containers & Microservices Conference" will take place June 9-11, 2015, at the Javits Center in New York City. The “Second Containers & Microservices Conference” will take place November 3-5, 2015, at Santa Clara Convention Center, Santa Clara, CA. Containers and microservices have become topics of intense interest throughout the cloud developer and enterprise IT communities.
    Buzzword alert: Microservices and IoT at a DevOps conference? What could possibly go wrong? In this Power Panel at DevOps Summit, moderated by Jason Bloomberg, the leading expert on architecting agility for the enterprise and president of Intellyx, panelists will peel away the buzz and discuss the important architectural principles behind implementing IoT solutions for the enterprise. As remote IoT devices and sensors become increasingly intelligent, they become part of our distributed cloud environment, and we must architect and code accordingly. At the very least, you'll have no problem fil...
    SYS-CON Events announced today that MetraTech, now part of Ericsson, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Ericsson is the driving force behind the Networked Society- a world leader in communications infrastructure, software and services. Some 40% of the world’s mobile traffic runs through networks Ericsson has supplied, serving more than 2.5 billion subscribers.
    The 4th International Internet of @ThingsExpo, co-located with the 17th International Cloud Expo - to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA - announces that its Call for Papers is open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
    The 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal today!
    Since 2008 and for the first time in history, more than half of humans live in urban areas, urging cities to become “smart.” Today, cities can leverage the wide availability of smartphones combined with new technologies such as Beacons or NFC to connect their urban furniture and environment to create citizen-first services that improve transportation, way-finding and information delivery. In her session at @ThingsExpo, Laetitia Gazel-Anthoine, CEO of Connecthings, will focus on successful use cases.
    The explosion of connected devices / sensors is creating an ever-expanding set of new and valuable data. In parallel the emerging capability of Big Data technologies to store, access, analyze, and react to this data is producing changes in business models under the umbrella of the Internet of Things (IoT). In particular within the Insurance industry, IoT appears positioned to enable deep changes by altering relationships between insurers, distributors, and the insured. In his session at @ThingsExpo, Michael Sick, a Senior Manager and Big Data Architect within Ernst and Young's Financial Servi...
    The recent trends like cloud computing, social, mobile and Internet of Things are forcing enterprises to modernize in order to compete in the competitive globalized markets. However, enterprises are approaching newer technologies with a more silo-ed way, gaining only sub optimal benefits. The Modern Enterprise model is presented as a newer way to think of enterprise IT, which takes a more holistic approach to embracing modern technologies.
    17th Cloud Expo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterprises are using some form of XaaS – software, platform, and infrastructure as a service.
    One of the biggest impacts of the Internet of Things is and will continue to be on data; specifically data volume, management and usage. Companies are scrambling to adapt to this new and unpredictable data reality with legacy infrastructure that cannot handle the speed and volume of data. In his session at @ThingsExpo, Don DeLoach, CEO and president of Infobright, will discuss how companies need to rethink their data infrastructure to participate in the IoT, including: Data storage: Understanding the kinds of data: structured, unstructured, big/small? Analytics: What kinds and how responsiv...