ColdFusion experts: Ques on serial vs. parallel procs.

[cstreit]

Hey folks, need some advice here:

We're attempting to bring a new customer live and they have a 3rd party that manages their databases. That 3rd party is responsible for extracting and transforming data for us and are 5 months behind in the work.

The developer there has told me that the main issue they are having is performance. Apparently they've built a bunch of steps in ColdFusion but that these steps cannot reliably trigger each other. So they have to execute a step, wait two hours to ensure it has completed, then execute the next step, otherwise they all kick off at once and then the end processes die before starting.

Is this normal? This runs contrary to every language/environment I've heard of where basic linear batch scheduling is simple.

Are we getting the run-around?

[legion]

What is the platform for the backend? Unix? zOS? Windows?

[cstreit]

Windows on Intel

[Tishabet]

Is CF actually necessary for the steps, i.e. is CF providing actual context or data for the DB step or is it simply being used as a scheduling device?
My exposure to CF is limited, but when performing serial processes against a DB I typically make everything run/trigger within the DB itself.

[legion]

Originally Posted by cstreit

Windows on Intel

I was afraid you'd say that. I have the most knowledge of zOS, some knowledge of Unix, and almost no knowledge of databases running on Windows.

[cstreit]

Well The issue from what I've heard is the way ColdFusion manages processes rather than the DB itself. Extracts are done by the DB and post processing done by Coldfusion. The transformations are too complex to reasonably do in SQL.

[Tishabet]

Originally Posted by cstreit

The transformations are too complex to reasonably do in SQL.

In the serial processes I alluded to (which were built in MS SQL Server) we use stored procedures... you can do some extremely complex transformations in there, essentially you can treat it as a programming environment hosted by the DB. Then when the transform for one step is done, trigger the next.

I have one system I architected this way which takes electronic debits from a banking platform, transforms them on a user basis into one transaction amount, looks up and decrypts the account information, builds a batch file on a remote drive and SFTPs it to a third party vendor and then follows up with a confirmation email... all 100% in SQL Server, including the scheduling.

[cstreit]

Well there's not dount that it COULD be done in another way. However since they put in many hours to the ColdFusion process I know they want to stick with it.

The question really is, is this something that CF does, or are they doing something wrong?

[svandamme]

is SQL on the same server as cold fusion?
what kind of machine is it running on?
windows version?
disk/controller setup?
memory?
what kind of cpu(s)?
???

DB work is usually more about disks and memory then anything else

[Paul_Heery]

Chris,

I would seriously question what they are doing.

We run ColdFusion for some apps, but all of the data actually resides in a SQL database. (It's on MS SQL Server 2005, but we won't go there.) As Tishabet mentioned, what you are attempting to do sounds like it could be accomplished solely through directly manipulating the database, possibly with stored procedures.

It sounds like they are trying to do this through ColdFusion because that is the only way they know how.

What are you using for your database (MSSQL, MySQL, Oracle, etc.)? Identify that, then lookup the local user's group. I bet you could find someone that could give a reasonable expert opinion for a few hundred bucks on the ide.

[ikarcuaso]

Originally Posted by Tishabet

Is CF actually necessary for the steps, i.e. is CF providing actual context or data for the DB step...

Giving the 3rd party the benefit of the doubt, it's possible they are leveraging some CF interfaces already in place that perform some complex calculations or tasks that would be difficult or impossible to replicate w/solely the db. Maybe something involving various data sources, protocols, events, etc. It's difficult to say w/o more knowledge.

That said, I would think sql server agent, vbscript, stored procedures, etc. could be used to better control the process flow.

[stomachmonkey]

Sounds like you are getting the run around

or

They are over charging the client and can't make it appear "easy"

or

They have no idea what they are doing

or

They built a Frankenstein


Have seen all of the above far too many times

Good luck.

[Paul_Heery]

Originally Posted by stomachmonkey

They built a Frankenstein

That is probably the best assessment of the situation.

[legion]

Wow. I'd say you're getting the run around.

Chris, I know you can appreciate this... We're about ready to get rid of all of our HP 3000/9000's and go to IBM mainframes (zOS) for our biggest system. This entails copying data from the HP 3000/9000 and merging it with data already on the mainframe into a completely knew data structure. Conversion is going to be a monster. We think implementation will take three years on an optimistic schedule. We're using 3-4 different tools for parts of the work. I know DPROPR is one of the tools.

[cstreit]

It sounds like they are trying to do this through ColdFusion because that is the only way they know how.

This is pretty much what I've read between the lines. At the rate they're going it probably makes sense to start over with something more suited to bulk processing. THey're using MySQL for the database IIRC. Unfortunately I am only in an advisory position here. I just can't believe it takes 8 months to do a good series of data extracts despite their complexity.