SLSCoder Posted June 6, 2022 Share Posted June 6, 2022 I'm almost scared to ask this because I kind of expect you guys to chew me to bits - lol - but I have to try and find out. I have to be able to reasonably handle 50,000 records total (1 = Count + AvgX + AvgY) from this set of queries. I can't think of a better way to do it and it works fine with a few records (13 surveys total in the initial tests) but I'm concerned about it being efficient enough with more records. I'm wondering if putting it all into a stored proc would speed it up. I don't like stored procs. I prefer the code to be in my server side language (PHP in this case). If I have to change the database server stored procs make the job bigger, I have to rewrite all that code. The records are the Total count, Total X Avg, Total Y Avg and the Counts and the X and Y averages for each cell. The WHERE clauses are pretty straight forward; generally just a filter for answers.QuestionID and maybe a few other answers table fields. The FILTER can include combinations of any fields from the surveys and/or answers table. The FILTER params come from a client side form so must be protected from sql injection - thus PDO. $arParams is an array of the FILTER params. Here, I'm building a cross tab report for 2 params (ie QuestionID: 134 and QuestionID 78) in numeric ranges given: Min1 to Max1 by Interval1, Min2 to Max2 by Interval2. I know this is pretty complicated. If it's too much don't worry about it. Like I said, this code works, it's just an awful lot of queries so I suspect with a lot of records it'll be slow. //Get Total Count and Total X and Y averages try { $sql = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . ") AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . ")"; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); $Total = $ar[0]["Cnt"]; $sql = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . ")"; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); if(is_numeric($ar[0]["Avg"])) $TotalXAvg = round($ar[0]["Avg"], 1); else $TotalXAvg = ""; $sql = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . ") AND " . $Where2; if(!empty($Filter)) $sql .= " AND " . $Filter; $ar = $this->executePDOQuery($sql, $arParams); if(is_numeric($ar[0]["Avg"])) $TotalYAvg = round($ar[0]["Avg"], 1); else $TotalYAvg = ""; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } //Get First X & First & Count and XAvg and YAvg try { $sqlA1 = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB1 = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND " . $DataFld1 . " < " . $CurMax1; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC1 = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX] = []; $arData[$idxX][$idxY] = $arRec; $YLabels[$idxY] = $CurMin2 . " to < " . $CurMax2; $XLabels[$idxX] = $CurMin1 . " to < " . $CurMax1; //Get First X and Remaining Y Count and Averages while($CurMax2 <= $Max2) { $idxY++; $CurMin2 = $CurMax2; $CurMax2 = $CurMin2 + $Interval2; try { $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB = $sqlB1 . " AND surveys.RecordID IN (" . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["XAvg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC = $sqlC1 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 ; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp1n1Data 2: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; $YLabels[$idxY] = $CurMin2 . " to < " . $CurMax2; } $Max2 = round($Max2); if($CurMax2 > $Max2) $YLabels[$idxY] = $CurMin2 . " to " . $Max2; //Get remaining X Counts & for each, First X and Y Average while($CurMax1 <= $Max1) { $CurMin2 = $Min2; $CurMax2 = $CurMin2 + $Interval2; $CurMin1 = $CurMax1; $CurMax1 = $CurMin1 + $Interval1; $idxX++; $idxY = 0; $arData[$idxX] = []; try { $sqlA1 = "SELECT COUNT(DISTINCT surveys.RecordID) AS Cnt FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB1 = "SELECT AVG(" . $DataFld1 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC1 = "SELECT AVG(" . $DataFld2 . ") AS Avg FROM answers INNER JOIN surveys ON answers.SurveyID = surveys.RecordID WHERE surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where1 . " AND " . $DataFld1 . " >= " . $CurMin1 ." AND " . $DataFld1 . " < " . $CurMax1 . ")"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " < " . $CurMax2 ; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp2NumData: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; //Get Counts & Averages for Remaining Y while($CurMax2 <= $Max2) { $idxY++; $CurMin2 = $CurMax2; $CurMax2 = $CurMin2 + $Interval2; try { $sqlA = $sqlA1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlA .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlA, $arParams); $arRec = []; $arRec["Cnt"] = $ar[0]["Cnt"]; if($arRec["Cnt"] > $MaxCnt) $MaxCnt = $arRec["Cnt"]; $sqlB = $sqlB1 . " AND surveys.RecordID IN (SELECT SurveyID FROM answers WHERE " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2 . ")"; if(!empty($Filter)) $sqlB .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlB, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["XAvg"] = round($ar[0]["Avg"], 1); if($arRec["XAvg"] > $MaxXAvg) $MaxXAvg = $arRec["XAvg"]; } else $arRec["XAvg"] = "n/a"; $sqlC = $sqlC1 . " AND " . $Where2 . " AND " . $DataFld2 . " >= " . $CurMin2 . " AND " . $DataFld2 . " < " . $CurMax2; if(!empty($Filter)) $sqlC .= " AND " . $Filter; $ar = $this->executePDOQuery($sqlC, $arParams); if(is_numeric($ar[0]["Avg"])) { $arRec["YAvg"] = round($ar[0]["Avg"], 1); if($arRec["YAvg"] > $MaxYAvg) $MaxYAvg = $arRec["YAvg"]; } else $arRec["YAvg"] = "n/a"; } catch(Exception $exp) { throw new Exception("Error in daRptStatsAnly.getCmp2NumData: " . $exp->getMessage() . "<br>" . $sql); } $arData[$idxX][$idxY] = $arRec; } } Quote Link to comment https://forums.phpfreaks.com/topic/314899-how-can-i-make-this-more-efficient/ Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.