ColdFusion 9 Released ... and more!

And with a new beta build of the ColdFusion Builder IDE. Get them while they're hot ... and while we can still overwhelm Adobe's servers with all the concurrent downloads :)!

And a very welcome change to the CF licensing as posted by Terrance Ryan:

CF EULA

But wait ... there's more!

Flash Builder 4 Beta 2

Flex 4 SDK

Flash Catalyst Beta 2

But let's not stop there ... check out WorkflowLab, a cool looking AIR application to handle, well, workflows! Looks kind of cool

WorkflowLab

And, of course, Adobe is just getting warmed up at Max. More announcements to come shortly!

Posted via email from Craig Kaminsky's posterous

Comments

Flex-to-ColdFusion Performance

I have a general Flex-to-CF performance question to pose and would love to get some feedback from the community.

Current Behavior

I am working on an enterprise application that contains several 'screens' where users manipulate application data. There are a variety of ways in which a user manipulates the data but, for the purposes of this post, a commonly used approach entails a user dragging and dropping items to get them in a specific order.

The current design responds each time a user drops an item in a spot. When that happens, events are fired to reorder the data based on the drag-drop and immediately send this new order to the CF server to update the data in a SQL Server database.

Alternative Behavior

The alternative would be to add a button for the user to click when s/he is done ordering their items. When clicked, the appropriate events fire to record the order and send said data to the server. While dragging and dropping would remain how the user orders their data, the drop event would not fire any functions to send data to the server ... that would be reserved for the 'save' button.

Concern

There are a variety of places in the application where we send data back to the server on the fly in like manner to what I described above (Current Behavior). The concern is valid as, even without myriad concurrent users, the application is processor/server intensive.

My concern is that, as we launch and the number of concurrent users rises, accessing the application and interacting with the data in our current manner may quickly overload the ColdFusion (v8.0.1) and SQL servers (SQL Server 2008) because I can see a lot of scenarios where users are constantly maniuplating data, changing their mind about what they did, re-manipulating the data, and so on. Basically, I worry that we'll end up in a situation where we are sending 100s or 1000s of asynchronous requests to the CF/SQL servers simultaneously and will crash or greatly reduce server/application performance.

My thought was that requiring users to click a 'save' button (or whatever it may be labeled) before sending the data would alleviate some of the UI-Server communications and bandwidth. However, such a revised approach would, I think, negatively, though not significantly, affect usability.

Questions

Is it better to sacrifice a little usability in favor of reduced server traffic?

Am I off-base in my assessment of how the current behavior could affect the server?

Any and all thoughts are welcome and thanks!!

Disclaimer

While I would love to chat about the application itself, I'm under an NDA and cannot (nothing unusual there!). Also, I want to clarify that I do not think there is anything wrong with our current approach and that my noting the application is server intensive is not a knock on the system at all.

This is a really kick-ass application and I can't wait until it launches! And, I say that having had nothing to do with the conception, design, or development until this week, as I was just added to the team :).

Posted via email from Craig Kaminsky's posterous

Comments

Anonymous

Maybe make it work like autosave in gmail. Requests are queued up and pushed to the server every n seconds. before this auto-save activates, the save button is enabled. after the save occurs the save button is disabled, then re-enabled on the next change.

Mark

The best thing about the save option is that it also present a reset option. If your user plays with the order of items and then changes his mind, then choosing to not save resets the order as it was when he started.

Mark Fuqua

Craig Kaminsky

Thanks, Paul! I appreciate your response and am glad to hear that I'm not off-base in my concerns and possible alternative.

Paul Carney

This is a question we have also dealt with as we move from CF based UI to Flash using Flash Builder.

My belief is that the user should be able to manipulate items, like moving them around in a sort, without the actions being immediately stored. So, they should have to take an action to "save" what they have been playing with.

And you are correct - sending that many requests to the server as you ramp up in concurrent users will create more traffic than you really want.

Using SQL Server Triggers to Save ColdFusion

Currently, I'm working on a large application that involves a Flex UI backed by ColdFusion 8 and SQL Server 2008. The ColdFusion code consists of a large base of CFCs that run many queries (via cfquery) for all database interaction/communication (CRUD, advanced searchers, etc.). The CFCs are invoked as Remote Objects from the Flex UI. In most cases, the CFCs return large arrays of objects for Flex to use in the UI.

Since ColdFusion 8 is particularly slow at object instantiation, my concerns about the application's performance under load are great. While getting acquainted with the existing code base and project, I was tasked to create a routine that enabled an application user to rate objects in the UI and compute an aggregate ranking for these objects based on rankings from all application users. As I worked through what steps I needed to take to accomplish this, I realized that a triggers was just what I needed (it helped that I was scanning a SQL Server book at the time!).

It turns out that triggers are not difficult to write and implement and, ultimately, can really help boost ColdFusion's performance in the right situations. Thus, this exciting blog entry :).

Triggers

Many already know this but, since I didn't fully 'get' triggers, I thought a brief introduction or definition would be good. Triggers are routines that run once a specified event occurs in the database. They're great for enforcing referential integrity (i.e., ensuring you don't delete a row from a table without also deleting related foreign key entries) and minimizing work on the application server by running a range of tasks in the DB itself; rather than your web application server.

There are a two main types of triggers in SQL Server (AFTER and INSTEAD OF) but, thus far, I've only gotten into the standard trigger (AFTER). Essentially, an AFTER trigger is an event listener that, when fired, tells SQL Server to run the compiled code of your trigger.

But when does it fire? Well, you get to tell it when to fire. Typically, an AFTER trigger is fired immediately following a successful insert, update or delete on the watched table

The Problem

As I mentioned above, the current application on which I'm working has a feature that enables application users to rank objects from 0 to 10. The ranking is handled in the UI (quite nicely -- we have awesome designers and interactive folks) and, when the user has finished, s/he saves their rankings and the data is submitted to a ColdFusion via a remote object in Flex.

Where it gets 'tricky' is that with each set of rankings saved, we need to immediately update our aggregated ranking data and return those to the UI.

If I were to do this in ColdFusion alone (using CFC methods with CFQUERY tags), the workflow might be something along the lines of the following:

Save the object's rankings/ratings to the database
Grab all previous (plus the current) rankings for the current object
Insert the updated aggregated ranking data in the database
Query the database for the aggregated rankings for all objects and return to the UI

Even though this isn't a complex, uncommon, or intensive operation, it still takes up to 4 database queries.

The Solution

My solution incorporated a stored procedure and the trigger and only 1 step for ColdFusion. Here's the flow as far as ColdFusion is concerned:

Save the object rankings/ratings to the database (CFC method invokes a stored procedure on the DB server)
Stored procedure inserts, updates, or deletes some ranking data and my trigger is then fired
Aggregated rankings for all objects returned from the stored procedure and are then sent back to the UI

The Trigger

Below, the trigger I created is listening for a product_sales table to be affected in some manner (insert, update, or delete). When one of these events occur, I need to update the aggregate rank for the product that was just sold.

Our aggregate rank is calculated quite simply: add the total customer rankings for a product and divide by the number of times it has been ranked. Once calculated, we need to update another table that holds the aggregate product rankings

*Note: I had to alter my actual trigger, so the tables referenced in the example were just pulled together for the example itself (and not intended to represent an ideal or good DB design).

Following is the T-SQL for the trigger:

[code lang='sql']

CREATE TRIGGER tr_my_trigger

ON [dbo].[product_sales]

-- we're listening for any changes to the table noted above

AFTER INSERT,UPDATE

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

declare @prodid nvarchar(50), @aggregate_ranking numeric(18,0)

select @prodid = prod_id from inserted

select @aggregate_ranking = (sum(rank) / count(prod_id)) from product_customer_rankings where prod_id = @prodid

update product_ranks

set aggregate_ranking = @aggregate_ranking

where prod_id = @prodid

END

[/code]

First, we declare a trigger on the table we want to watch. We specifically created an 'AFTER' trigger for an INSERT or UPDATE event occurs on the product_sales table.

Second, we declare two local variables that the trigger needs (@prodid and @aggregate_ranking).

Third, we set the value for our first local variable, @prodid. I think this is where the trigger gets cool.

In order to successfully update the aggregated ranking data, we need to get the id of the product that was just sold. Notice the line "select @prod_id = prod_id from inserted".

We can access the modified data from a special table called 'inserted' (if your routine did a delete, you would select from 'deleted' and not inserted). So, this table holds the details of the product that was just sold and whose sales data was added to the product_sales table milliseconds earlier. With that simple select statement, I now have the product id I need to do my real work.

Fourth, we set the value for our second local variable, @aggregate_ranking. The value of @aggregate_ranking is set to the result of the SQL statement that follows (using our local @prodid variable to get the right data).

Finally, we update the aggregate ranking for this product in the product ranks table.

Perhaps I'm overly excited about what potential this holds ... I've (clearly) only scratched the surface of what you can do with triggers to keep some of the workload away from your application server (be it ColdFusion, .NET, PHP, whatever) but the possibilities seem vast.

Posted via email from Craig Kaminsky's posterous

Comments

Unknown

Thanks for this post. Among all the different examples I saw on StackOverflow and SQL Forums this one was well designed and worked for me. I appreciate you posting it.

Paul Carney

Good job! Triggers are awesome. You should also work on converting your standard CRUD cfquery statements to stored procedures - that will also give you a performance boost.

One other item for readers to know: the table you get your data from is still 'inserted' even if an update happens. It may not make sense, but that is the way they programmed it!

ColdFusion 9 Released ... and more!

Comments

Disclaimer

Older · View Archive (109)

Flex-to-ColdFusion Performance

Comments

Newer

Using SQL Server Triggers to Save ColdFusion

Comments

Author

Craig Kaminsky