dragon87836215
2012-12-18 16:35 阅读 55
已采纳

如何加快我的PHP PSQL查询

REQUESTED TABLE DEFINITIONS

                Table "public.call_record"
     Column      |          Type          |   Modifiers
-----------------+------------------------+---------------
 cntrct_id       | character varying(15)  | not null
 call_regard     | text                   |
 port_type       | character varying(9)   |
 inst            | text                   |
 info_taken      | character varying(40)  |
 log_date        | date                   | not null
 log_time        | time without time zone | not null
 act_taken       | text                   |
 use_material    | text                   |
 targ_pest       | integer                |
 work_comp_by    | text                   |
 emp_no          | integer                |
 comp_date       | date                   |
 job_start_time  | time without time zone |
 job_leave_time  | time without time zone |
 comp_val        | boolean                | default false
 fti_call_regd   | public.tsvector        |
 fti_inst        | public.tsvector        |
 fti_act_take    | public.tsvector        |
 route           | character(3)           |
 act_port        | text                   |
 targ_pest_opt   | text                   |
 call_regard_opt | text                   |
 targpest_other  | text                   |
 date_sched      | date                   |
 custord_num     | integer                |
 dist_id         | integer                |
 phone_slot      | integer                | default 0
 Indexes:
    "call_record_pkey" PRIMARY KEY, btree (cntrct_id, log_date, log_time)
    "route_index" hash (route)
 Check constraints:
    "call_record_targ_pest_check" CHECK (targ_pest <= 100)
    "call_record_targ_pest_check1" CHECK (targ_pest >= 0)

          Table "public.per_call"
 Column  |         Type         | Modifiers
---------+----------------------+-----------
 dist_id | character varying(2) |
 route   | character varying(2) |
 type    | character(1)         |
 total   | integer              |

I need to get data from 2 tables and print it in a single report. The report should look like this:

district | route | type | total | callbacks
         | 01    | T    | 12    | 5
         | 02    | P    | 0     | 0
         | 03    | P    | 3     | 1
2        | 01    | T    | 4     | 1
         | 02    | T    | 1     | 0
         | 03    | P    | 0     | 0
etc... (this is theoretical sample data)

So, in essence I need to get the dist_id, route, type, and count(*) from the table per_call and the count of call_backs from the table call_record

PROBLEM: looping through tables makes it go glacially slow. How can I adjust the following PSQL query so that I don't have to loop and I can echo the tabular data properly?

Let me know if anything is opaque and I will try to clarify

   echo    '<table align="center" border = 2>
            <th>DISTRICT</th>
            <th>ROUTE</th>
            <th>TYPE</th>
            <th>TOTAL</th>
            <th>CALL BACKS</th>';


    $SQL = " SELECT per_call.dist_id, per_call.route, per_call.type, per_call.total
            FROM per_call, call_record
            WHERE TRUE  ";

    if($type == 'termite'){
            $SQL = $SQL." AND per_call.type  = 'T' ";
    }
    else{
            $SQL = $SQL." AND per_call.type = 'P' ";
    }
    $SQL = $SQL."   AND call_record.dist_id = per_call.dist_id
                    AND call_record.log_date >= '$startDate'
                    AND call_record.log_date <= '$endDate'
                    ORDER BY per_call.dist_id, per_call.route, per_call.type    ASC ";

    echo    $SQL;
                    /*AND call_record.log_date = '$startDate'
                    AND call_record.log_date = '$endDate'*/

    $Q = pg_query($connect,$SQL);
    while($row = pg_fetch_row($Q)){
            $dist = $row[0];
            $route = $row[1];
            $type = $row[2];
            $total = $row[3];

            echo '<tr>';
            echo '<td align="center">'.$dist.'</td>';
            echo '<td align="center">'.$route.'</td>';
            echo '<td align="center">'.$type.'</td>';
            echo '<td align="center">'.$total.'</td>';

            $SQL2 = "SELECT COUNT(*)
                    FROM call_record
                    WHERE dist_id = $dist
                    AND route = '$route'
                    AND substring(cntrct_id from 2 for 1) = '$type'
                    AND substring(call_regard_opt from 2 for 1) = '1'

                     ";
            $Q2 = pg_query($connect,$SQL2);
            $row2 = pg_fetch_row($Q2);

            $callbacks = $row2[0];

            echo '<td align="center">'.$callbacks.'</td>';

            echo '</tr>';
    }

    echo "</table>";
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    dongma0722 dongma0722 2012-12-18 17:03
    select pc.dist_id, pc.route, pc.type, pc.total,
        count(
            substring(cntrct_id from 2 for 1) = '$type'
            AND substring(call_regard_opt from 2 for 1) = '1'
            or null
        ) callbacks 
    from
        per_call pc
        inner join
        call_record cr on cr.dist_id = pc.dist_id
    where cr.log_date between '$startdate' and cr.log_date <= '$enddate'
    group by pc.dist_id, pc.route, pc.type, pc.total
    order by pc.dist_id, pc.route, pc.type asc
    
    点赞 评论 复制链接分享
  • duanmu1736 duanmu1736 2012-12-18 16:48

    Unless index problems, your query is not badly written, so it can't be really optimized from a query form point of view. You can do that though which is cleaner :

    $SQL = "SELECT per_call.dist_id, per_call.route, per_call.type, per_call.total
      FROM per_call, call_record
     WHERE call_record.dist_id = per_call.dist_id
       AND per_call.type  = '".($type == 'termite' ? "T" : "P")."'
       AND call_record.log_date >= '$startDate'
       AND call_record.log_date <= '$endDate'
     ORDER BY per_call.dist_id, per_call.route, per_call.type"
    

    Anyway, this is still vulnerable to SQL injections. Try using parameterized queries.

    点赞 评论 复制链接分享
  • dongzhong8691 dongzhong8691 2012-12-18 17:09

    You don't have any indexes on dist_id in either table, which is going to make your join very slow. Add indexes on dist_id and see how much it improves.

    Also, that query inside the loop is going to be the death of you, because you're going to be doing many many many queries. Work the inner query into your main query so you only execute one query in the database.

    点赞 评论 复制链接分享

相关推荐