Jump to content

How can I make this more efficient?

SLSCoder

By SLSCoder
in MySQL Help

 Share

Recommended Posts

SLSCoder Regular Member

SLSCoder

Posted
Posted

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;
				
			}				
		}

GridXY1.png.99f3cadd3b53fa7a81c95927bd0727d6.png

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.