4

I'm in the process of porting a Java desktop application to a ColdFusion web app. This desktop app made queries with very large result sets (thousands of text records) that, while being all right on the database side, could take a lot of memory on the client side if they were buffered. For this reason, the app explicitly tells the database driver to not buffer results too much.

Now that I'm working on the ColdFusion port, I'm being hit by the buffering problem. The ColdFusion page times out during the <cfquery> call, and I'm fairly sure this is because it tries to buffer everything.

Can I make an unbuffered query in ColdFusion?

zneak
  • 134,922
  • 42
  • 253
  • 328
  • "Buffering" as in setFetchSize? Desktop code does not always translate well into web application code. What are these large resultsets used for? Do you really need to retrieve all of the records at once? Just wondering if pagination might be a better option. – Leigh Aug 01 '11 at 20:26
  • @Leigh, I need to retrieve the rows to match and replace regular expressions on them. I can stop fetching rows as soon as I find one that matches any of the regexes I'm looking for. My DBMS does not support regular expressions itself, so I have to do that matching on the client side (and even if it did it would be pretty expensive). – zneak Aug 01 '11 at 20:36
  • You never did say, how were you creating this "unbuffered" query in java - using setFetchSize? Also, which DBMS? – Leigh Aug 02 '11 at 01:38
  • @Leigh, I'm basically repeating what I was told before I started. It probably uses setFetchSize. The DBMS is SQL Server 2000, so it might also be something specific to that driver. – zneak Aug 02 '11 at 03:15
  • Find out if you can. Hard to believe there is not a better way to do this. But all I am coming up with for SQL 2000 is extended stored procedures. – Leigh Aug 02 '11 at 15:23
  • Can you provide the regexes that you're searching against? Also, have you considered using ``, `` and `` tags to setup full text search with Solr? This might a way of doing the sort of complex matching you're after without relying upon SQL Server or ColdFusion. – orangepips Aug 02 '11 at 20:05
  • @orangepips, they're very simple regexes in the form of `\bexpression here\b`. I haven't considered `` and its friends; though, I'm not sure I weight enough to try Solr, though, especially since I'm on my leave. – zneak Aug 03 '11 at 03:47

2 Answers2

2

If pagination is not an option (i.e., you're writing out a report for example), then you'll have to get low level with the java, using setFetchSize(). See this answer. Note that the code in the answer uses the DataSourceService, which, with latest security patches from Adobe, is no longer available on CF8. You'll have to figure out how to get a connection via the adminapi or create a connection outside of coldfusion. Or you could transition your datasource to use JNDI, and then you can lookup the resource yourself without using CF api's.

Community
  • 1
  • 1
Mark
  • 2,362
  • 18
  • 34
  • Is that different than setting the cfquery blockFactor? I was never sure.. http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fae.html – Leigh Aug 01 '11 at 20:43
  • Interesting. How will this be scoped though? It would be fine to change the set size for my script, but I would like to leave it as is for the others. – zneak Aug 01 '11 at 20:43
  • From what I saw, the changes are applied to that one `statement`. So they should not affect other connections. – Leigh Aug 01 '11 at 21:11
1

I'm almost certain that ColdFusion does not provide such a mechanism. As a language, it was meant to abstract the developer away from things like that.

I'd suggest that you look into a few options:

  • Re-work your query to use pagination, and run it in a loop.
  • Use the timeout attribute on the <cfquery> to prevent timeouts from happening
  • Use the CreateObject() syntax to instantiate a JDBC database connection.

With the last option, what you'd actually do is access the underlying Java classes to do the querying and getting results. Take a look at this article for a quick look at the CreateObject() function.

You can also look at the Adobe Livedocs for the function, but they don't really seem helpful.

I haven't tried to use CreateObject() to do querying with the Java database access classes, but I imagine that you can probably get it to work.

Ryan
  • 2,948
  • 3
  • 30
  • 41
  • Yes, I think examining whether such a large resultset is really needed would be the best first step .. Because desktop apps often do resource intensive things you would never want to do on server. – Leigh Aug 01 '11 at 20:36
  • I'm operating on the data, rather than presenting it, so pagination isn't really an option. Also, the query shouldn't take ages in the first place, it's the unnecessary buffering that really kills the performance. – zneak Aug 01 '11 at 20:38
  • Operating how? Is this an automated clean up script, a one time script, or something else ... – Leigh Aug 01 '11 at 20:54
  • This script should assist technical writers of my company in finding and replacing occurences of certain expressions in the help files of our very large application, in order to better fit the help to the corporate vocabulary of our clients. It will be used every time we get a new client, and possibly once for each client we currently have. – zneak Aug 01 '11 at 21:03
  • 2
    Maybe not pagination exactly, but a "set" approach still seems feasible. As long as you can identify the records processed, like using a sequential record id. Just grab a 100 records. Process them. Grab the next 100 that are > lastRecordID. Loop/continue until you find the desired record and finish. That is assuming there is not a better way to process regex's with your db type. Because this does not feel like the best option .. – Leigh Aug 01 '11 at 21:27
  • I could be missing something, but it seems that if you're crunching data, rather than presenting it, ColdFusion is not the right language for the job. Have you thought about off-loading the crunching part to a service (written in Java, or some other more powerful language), storing the results, and just using ColdFusion for presentation? – Ryan Aug 02 '11 at 17:27
  • I ended up using the "paginated" query, with fetching like 250 records at a time. – zneak Aug 03 '11 at 03:46