As with most things in ColdFusion, you get convenience over performance. If you use a raw database driver (and a front-end platform to handle it), you may have the option of streaming results directly out of the database and onto a web page. This is extremely performant and uses very little memory. This is not what ColdFusion does. CF pulls the results out of the database and drops them into a query object. This object looks more like a struct of arrays when you dig in (and it essentially is). Furthermore CF lets you manipulate the data, change the recordset, query off of it, merge it with another, and so on. It's crazy powerful, but not the fastest knife in the drawer.
Adobe ColdFusion uses DataDirect (.com) JDBC drivers for most (if not all) of the databases. This makes it convenient for Adobe to farm out that hard work while providing a standard interface, but on the other hand, native JDBC drivers (i.e. drivers provided by Microsoft, Oracle and other database vendors) tend to be a bit quicker because they have a lot riding on their performance with Java applications. Those native drivers also usually contain more advanced, database-specific features. DataDirect, by their very nature, has to take a safer route. Then again, that's DataDirect's primary job, and you won't often find them more than a half-step behind and often two steps ahead, that's how they make money. Of course, you don't have to use the DataDirect drivers. ColdFusion will accept any valid JDBC driver (that's why the JDBC spec was invented). But then you still have the ColdFusion overhead. DataDirect + ColdFusion has the fantastic advantage of managing database connection pools, thread pools, network connections, network database resolution, security, and so on. If you think you can do a better job by writing native Java, then you work at the wrong company. In other words, this is way, way more work than anyone should ever sign up for, except those people at DataDirect (or Microsoft, or Oracle), or the Adobe CF team office. This is the kind of low-level programming that business application developers need to avoid in order to stay productive. Now, if you have some people who are handy with JDBC and can write great Java, it could make sense to put some of your objects into Java, but chances are, this is a bunch of baloney also. If your only work in Java is some SQL-containing database objects (first off I'd like to point out that that's not actually OO), then you are creating more overhead than you think. Instead of a CF JDBC-connector object, you'll be creating a custom JDBC connector object; personally I'd rather rely on Adobe to get this right, either way it's probably about the same amount of actual objects on the heap. Yes calling java from CF is lightning quick, and getting results back will be sliiiiiightly faster, let me point out a few things about your new development lifestyle: 1. You now have another language to maintain 2. That other language needs to be compiled every time you make a change 3. Unless you are on a very recent version of CF, CF may need a restart every time you change the Java code 4. Your SQL is in that other language, so now it is harder to get to 5. Your SQL has to fit into Java strings, so no more pretty line breaking and easy reading of SQL for the developers 6. There is nothing invented by all of mankind that is simpler than the cfquery tag 7. You now require CF and Java developers to do what used to take just a CF developer 8. You lose the ability to do query of queries and other CF manipulations So let's go back to the drawing board. First, are you sure you need to scale up? Could you scale out instead? Is it possible that a SQL database isn't even what you need? When you talk about scalability, maybe going to NoSQL databases would be a better fit. How about a cloud-hosted database where this conversation wouldn't even exist? Going back to the CF speed problem, I know a guy, maybe you know Mike Brunt too, he calls himself the cf whisperer, and it's true, he can listen to your app and tune your JVM so that it works faster than almost any pure Java app. If you're experiencing performance problems, almost every single time you'll find that you have a poorly tuned database, you're selecting too much data at a time, you're using inefficient joins, you don't use cfqueryparams, or something similar along these lines. There is a certain point, depending on hardware and the application, where you actually do have a scalability problem, but from my experience, some proper tuning and refactoring will get you way further than you think (until you have to spend $millions on "real" server hardware). ColdFusion server and its database connections are never the problem in my experience. Jumping to Java would be a bad call. nathan strutz [www.dopefly.com] [hi.im/nathanstrutz] [about.me/nathanstrutz] On Fri, Jul 13, 2012 at 9:57 PM, PT <[email protected]> wrote: > > Is there any advantage to having a CFC hand off database operations to > java or some derivative over letting cfquery handle them itself? I have > seen people use the CFC as a wrapper for using another language to > handle the database access, but I have never seen a concrete explanation > for doing this. > > I am looking for speed advantages, mainly. > > Is there any language that is faster at database interactions than CF on > a large scale, or does it even matter? > > I am pretty sure no matter what I do, the database is going to be the > choke point, but every little bit helps, especially when scaling up and > things get wonky. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351889 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
