Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post

    php export - slowing down site - suggestions please!

    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

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what is the query..? and, do you index the columns you use in the query?

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    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!!

  • #4
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by homerUK
    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..)

  • #5
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    here's the entire source code...

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

    thanks

  • #6
    Regular Coder
    Join Date
    Jan 2006
    Location
    Preston, Lancashire, England
    Posts
    285
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try changing this
    PHP Code:
        $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
    PHP Code:
        $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()); 

  • #7
    mio
    mio is offline
    New Coder
    Join Date
    May 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    or this
    PHP Code:

    $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.
    Last edited by mio; 05-05-2006 at 12:58 PM.

  • #8
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    #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";

  • #9
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    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

    Code:
    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?

  • #10
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...
    PHP Code:
    $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"
    ); 

  • #11
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    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!

  • #12
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sure, if it cuts down on the number of queries that would be needed..

  • #13
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    Quote Originally Posted by fci
    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...
    PHP Code:
    $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!

  • #14
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    these columns would be selected so those would be in the result set..: questionID, surveyID, question, type, questionNo, position, options


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •