ColdFusion Muse

Clustered Indexes Mia Culpa

I have for years espoused the benefits of clustered indexes on MS SQL. Unlike a regular index a "clustered" index represents the actual sort order of the table. It is, therefore, the fastest available type of index. It is my view that some thought should be given to which columns are added to the clustered index. Please note, any indexing plan should include performance metrics coupled with experience. Please don't think I am recommending wholesale changes to any given schema. Having said that, a misunderstanding or misuse of indexing is the one of the most common cause of performance related problems. Now back to our discussion of clustered indexing.

SSIS and DTS - Each Has Uses

You probably know that Microsoft replaced the venerable DTS with something called "SSIS" - which I gather stands for "SQL Server Integration Services". SSIS is immensely powerful and comes with a full featured scripting language and development environment that uses Visual Studio. Practically any kind of data migration and transformation is possible with SSIS. Unfortunately SSIS is also dizzyingly more complicated than the tried and true "Data Transformatin Services" (DTS). In SSIS I have trouble simply finding the list of tables and columns let alone doing transformations. For simple, one time migration tasks it is like using a 5 horsepower tiller to plow up your house plants.

Recently I was moving large datasets from an MS SQL 2000 (32bit) server to an MS SQL 2005 (64 bit) server and discovered that the SSIS package was importing dates incorrectly. It was somehow transforming them into completely different dates (probably due to a format difference or a difference in the way dates are stored). In addition the SSIS wizard did not automatically check the box for "enable identity insert". You might recall that DTS by default checks this flag for any table using the Identity feature. If you create your tables with the Identity property set ahead of time the DTS import will automatically work correctly without the need to edit the import properties of each table. In SSIS however, I have to go into the properties of each table in the wizard and specifically check the "enable identity insert" checkbox.

My brute force solution to these irritaing issues with SSIS is simple. Instead of "importing" using SSIS I "export" using DTS. This is my rule of thumb (at least until I can get SSIS to sing a new tune) - If you are doing straight forward migrations from SQL 2000 to SQL 2005 I recommend that you stick with DTS and keep SSIS for more complex integration needs. If you are interested in integrating the DTS wizard directly into the Server Management Studio, read on:

Memorial Day in the Nation's Capital

It's campaign season and the country is abuzz with speculation about our next president. The air waves are humming, the blogosphere is awash with sound bites and pithy slogans alongside lengthy diatribes advocating this candidate or that candidate. It seems we are destined to make history no matter who we elect. Now before your blood-pressure starts racing let me assure you that this is not a political post. I only want to point out that during this cycle in our communal life as a nation we get pretty jaded. Our eyes glaze over at the latest political ad and we have long since stopped giving any politician the benefit of the doubt. Experience has taught us that the business of getting elected is a dirty one, and the dirt rubs off on everyone... or at least so I thought. Last week I was in DC, speaking and attending Webmaniacs at the Carnegie Center, and I had an experience that made me both proud of my country and ashamed of my cynicism.

Webmaniacs - Cryptography and Dentistry

On Tuesday I took in a workshop on Cryptography by Dean Saxe. Dean is an impressive character with a head stuffed full of knowledge and spilling out everywhere. He obviously knew what he was talking about. As a topic, cryptography is so impossibly complicated and intricate that he could not do it justice in a 50 minute session. Most discussions about cryptography center around keys, algorithms and best practices - and this was no exception. Dean recommended against relying on CF's own encrypt and decrypt functions for anything but the most rudimentary encryption. In fact, he probably didn't even go that far. That tidbit of advice is common from almost every security pro I have ever heard mention the subject. When it came to discussing keys it was like a trip to the dentist.

Webmaniacs Live - Mike Brunt and CF Server Tuning

CF Optimization Guru Mike Brunt gave an awesome presentation on tuning ColdFusion. In a short 50 minutes he covered such topics as JVM configuration, multi-server install and load testing. It was an excellent overview. One recommendation (that he made in is lyric British access) was to install See Fusion or Fusion Reactor instead relying on the built in monitor. His take was that, because the monitor is running under ColdFusion, it is too easily become affect itself by performance - or perhaps could become part of the problem instead of being part of the solution.

I had thought, based on a presentation by Adam Lehman on CF 8 a year ago, that the monitor ran in a different JVM space than CF. One of the things I have noticed with the monitor always shows a Flex gateway thread in the request monitoring. That thread is the monitor itself. So I'm thinking Mike might be right. Perhaps the server monitor does exist "inside" of ColdFusion. I like the new server monitor and I've found it useful. But I really like SeeFusion's default view which shows requests and visuals of the heap on one page. I think it is a better aggregate of the things I want to know immediately when I'm looking at a performance problem.

Verbose Garbage Collection

Another excellent tip is a "how to" on enabling verbose garbage collection. This is easy to do. You simply add the following arguments to the JVM.config file and restart ColdFusion.

The information is going to be stored in a log file in the default logs directory (like /runtime logs). How would you use this? One of the best ways to use it is to figure out when new generation collection is going on, when tenure (old "stop the world") collection is going on, and how much data is going from new to old. You can get a good picture of what is going on by examining this log information and using your imagination. Check out Mike's blog on GC tuning for a good outline of the process.

Webmaniacs Live - CF, RAD and OOP

I'm sitting through a session by Peter Bell of System Forge fame. He's talking about frameworks and OOP. His take is somewhat unique. Unlike many of the speakers I've heard on this topic (who shall remain nameless but their first name is usually Polly-Anna), he is giving us some real world work arounds and tips on when to break and when not to break framework rules. It's practical and very useful. Kudos to him.

Webmaniacs Live - Ben Forta Keynote, Monday

I'm here at Webmaniacs soaking in knowledge from various CF luminaries. This morning, a harried but erudite Ben Forta gave us a very thin sketch of Centaur (the upcoming CF 9) and an overview of the under utilized but extremely powerful Live Cycle Data Services (LCDS - and no, it's not a denomination). I don't suppose I ever grasped the nuances between remote object calls from flex (flash remoting and "flexgateway") and integration with data services.

I always knew that the "express" version of LCDS was limited to a single processor. But I had assumed (wrongly as it turned out) that LCDS was really the new label for the venerable old "flash remoting" - meaning the translation engine for AMF3 requests. What I had not realized was that the ES version allows for messaging and data synchronization services "out of the box" without a 10 thousand dollar license upgrade. While the processor limitation is significant in some cases where the load is expected to be heavy, in many cases it is a tolerable condition. Consider, for example, a typical intranet application. Even with a few hundred users a single dual or quad core processor would no doubt be enough to handle expected load.

Of course it's also possible that I missed some important cost aspect of ES - or that not all the features are enabled on CF standard and require enterprise. That gives me a bit more digging to do. Perhaps I'll look up Ben Forta and ask him for some clarification. The problem is that his face glows so brightly I have to shield my eyes (See Exodus 34).

Disabling Backslash Escaping in MySQL

For muse readers who read my previous post on SQL injection examples that use character rather than numeric fields, I offer this tip I picked up on CF-Talk from Azadi Saryev. It appears you can disable the ability to escape special characters using the backslash. Here is the exact note from Azadi.