|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.
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 19th International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world and ThingsExpo Silicon Valley Call for Papers is now open.
Jul. 25, 2016 10:00 PM EDT Reads: 2,494
The IoT is changing the way enterprises conduct business. In his session at @ThingsExpo, Eric Hoffman, Vice President at EastBanc Technologies, discussed how businesses can gain an edge over competitors by empowering consumers to take control through IoT. He cited examples such as a Washington, D.C.-based sports club that leveraged IoT and the cloud to develop a comprehensive booking system. He also highlighted how IoT can revitalize and restore outdated business models, making them profitable ...
Jul. 25, 2016 08:30 PM EDT Reads: 1,931
With 15% of enterprises adopting a hybrid IT strategy, you need to set a plan to integrate hybrid cloud throughout your infrastructure. In his session at 18th Cloud Expo, Steven Dreher, Director of Solutions Architecture at Green House Data, discussed how to plan for shifting resource requirements, overcome challenges, and implement hybrid IT alongside your existing data center assets. Highlights included anticipating workload, cost and resource calculations, integrating services on both sides...
Jul. 25, 2016 08:00 PM EDT Reads: 1,965
Big Data engines are powering a lot of service businesses right now. Data is collected from users from wearable technologies, web behaviors, purchase behavior as well as several arbitrary data points we’d never think of. The demand for faster and bigger engines to crunch and serve up the data to services is growing exponentially. You see a LOT of correlation between “Cloud” and “Big Data” but on Big Data and “Hybrid,” where hybrid hosting is the sanest approach to the Big Data Infrastructure pro...
Jul. 25, 2016 07:30 PM EDT Reads: 1,881
"We are a well-established player in the application life cycle management market and we also have a very strong version control product," stated Flint Brenton, CEO of CollabNet,, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.
Jul. 25, 2016 07:15 PM EDT Reads: 1,794
We all know the latest numbers: Gartner, Inc. forecasts that 6.4 billion connected things will be in use worldwide in 2016, up 30 percent from last year, and will reach 20.8 billion by 2020. We're rapidly approaching a data production of 40 zettabytes a day – more than we can every physically store, and exabytes and yottabytes are just around the corner. For many that’s a good sign, as data has been proven to equal money – IF it’s ingested, integrated, and analyzed fast enough. Without real-ti...
Jul. 25, 2016 07:15 PM EDT Reads: 1,004
I wanted to gather all of my Internet of Things (IOT) blogs into a single blog (that I could later use with my University of San Francisco (USF) Big Data “MBA” course). However as I started to pull these blogs together, I realized that my IOT discussion lacked a vision; it lacked an end point towards which an organization could drive their IOT envisioning, proof of value, app dev, data engineering and data science efforts. And I think that the IOT end point is really quite simple…
Jul. 25, 2016 06:30 PM EDT Reads: 979
A critical component of any IoT project is what to do with all the data being generated. This data needs to be captured, processed, structured, and stored in a way to facilitate different kinds of queries. Traditional data warehouse and analytical systems are mature technologies that can be used to handle certain kinds of queries, but they are not always well suited to many problems, particularly when there is a need for real-time insights.
Jul. 25, 2016 06:15 PM EDT Reads: 1,751
Unless your company can spend a lot of money on new technology, re-engineering your environment and hiring a comprehensive cybersecurity team, you will most likely move to the cloud or seek external service partnerships. In his session at 18th Cloud Expo, Darren Guccione, CEO of Keeper Security, revealed what you need to know when it comes to encryption in the cloud.
Jul. 25, 2016 06:00 PM EDT Reads: 2,431
We're entering the post-smartphone era, where wearable gadgets from watches and fitness bands to glasses and health aids will power the next technological revolution. With mass adoption of wearable devices comes a new data ecosystem that must be protected. Wearables open new pathways that facilitate the tracking, sharing and storing of consumers’ personal health, location and daily activity data. Consumers have some idea of the data these devices capture, but most don’t realize how revealing and...
Jul. 25, 2016 06:00 PM EDT Reads: 2,077
You think you know what’s in your data. But do you? Most organizations are now aware of the business intelligence represented by their data. Data science stands to take this to a level you never thought of – literally. The techniques of data science, when used with the capabilities of Big Data technologies, can make connections you had not yet imagined, helping you discover new insights and ask new questions of your data. In his session at @ThingsExpo, Sarbjit Sarkaria, data science team lead ...
Jul. 25, 2016 03:45 PM EDT Reads: 961
Extracting business value from Internet of Things (IoT) data doesn’t happen overnight. There are several requirements that must be satisfied, including IoT device enablement, data analysis, real-time detection of complex events and automated orchestration of actions. Unfortunately, too many companies fall short in achieving their business goals by implementing incomplete solutions or not focusing on tangible use cases. In his general session at @ThingsExpo, Dave McCarthy, Director of Products...
Jul. 25, 2016 03:30 PM EDT Reads: 1,692
"delaPlex is a software development company. We do team-based outsourcing development," explained Mark Rivers, COO and Co-founder of delaPlex Software, in this SYS-CON.tv interview at 18th Cloud Expo, held June 7-9, 2016, at the Javits Center in New York City, NY.
Jul. 25, 2016 03:00 PM EDT Reads: 1,973
WebRTC is bringing significant change to the communications landscape that will bridge the worlds of web and telephony, making the Internet the new standard for communications. Cloud9 took the road less traveled and used WebRTC to create a downloadable enterprise-grade communications platform that is changing the communication dynamic in the financial sector. In his session at @ThingsExpo, Leo Papadopoulos, CTO of Cloud9, discussed the importance of WebRTC and how it enables companies to focus...
Jul. 25, 2016 02:45 PM EDT Reads: 872
Is your aging software platform suffering from technical debt while the market changes and demands new solutions at a faster clip? It’s a bold move, but you might consider walking away from your core platform and starting fresh. ReadyTalk did exactly that. In his General Session at 19th Cloud Expo, Michael Chambliss, Head of Engineering at ReadyTalk, will discuss why and how ReadyTalk diverted from healthy revenue and over a decade of audio conferencing product development to start an innovati...
Jul. 25, 2016 02:00 PM EDT Reads: 978
Early adopters of IoT viewed it mainly as a different term for machine-to-machine connectivity or M2M. This is understandable since a prerequisite for any IoT solution is the ability to collect and aggregate device data, which is most often presented in a dashboard. The problem is that viewing data in a dashboard requires a human to interpret the results and take manual action, which doesn’t scale to the needs of IoT.
Jul. 25, 2016 01:00 PM EDT Reads: 1,936
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
Jul. 25, 2016 12:15 PM EDT Reads: 513
CenturyLink has announced that application server solutions from GENBAND are now available as part of CenturyLink’s Networx contracts. The General Services Administration (GSA)’s Networx program includes the largest telecommunications contract vehicles ever awarded by the federal government. CenturyLink recently secured an extension through spring 2020 of its offerings available to federal government agencies via GSA’s Networx Universal and Enterprise contracts. GENBAND’s EXPERiUS™ Application...
Jul. 25, 2016 12:00 PM EDT Reads: 1,826
IoT generates lots of temporal data. But how do you unlock its value? You need to discover patterns that are repeatable in vast quantities of data, understand their meaning, and implement scalable monitoring across multiple data streams in order to monetize the discoveries and insights. Motif discovery and deep learning platforms are emerging to visualize sensor data, to search for patterns and to build application that can monitor real time streams efficiently. In his session at @ThingsExpo, ...
Jul. 25, 2016 11:00 AM EDT Reads: 925
Verizon Communications Inc. (NYSE, Nasdaq: VZ) and Yahoo! Inc. (Nasdaq: YHOO) have entered into a definitive agreement under which Verizon will acquire Yahoo's operating business for approximately $4.83 billion in cash, subject to customary closing adjustments. Yahoo informs, connects and entertains a global audience of more than 1 billion monthly active users** -- including 600 million monthly active mobile users*** through its search, communications and digital content products. Yahoo also co...
Jul. 25, 2016 10:30 AM EDT Reads: 389