cfflush usage question

[ghanna1]

I'm trying to use cfflush on a page that takes several minutes to complete. I'm doing something wrong because I still get a blank page for a few minutes then all at once I get the results. I've tried the example in the online docs and I'm not sure what I am doing differently. Here's an example of my code.  

cfflush interval = 5>
<cfset count = 0>
<cfquery name="c" datasource="blahblah">
Select CustomerID
From Customer
Where Customer like 'blah'
Order by CustomerID DESC
</cfquery>

<cfoutput query="c">
<cfquery name="s" datasource="blahblah">
Select blah
From Unit
Where CustomerID = c.customerID
</cfquery>
<cfif s.recordcount NEQ 0>

<cfset count = count +1 />
#s.blah#<br>
<cfinclude template="/Connectback/CBChannelAPlaySchedule.cfm">
<a few more cfincludes>
</cfif>
</cfoutput>

<cfoutput>#count#</cfoutput>

Thanks,

Gary

[WolfShade]

Hello, ghanna1,

I think part of the problem is running a SELECT query, and including other .cfm files (which I assume are also doing some kind of data crunching), inside of a query iteration loop.  I don't think cfflush will help with this.

If you can write a JOIN query to get all of the information, and then use GROUPBY in your cfoutput, this might run a bit quicker.  And are the includes really necessary?  Could it be done another way?

V/r,

^ _ ^

[BKBK]

Like WolfShade, I thought of a join too. Something like:

<cfset count = 0>

<cfquery name="s" datasource="blahblah">
   SELECT Customer.blah1 as sblah1, Unit.blah2 as sblah2, Customer.blah3 as sblah3
   FROM Customer
   INNER JOIN Unit ON Customer.CustomerID=Unit.CustomerID
   Order by Customer.CustomerID DESC
</cfquery>

<cfif s.recordcount NEQ 0>
	<cfoutput query="s">
		<cfset count = count +1 />
		#s.sblah1#<br>
		<cfinclude template="/Connectback/CBChannelAPlaySchedule.cfm">
		<a few more cfincludes>

		<p>count:#count#</p>
	</cfoutput>
</cfif>
 

[ghanna1]

Good point a JOIN would work better. I just took an online SQL class to learn how to be more efficient. Thanks.

As for all the cfincludes yes there's a bunch of SQL functions in there. I built them as includes since there are dozens of other pages in the project that call on them. Functions would be better but I still struggle to understand how functions work.

Gary

[WolfShade]

Well, while creating functions that are called from different areas is, indeed, more efficient than writing the same function over and over, there is a point where efficiency can overwhelm the application.  And if said SQL functionality can be contained within a single SQL query, that would be much more efficient than several, separate SQL functions all focusing on the same goal.

That being said, there are instances where what you are doing is the only way.  For example, if the data needs to be aggregated from more than one DSN.  If security is set properly, the tables accessed from one DSN cannot be accessed by another DSN.  So, in this case calling queries and doing other processing inside of the iteration of a query object is the only option.

But if you are working from just one DSN, then JOINS and other methods within SQL would be significanly faster and more efficient.  And if you are working JOINS, then aliasing your tables and using said alias in front of each column name is paramount, if some tables have commonly named columns.  I call it "pre-emptive disambiguation".

HTH,

^ _ ^

[John123]

If you are running with IIS then by default CFFLUSH will not work. To enable CFFLUSH you need to make a change to the IIS connector configuration as detailed in the CF install guide. The relavent point is 

To disable webserver buffer, change the iis_buffer_enable to false in the cfroot\config\wsconfig\1\isapi_redirect.properties file. Disable webserver buffer if you want cfflush to work over an IIS connector. If your application does not use cfflush, set it to true for increase in the performance.

[Charlie Arehart]

Ghanna1, a few thoughts on all this:

First, John makes a good point (getting back to the original point of the question, about cfflush not seeming to work). And that alone may be the problem, if indeed you are doing your testing of requests by way of running them through IIS. Are you?

But either way, I'll propose another thing that may be the problem: you set the interval to "5". That means "try to flush after every 5 bytes" There are two problems with that.

First, as the docs for cfflush say, "When you flush data, ensure that enough information is available, as some browsers might not respond if you flush only a small amount."

Second, FWIW, note that the next sentence goes on to say, "Similarly, set the interval attribute for a few hundred bytes or more, but not thousands of bytes.Use the interval attribute only when a large amount of output is sent to the client, such as in a cfloop or a cfoutput of a large query."

Now, all the other discussion about how to most effectively do the queries may affect the speed of the query--and that may well help diminish the need of flushing in the first place, if the page runs much faster. 🙂 But that last sentence in the docs does of course refer to the process of producing OUTPUT of the query result, which has to do with the SIZE of the result rather than the query's speed of execution.

You'll want to keep all this in mind (speed of the page, size of what's flushed, IIS connector settings supporting flushing) when trying to debug what may seem "amiss" about flushing "not working".

Hope that's helpful.

[ghanna1]

The data comes from multiple DSN's so there is only so much I can speed up with JOIN. In the end, I opted to not use cfflush. There is a risk the end-user wouldn't wait for the process to complete and leave the page. Instead, I added a flag to the initial records and then used a scheduled task behind the scenes to process the rest. With this method I am not reliant on the end-users patience - which is always a good thing.

Thanks for all the great suggestions!

[Charlie Arehart]

Yep, and yet another way to go would be to use cfthread to spin the task off to a background asynchronous thread. That seems a slight twist. In what you're doing, but that may add considerable power for you.

You could even leverage websockets (supported by cf) to add a way for the end user to know when the background thread is finished.

But I realize you may be satisfied with your current solution. 

[ghanna1]

Thanks Charlie! I just read up on cfthread. Very interesting approach. It would definitely be more efficient since there wouldn't be a delay waiting for the next scheduled task cycle. In this particular case, the user doesn't need to know when the process completes but I am familiar with the WebSocket approach. 

Gary

[BKBK]

Hi ghanna1 

If you're on ColdFusion 2018, you might also be interested in asynchronous programming by means of the runAsync function.