[SOLVED] creating a faster mssql query

[knowram]

Ok I have a series of tables that I need to sort through. meaning get one peace of info from one table to get the next peace form the next table and so on. My problem is I only know how to write the code to do it in that order.

 

My code

 

	if (empty($_REQUEST['shInOctets_Avg']) || empty($_REQUEST['shOutOctets_Avg']) || empty($_REQUEST['shInOctets_Max']) || empty($_REQUEST['shOutOctets_Max'])){

	set_time_limit(240);

	foreach ($Print_Order as $key){

		$resultDID = mssql_query("SELECT nDeviceID FROM Device WHERE sDisplayName = '$Device_Name[$key]' AND nDeviceTypeID = '3'") or
		 				die ("can't select nDeviceID from Device");

		$rowDID = mssql_fetch_array($resultDID, MSSQL_ASSOC);

		$resultPSM = mssql_query("SELECT nPivotStatisticalMonitorTypeToDeviceID FROM PivotStatisticalMonitorTypeToDevice WHERE nDeviceID = '$rowDID[nDeviceID]' and nStatisticalMonitorTypeID = '1'") or
						die ("Can't select nPivotStatisticalMonitorTypeToDeviceID");

		$rowPSM = mssql_fetch_array($resultPSM, MSSQL_ASSOC) or
				die ("Can't find pivit number");

		$Interfaces = explode("|",$Interface[$key]);

		foreach ($Interfaces as $Int){
			if ($Int != "" && preg_match('/^(Serial)/',$Int)){

				unset($IfInOctets_Max);

				$Int_o2 = $Int.'-802.1Q vLAN subif';

				$resultSIIID = mssql_query("SELECT nStatisticalInterfaceIdentificationID FROM StatisticalInterfaceIdentification WHERE nPivotStatisticalMonitorTypeToDeviceID = '$rowPSM[nPivotStatisticalMonitorTypeTo]' and sIfDescr = '$Int'") or
										die ("Canb't Select nStatisticalInterfaceIdentificationID");

				$rowSIIID = mssql_fetch_array($resultSIIID, MSSQL_ASSOC) or
						die ("Can't find SIIID");

				$resultSI = mssql_query("SELECT * FROM StatisticalInterface WHERE nStatisticalInterfaceIdentificationID = '$rowSIIID[nStatisticalInterfaceIdentific]'") or
											die ("can't select StatisticalInterface");

				$rowSI = mssql_fetch_array($resultSI, MSSQL_ASSOC);

				$nIfSpeedIn[$key] = $rowSI['nIfSpeedIn'];
				$nIfSpeedOut[$key] = $rowSI['nIfSpeedOut'];

				while ($rowSI = mssql_fetch_array($resultSI, MSSQL_ASSOC)){
					if ((strtotime($rowSI['dPollTime']) <= strtotime("now")) && (strtotime($rowSI['dPollTime']) >= strtotime("-4 week"))){

						if (empty($IfInOctets_Max)) {
							$IfInOctets_Max = $rowSI['nIfInOctets_Max'];
						}else if ($IfInOctets_Max < $rowSI['nIfInOctets_Max']) {
							$IfInOctets_Max = $rowSI['nIfInOctets_Max'];
						}
						if (empty($nIfOutOctets_Max[$key])) {
							$nIfOutOctets_Max[$key] = $rowSI['nIfOutOctets_Max'];
						}else if ($nIfOutOctets_Max[$key] < $rowSI['nIfOutOctets_Max']) {
							$nIfOutOctets_Max[$key] = $rowSI['nIfOutOctets_Max'];
						}
					}
				}
				if (empty($nIfInOctets_Max[$key])) { $nIfInOctets_Max[$key] = $IfInOctets_Max; }
				else { $nIfInOctets_Max[$key] = $nIfInOctets_Max[$key].'|'.$IfInOctets_Max; }
			}
		}
	}
}

 

My but the problem is that takes for ever given that for each interface on each device it has to go through the table of  240174 entry's (at last count) to get the info I am looking for. There has to be a better way.

I would think that there should be a way to set it up so that it only has to go through that last big table once and pic out the info I need for each interface on each device. But I am not sure how to do this via code.

 

Let me know if how I explained my problem doesn't make any sense.

Any suggestions are great

Thanks allot everyone.

[MadTechie]

Basically you need to learn JOINs..

 

i'll review your code a little more see if i can write one

[MadTechie]

OK that will take me a while plus without the database to test each step.. i really suggect you do some research!

 

SELECT nPivotStatisticalMonitorTypeToDeviceID 
FROM PivotStatisticalMonitorTypeToDevice AS PSMTTD
LEFT JOIN Device as nDID ON ( nDID.nDeviceID = PSMTTD.nDeviceID 
AND nDeviceTypeID = '3')
WHERE sDisplayName = '$Device_Name[$key]'
AND nStatisticalMonitorTypeID = '1'"

[knowram]

Thanks for the help i didn't even know about JOINs I will look into it.

 

 

[knowram]

An after though can you give me a syntax example. Or maybe point me in the right direction. the only place I know to search for stuff like that is php.net and it seems there is a php function join that is different.

 

 

Thanks for the help

[MadTechie]

Normally i would point you to

mssql.com

but your using MSSQL so try this

[knowram]

I found a cool example on w3schools http://www.w3schools.com/sql/sql_join.asp but there example only deals with 2 tables I am not sure a see how to incorporate a thirded. The info I want is three tables deep.

[MadTechie]

Its the same idea just add another Join they can be a pain when you start but oh so powerful

[knowram]

Ok so this is what I have come up with

 

foreach ($Print_Order as $key){

		$resultSIIID = mssql_query("
		SELECT StatisticalInterfaceIdentification.nStatisticalInterfaceIdentificationID
		FROM StatisticalInterfaceIdentification
		INNER JOIN PivotStatisticalMonitorTypeToDevice
		ON StatisticalInterfaceIdentification.nPivotStatisticalMonitorTypeToDeviceID = PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID
		INNER JOIN Device
		ON sDisplayName = '$Device_Name[$key]' AND nDeviceTypeID = '3'") or
				die ("can't select StatisticalInterface");

		$rowSIIID = mssql_fetch_array($resultSIIID, MSSQL_ASSOC);

		print_r ($rowSIIID);
		echo '<br>';

	}

 

But for some reason $rowSIIID is the same for each $key

 

Any ideas?

[knowram]

Ok got it all figured out and yeah that is allot faster. Thanks for the help

[MadTechie]

Cool,

 

if solved please click solved