Jump to content

Archived

This topic is now archived and is closed to further replies.

chaking

Optimize Query

Recommended Posts

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

?>

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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 -

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×
×
  • 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.