[SOLVED] So ... Can You Optimize This Query!?

[libertyct]

I am using this following code to generate a summary page on a web app im developing. I have used both the CREATE VIEW and CREATE TABLE techniques but performance is still an issue it takes an avg of 3 - 5 seconds for the summary page to load.

 

At this point i have less than 50 records in my database but im expecting the system to handle something like 1000+ records. Can anyone recommend ways i can improve performance?

 

Thnx!

 

<?php

 

#business block

 

$drop_business_backupview = $db->sql_query("

DROP TABLE IF EXISTS business_backupview;");

 

$drop_business_priorityview = $db->sql_query("

DROP TABLE IF EXISTS business_priorityview;");

 

$drop_business_totalview = $db->sql_query("

DROP TABLE IF EXISTS business_totalview;");

 

#region block

 

$drop_region_backupview = $db->sql_query("

DROP TABLE IF EXISTS region_backupview;");

 

$drop_region_priorityview = $db->sql_query("

DROP TABLE IF EXISTS region_priorityview;");

 

$drop_region_totalview = $db->sql_query("

DROP TABLE IF EXISTS region_totalview;");

 

#manager block

 

$drop_mngr_backupview = $db->sql_query("

DROP TABLE IF EXISTS manager_backupview;");

 

$drop_mngr_priorityview = $db->sql_query("

DROP TABLE IF EXISTS manager_priorityview;");

 

$drop_mngr_totalview = $db->sql_query("

DROP TABLE IF EXISTS manager_totalview;");

 

#-------------------------------------------------------------------------------

 

#business block

 

$create_business_backupview = $db->sql_query("

CREATE TABLE business_backupview AS

SELECT fldBusinessID, fldBusinessName, COUNT(*) AS fldBackupCount

FROM tblNominee nomn

LEFT JOIN tblBusiness busn ON (nomn.fldBusiness = busn.fldBusinessID)

WHERE fldHRPersonnel = $id

AND fldPriority = 1

GROUP BY fldBusinessName ASC;");

 

$create_business_priorityview = $db->sql_query("

CREATE TABLE business_priorityview AS

SELECT fldBusinessID, fldBusinessName, COUNT(*) AS fldPriorityCount

FROM tblNominee nomn

LEFT JOIN tblBusiness busn ON (nomn.fldBusiness = busn.fldBusinessID)

WHERE fldHRPersonnel = $id

AND fldPriority = 0

GROUP BY fldBusinessName ASC;");

 

$create_business_totalview = $db->sql_query("

CREATE TABLE business_totalview AS

SELECT fldBusinessID, fldBusinessName, COUNT(*) AS fldTotalCount

FROM tblNominee nomn

LEFT JOIN tblBusiness busn ON (nomn.fldBusiness = busn.fldBusinessID)

WHERE fldHRPersonnel = $id

GROUP BY fldBusinessName ASC;");

 

#region block

 

$create_region_backupview = $db->sql_query("

CREATE TABLE region_backupview AS

SELECT fldRegionID, fldRegionName, COUNT(*) AS fldBackupCount

FROM tblNominee nomn

LEFT JOIN tblRegion regn ON (nomn.fldRegion = regn.fldRegionID)

WHERE fldHRPersonnel = $id

AND fldPriority = 1

GROUP BY fldRegionName ASC;");

 

$create_region_priorityview = $db->sql_query("

CREATE TABLE region_priorityview AS

SELECT fldRegionID, fldRegionName, COUNT(*) AS fldPriorityCount

FROM tblNominee nomn

LEFT JOIN tblRegion regn ON (nomn.fldRegion = regn.fldRegionID)

WHERE fldHRPersonnel = $id

AND fldPriority = 0

GROUP BY fldRegionName ASC;");

 

$create_region_totalview = $db->sql_query("

CREATE TABLE region_totalview AS

SELECT fldRegionID, fldRegionName, COUNT(*) AS fldTotalCount

FROM tblNominee nomn

LEFT JOIN tblRegion regn ON (nomn.fldRegion = regn.fldRegionID)

WHERE fldHRPersonnel = $id

GROUP BY fldRegionName ASC;");

 

#manager block

 

$create_mngr_backupview = $db->sql_query("

CREATE TABLE manager_backupview AS

SELECT fldManagerID, COUNT(*) AS fldBackupCount

FROM tblNominee nomn

LEFT JOIN tblManager mngr ON (nomn.fldManager = mngr.fldManagerID)

WHERE fldHRPersonnel = $id

AND fldPriority = 0

GROUP BY fldManagerID ASC;");

 

$create_mngr_priorityview = $db->sql_query("

CREATE TABLE manager_priorityview AS

SELECT fldManagerID, COUNT(*) AS fldPriorityCount

FROM tblNominee nomn

LEFT JOIN tblManager mngr ON (nomn.fldManager = mngr.fldManagerID)

WHERE fldHRPersonnel = $id

AND fldPriority = 1

GROUP BY fldManagerID ASC;");

 

$create_mngr_totalview = $db->sql_query("

CREATE TABLE manager_totalview AS

SELECT fldManagerID, COUNT(*) AS fldTotalCount

FROM tblNominee nomn

LEFT JOIN tblManager mngr ON (nomn.fldManager = mngr.fldManagerID)

WHERE fldHRPersonnel = $id

GROUP BY fldManagerID ASC;");

 

?>

 

 

[attachment deleted by admin]

[fenway]

What needs optimization? Why keep dropping tables?

[libertyct]

Hi Fenway,

 

Well my mistake, i forgot to mention that this is a dynamic summary page, in the sense that several mod accounts will have access to the page but with a different summary generated each time they visit the page, the summary is generated and filtered by the mods "Id" for example:

 

$create_business_backupview = $db->sql_query("

CREATE TABLE business_backupview AS

SELECT fldBusinessID, fldBusinessName, COUNT(*) AS fldBackupCount

FROM tblNominee nomn

LEFT JOIN tblBusiness busn ON (nomn.fldBusiness = busn.fldBusinessID)

WHERE fldHRPersonnel = $id AND fldPriority = 1

GROUP BY fldBusinessName ASC;");

 

this will create a table that shows a view of all businesses related to this mod.

 

the reason why i drop the tables is so that each time someone opens the page e.g. Id="2" , first any previous summary tables or temporary tables are deleted, then new summary tables are created based on the current users id=2. when the next user logs in id="6" , then the temporary tables based on user id="6" are created.

 

initially i had done this using views but my stupid webhost just informed me today they do not support creation of views as of now so thats why i am resorting to creating temporary tables each time the pge loads :-{

[Barand]

No wonder you have a performance issue.

 

If all that is to produce the page illustrated, all you need are 3 or 4 queries on the existing tables :

 

tblNominee

tblRegion

tblManager

tblBusiness

 

without any of that timewasting dropping and creating tables every time.

[fenway]

No wonder you have a performance issue.

 

If all that is to produce the page illustrated, all you need are 3 or 4 queries on the existing tables :

 

tblNominee

tblRegion

tblManager

tblBusiness

 

without any of that timewasting dropping and creating tables every time.

 

That was my hunch, too....

[libertyct]

ok. well thast a relief to know, so how can i break this down into a smaller query? can you guys help me with the query just by reading the info i have provided or would you like further details? maybe a diagram of the database structure might help?

 

thnx in advance

[Barand]

As an example, this will get your three "manager block" totals

 

SELECT fldManagerID, COUNT(*) AS fldTotalCount,

SUM(IF(fldPriority = 0, 1, 0 )) AS fldBackupCount,

SUM(IF(fldPriority = 1, 1, 0 )) AS fldPriorityCount

FROM tblNominee nomn

LEFT JOIN tblManager mngr ON (nomn.fldManager = mngr.fldManagerID)

WHERE fldHRPersonnel = $id

GROUP BY fldManagerID

[libertyct]

thank you evry much. works perfectly!