Optimize Query

[chaking]

My queries are currently taking over 20 minutes to run and I have a feeling I'm probably doing it rather poorly, was hoping someone could suggest an alternative...

 

The jist of the script is to go through the database and find the last time a certain entry was made and then put it into an array for later.  It's going through hundreds of thousands of rows and so it's taking entirely too long.  After I populate the array, I then get the unique values from it and run another set of queries... to see if the canceled state of what I was originally looking for appears after the time of the active state (the original search and array).  So I run a SELECT COUNT(*) as ... WHERE TIME > ORIGINAL_TIME... If it returns 0 I output the result saying the original active state never had a canceled entry. 

 

Any optimizations would be hugely appreciated - thanks!

 

 

<?php-
while ($a = mssql_fetch_assoc($sql)){
	$b = "SELECT PANELID, DEVICEID, INPUTDEVID FROM EVTALMLINK WHERE ALID= " . $a['ALID'] . "";
	$b2 = mssql_query($b, $cxn);
		while ($c = mssql_fetch_assoc($b2)){
			$sql2 = "SELECT MAX(EVENT_TIME_UTC) as TIME FROM EVENTS WHERE (MACHINE = " . $c['PANELID'] . ") AND (EVENTID = " . $xxx . ") AND (EVENTTYPE = 4) AND (INPUTDEVID = " . $c['INPUTDEVID'] . ") AND (DEVID = " . $c['DEVICEID'] . ") AND (EVENT_TIME_UTC >= '" . $yester . "') GROUP BY DEVID, INPUTDEVID";
			$query2 = mssql_query($sql2, $cxn);

				while ($cc = mssql_fetch_assoc($query2)){
					$thearray[] = $c['PANELID'] . "|" . $c['DEVICEID'] . "|" . $c['INPUTDEVID'] . "|" . $cc['TIME'] . "|" . $xxx;
				}
		}
}


$thearray = array_unique($thearray);

foreach ($thearray as $vv => $ll){
	list($panel,$device,$input,$time,$type) = explode("|", $ll);
	$sql = "SELECT COUNT(*) as MYCOUNT FROM EVENTS WHERE (MACHINE = " . $panel . ") AND (EVENTID = " . $yyy . ") AND (EVENTTYPE = 4) AND (EVENT_TIME_UTC > '" . $time . "') AND (INPUTDEVID = " . $input . ") AND (DEVID = " . $device . ")";
	$query = mssql_query($sql, $cxn);
	$res = mssql_fetch_assoc($query);	
		if ($res['MYCOUNT'] == 0){
                            echo "......various code.....";
                       }

?>

[chaking]

Can anyone walk me through dumping some data to a temp file and then using the server to search through it so that I won't have to make so many calls to the db?

[Gamic]

You shouldn't need to have php looping through queries to prepare new queries in this way. Why not try and write one query that will give you the information you need?

 

select  el.PanelID, el.DeviceID, el.InputDevID, max(events.Event_Time_UTC) as time

from evtalmlink el join events on el.panelid = " . " + convert(nvarchar(20),machine.panelid) + " . "

where

eventid = 

and eventype = 4

--(etc)

group by el.panelid, el.deviceid, el.inputdevid

[/Code]

 

It's not perfect (as I am not sure entirely what you are trying to do).

[chaking]

Thanks, I'm pretty sure you're right - My sql statements aren't so great... I think I need a bit of help though - Here's the statements I have to run:

 

<?php
$eventids = "EVENTID = 8 OR EVENTID = 10 OR EVENTID = 2 OR EVENTID = 431 OR EVENTID = 18 OR EVENTID = 4 OR EVENTID = 6 OR EVENTID = 20 OR EVENTID = 5 OR EVENTID = 7 OR EVENTID = 76";

$sql1 = "SELECT NAME,PANELID FROM ACCESSPANE WHERE (PANELTYPE = 14 OR PANELTYPE = 19 OR PANELTYPE = 59 OR PANELTYPE = 63 OR PANELTYPE = 10) ORDER BY NAME ASC"

$sql2 = "SELECT DISTINCT DEVID FROM EVENTS WHERE (MACHINE = ".**(Panelid from sql1)**." ) AND (".$eventids.") AND (EVENTTYPE = 4) AND (EVENT_TIME_UTC >= '".$yesterday."')";

$sql3 = "SELECT DISTINCT INPUTDEVID, EVENTID FROM EVENTS WHERE (MACHINE = ".**(Panelid from sql1)**." ) AND (DEVID = ".**(devid from sql2)**.") AND (".$eventids.") AND (EVENTTYPE = 4) AND (EVENT_TIME_UTC >= '".$yester."')";

?>

 

So is it possible to combine all 3 into 1? I imagine so, but I'm having trouble doing it -

[Gamic]

I think this is the query that you want:

 

select 
ap.name, 	--From $sql1
ap.panelID, --From $sql1
e.devID, 	--From $sql2 and potentially redundent as you only used this data input into the third query.
e.inputdevid, --From $sql3
e.eventid,	--From $sql3
from accessPane ap 
join events e on ap.panelID = e.Machine 
where 
ap.paneltype in (14,19,59,63,10) 
and e.eventID in (8,10,2,431,18,4,6,20,5,7,76) 
and e.eventType = 4
and e.event_time_utc >= DateAdd(day,-1,getDate()) --This will be exactly 24 hours ago. You may need work out if you want to start at the beginning of the day
group by ap.name, ap.panelID, e.devID, e.inputDevID, e.eventID

[chaking]

That's awesome - thanks.  I'm learning slowly...