Can't Get Buffering To Cooperate

[Strahan]

Hi. I have code that does a SQL query which can take ten seconds or so. I figured rather than have people just sit there wondering if it's working, I'd display something like "Please wait ..." then after the query display the data. I did:

 

ob_end_flush();
echo "Please wait ...<BR><BR>";
(sql stuff)
echo "Done!";

 

...however it waits until the whole thing is done before it displays anything. I thought from ob_end_flush would do it. What would? Thanks!

[ManiacDan]

ob_end_flush() would do that...if you put it AFTER the echo and not before.  But it won't work on all hosts, and it's not all that reliable.  Better to print the page and then perform the query in ajax so you can make a JS spinner with custom timeouts and stuff.

Edited November 2, 2012 by ManiacDan

[PFMaBiSmAd]

I would find and fix the problem that is causing a query to take that long. It's rare that a query will take longer than a second to run.

[Strahan]

Hmm, I'll have to read up on ajax. As for the SQL, it's not so much the time it takes for SQL to send data back as it is the time for me to process it. Orig, I was doing something like SELECT SUM(numberfield) AS blah but then someone put in a date range for a whole year of data and I got an arithmetic failure. Apparently it chokes trying to sum 35,000 records worth of numeric fields that can get large (it's seconds that a service ticket has been open, and some tickets go 60+ days). I switched to just doing SELECT numberfield then doing a while ($row = $sql->fetch(PDO::FETCH_ASSOC)) $tot += $row["numberfield"];. The looping is what takes so darned long.

 

Thanks guys.

[PFMaBiSmAd]

A) Do you really need seconds resolution? You could divide the values, inside the sum() term, by 60 or 3600 to get minutes or hours resolution.

 

B) What's the goal of doing this anyway? Wouldn't you sum just the values for tickets that are still 'open'?

 

C) You can do a PDO fetchall for just that column and do a php array_sum to get the result. No loop needed.

Edited November 4, 2012 by PFMaBiSmAd

[Strahan]

A - Good idea, maybe that will stop the SQL from crapping itself. I'll try that.

B - Average time a service ticket was open. System logs open time and last activity time (UNIX time) so what I'm actually doing is a sum of ticket end-ticket open where ticket status = closed.

C - Cool, wasn't aware of that feature. If A still fails, I'll try that.

 

Thanks a lot for the tips!

 

EDIT: Just tried it, A works like a champ. My SQL:

 

SELECT AVG(DATEDIFF(second, created, lastupdate)/60) AS ddiff FROM tickets WHERE ticket_status = 0 AND created >= CAST('2011-11-01' AS DATETIME) AND created <= CAST('2012-11-03' AS DATETIME)

 

Gives me back average minutes open on 35k records in a practically an instant. Whoo hoo

 

Thanks again!

Edited November 4, 2012 by Strahan