Jump to content

Optimize Query


chaking

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

?>

Link to comment
Share on other sites

  • 2 weeks later...
  • 4 weeks later...

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

Link to comment
Share on other sites

  • 3 weeks later...

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 -

Link to comment
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

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.

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