MySQL Forums
Forum List  »  Performance

To store or calculate on the spot?
Posted by: newbie Shai
Date: July 23, 2011 09:15AM

Dear All,
I got few tables as below. So then what I do on any time I will calculate the total points collected based on each table via some of this queries like below. What I am using now is the sum function. I am confuse should I like run a monthly service which will total and store in separate table the total for each month rather than doing it live calculation? Which one is more effective?

Query 1
Select sum(tblDriverF2FPrograms.f2FProgramsPoints) As sumF2FPrograms FROM tblDriverF2FPrograms Where tblDriverF2FPrograms.driverID=".$driverID. " And tblDriverF2FPrograms.clientID=".$clientID." And Month(tblDriverF2FPrograms.dateTimeInsert)=".$monthValue. " And Year(tblDriverF2FPrograms.dateTimeInsert)=".$yearValue

Query 2
Select sum(tblDriverMerits.meritsPoints) As sumMerits FROM tblDriverMerits Where tblDriverMerits.driverID=".$driverID. " And tblDriverMerits.clientID=".$clientID." And Month(tblDriverMerits.dateTimeInsert)=".$monthValue. " And Year(tblDriverMerits.dateTimeInsert)=".$yearValue

Table 1
CREATE TABLE IF NOT EXISTS `tblDriverF2FPrograms` (
`driverF2FProgramsID` int(5) NOT NULL auto_increment,
`clientF2FProgramsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`f2FProgramsPoints` int(2) NOT NULL,
`driverF2FProgramsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverF2FProgramsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Table 2
CREATE TABLE IF NOT EXISTS `tblDriverMerits` (
`driverMeritsID` int(5) NOT NULL auto_increment,
`clientMeritsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`meritsPoints` int(2) NOT NULL,
`driverMeritsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverMeritsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Options: ReplyQuote


Subject
Views
Written By
Posted
To store or calculate on the spot?
3301
July 23, 2011 09:15AM
1086
August 10, 2011 07:22PM
1149
August 11, 2011 07:52PM
1003
August 12, 2011 07:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.