...

View Full Version : php export - slowing down site - suggestions please!



homerUK
05-04-2006, 10:24 AM
Hi..

I've created an export script which runs through hundreds of records to output to a tabulated file for use in excel. It works perfectly, trouble is though, it takes FOREVER to run, and if it's running, other users of the site suffer as it uses so much resources (I assume).

Any suggestions as to how I can combat this problem? Maybe run exports on another server? like exports.domain.com ? Not sure how that would work, but it may be an option.

Thanks for any advice :)

fci
05-04-2006, 02:36 PM
what is the query..? and, do you index the columns you use in the query?

homerUK
05-04-2006, 04:35 PM
not really sure what you mean by indexing the columns... but the ones in question are the primary key?

The query is a simply

GET all questions from survey XX
then get each response for that survey

its just that with over 1000 responses, it gets waaaay slow!!

fci
05-04-2006, 05:57 PM
not really sure what you mean by indexing the columns... but the ones in question are the primary key?

The query is a simply

GET all questions from survey XX
then get each response for that survey

its just that with over 1000 responses, it gets waaaay slow!!

post all your code and we can tell you what to do (I speculate you're not doing a join when you could be..)

homerUK
05-05-2006, 11:05 AM
here's the entire source code...

http://www.mattfacer.com/example.phps

thanks :)

MRMAN
05-05-2006, 11:31 AM
try changing this

$survey_details = mysql_query("SELECT
survey.memberID as memberID,
survey.title AS title,
members.username
FROM survey
INNER JOIN members ON survey.memberID = members.memberID
WHERE members.username='".$_SESSION['username']."'
AND surveyID=" . $surveyID."") or die(mysql_error());

to this

$survey_details = mysql_query("SELECT
survey.memberID as memberID,
survey.title AS title,
members.username
FROM survey
RIGHT JOIN members ON survey.memberID = members.memberID
WHERE members.username='".$_SESSION['username']."'
AND surveyID=" . $surveyID."") or die(mysql_error());

mio
05-05-2006, 01:41 PM
or this



$survey_details = mysql_query("SELECT
survey.memberID as memberID,
survey.title AS title,
members.username
FROM survey, members
WHERE survey.memberID=members.memberID
AND members.username='".$_SESSION['username']."'
AND surveyID=" . $surveyID."") or die(mysql_error());



1st equality from the where clause exempt the inner join or right join clause. One clause less.

By the way, not sure it comes from the query, may be you should set a timer each time you launch a query either to be sure it comes from a query or anything else.

fci
05-05-2006, 02:33 PM
There are some *major* problems with your code... for example, you were running a query for each responseID instead of using an in clause.. you also have the columnquestionNo which isn't even relational(a very bad sign.. you should add 2 columns, one called questionID and one for the value of $q_row, if you can't do that, be sure to at least index the questionNo column), here is how I've tried to optimize your code but based on the amount of code duplication you already have, it will be a lot of work to optimize (starting with the issue that your tables are slightly but then leading into so great queries).. the code below may not even work exactly as needed but it at least doesn't loop through the $responses_array and generate numerous dynamically built queries

#now get the responses for this row
//for ($i=0;$i<sizeof($responses_array);$i++) {
$responses = implode(',', $responses_array);
$sql = "
SELECT responseID, answer
FROM answers
WHERE questionNo = 'answer_".$questions_row['questionID']."_" . $q_row . "'
AND responseID IN (".$responses.")
ORDER BY responseID
";

while ($answers_row=mysql_fetch_assoc($rs)) {

while ($current_response = array_shift($responses_array)) {
if ($answers_row['responseID']!=$current_response) {
$questions .= "\t";
break;
}
}

if ($answers_row['answer'] == str_replace("\r", "", str_replace("\n", "", $col[$h])))
$questions .= "1\t";
else
$questions .= "\t";

}
$questions .= "\n";

homerUK
05-05-2006, 03:01 PM
hi - thanks for the replies...

fci - the database is really old and was not built well - this is why it's a pain having to work on it now!

the layout of the export file I am producing is as follows



QUESTION RESPONSE1 RESPONSE2 RESPONSE3
1)Question 112 445 664
2)Question 1 6
3)Question yes yes


in the example above there are some responses which do not have answers... I wonder if the better thing to do is to put responses down the page? Then you could get all the questions, then look for the answers for each?

fci
05-05-2006, 03:19 PM
I'd say ultimately your problem is from all the nested queries.. if there is a way to cut down on those then that would likely speed it up...
here I am trying to join the two tables...

$questions_sql = mysql_query("
SELECT
q.questionID, q.surveyID, q.question, q.type, q.questionNo, q.position, q.options
FROM questions q, answers a
WHERE q.surveyID = $surveyID
AND a.questionNo REGEXP CONCAT('^answer_', q.questionID, '(_[:digit:]+)?$')
ORDER BY q.questionNo ASC");

homerUK
05-05-2006, 05:17 PM
if I were to load the answers in to an array, then process it, would that be any quicker? For each question, I need to get all the responses, if there is no response, then put a "\t" (tab) and move on to the next response..... nightmare!

fci
05-05-2006, 05:23 PM
sure, if it cuts down on the number of queries that would be needed..

homerUK
05-08-2006, 05:28 PM
I'd say ultimately your problem is from all the nested queries.. if there is a way to cut down on those then that would likely speed it up...
here I am trying to join the two tables...

$questions_sql = mysql_query("
SELECT
q.questionID, q.surveyID, q.question, q.type, q.questionNo, q.position, q.options
FROM questions q, answers a
WHERE q.surveyID = $surveyID
AND a.questionNo REGEXP CONCAT('^answer_', q.questionID, '(_[:digit:]+)?$')
ORDER BY q.questionNo ASC");


how would I refer to the answers in the SQL statement you gave (above)...?
If I loop through the results, what would be there? Sorry - I just dont really understand what you've done! :rolleyes:

fci
05-08-2006, 06:40 PM
these columns would be selected so those would be in the result set..: questionID, surveyID, question, type, questionNo, position, options



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum