It takes very long time.....

[iresha]

Hi all,

I have to get leave records for a month from database. There are over 700 employees in the database. And also each employee data
must check with each date in a month(that means 31 or 30 days repeating) I created FOR loop for this one. but it takes very long time(over 20 mins)
Is there any easy way to get the data.
Let me know anyone know to do this.

Thankyou all

this is part of my java code.

//for loop for displaying the details of each date of the month for an employee
for(int i=1;i<=(int)cols;i++)
{
colDateDB = Utility.formatDateToDB(colDate, conn);

try
{
group = Utility.getEmployeeShiftGroup(nic, colDateDB, conn);
}
catch(Exception e){}

if(group == null || group.length() <= 0)
isRoster = false;
else
{
pRoster.clearParameters();

pRoster.setString(1, colDateDB);//***date should be given
pRoster.setString(2, group);

rsRoster = pRoster.executeQuery();

if(rsRoster.next())
isRoster = true;
else
isRoster = false;
}//end of else

cal.setTime(Utility.formatDateToDB2(colDate));
day = Utility.getDayOfWeekName(cal.get(GregorianCalendar .DAY_OF_WEEK));

pLeave.clearParameters();
pLeave.setString(1, colDateDB);
pLeave.setString(2, nic);
rsLeave = pLeave.executeQuery();

var = "";

if(isRoster) //roster employee
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = ""; //blank for full work
if(rsRoster.getString(4).equalsIgnoreCase("N"))
var = "N";

if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";

}//end of if(date equals)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}
}//end of if(isRsAtt)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}

}//end of if(isRoster)

else //general employee
{
isHoliday = false;

pHoliday.clearParameters();
pHoliday.setString(1, colDateDB);
rsHoliday = pHoliday.executeQuery();

if(rsHoliday.next())
isHoliday = true;

if(day.equalsIgnoreCase("Sunday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else
var = "SN";
}
else
var = "SN";
}//end of Sunday

else if(day.equalsIgnoreCase("Saturday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of Saturday

else //week day
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
}//end of if(date equals)

else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of weekday


if(rsHoliday!=null)
rsHoliday.close();
}//end general emp
if ("NP".equals(var) && "N".equals(processAttendance) ){
var = "";
}


if(rsLeave!=null)
rsLeave.close();

if(rsRoster!=null)
rsRoster.close();

[Eranga]

I can't test your code at time. Check that in your for loops, executing any initializations like data connection open/close and such. Those type of process really slow down your application execution process.

[sukatoa]

Please, use codetags...

put [/code] on the bottom of your code and [code] above your code.

[rjuyal]

Code:

 //for loop for displaying the details of each date of the month for an employee
for(int i=1;i<=(int)cols;i++)
{
colDateDB = Utility.formatDateToDB(colDate, conn);

try
{
group = Utility.getEmployeeShiftGroup(nic, colDateDB, conn);
}
catch(Exception e){}

if(group == null || group.length() <= 0)
isRoster = false;
else
{
pRoster.clearParameters();

pRoster.setString(1, colDateDB);//***date should be given
pRoster.setString(2, group);

rsRoster = pRoster.executeQuery();

if(rsRoster.next())
isRoster = true;
else
isRoster = false;
}//end of else

cal.setTime(Utility.formatDateToDB2(colDate));
day = Utility.getDayOfWeekName(cal.get(GregorianCalendar .DAY_OF_WEEK));

pLeave.clearParameters();
pLeave.setString(1, colDateDB);
pLeave.setString(2, nic);
rsLeave = pLeave.executeQuery();

var = "";

if(isRoster) //roster employee
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = ""; //blank for full work
if(rsRoster.getString(4).equalsIgnoreCase("N"))
var = "N";

if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";

}//end of if(date equals)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}
}//end of if(isRsAtt)
else
{
if(rsRoster.getString(4).equalsIgnoreCase("O"))
var = "H";
else
{
if(rsLeave.next()) //check whether he has applied for leave
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}

}//end of if(isRoster)

else //general employee
{
isHoliday = false;

pHoliday.clearParameters();
pHoliday.setString(1, colDateDB);
rsHoliday = pHoliday.executeQuery();

if(rsHoliday.next())
isHoliday = true;

if(day.equalsIgnoreCase("Sunday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else
var = "SN";
}
else
var = "SN";
}//end of Sunday

else if(day.equalsIgnoreCase("Saturday"))
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;
}//end of if(date equals)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(!workOnSat.equalsIgnoreCase("Y"))
var = "S";
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of Saturday

else //week day
{
if(isRsAtt)
{
if(Utility.formatDateToDB2(colDate).equals(rsAtt.g etDate(1)))
{
var = "";
if(!rsAtt.next())
isRsAtt = false;

if(rsLeave.next() && rsLeave.getFloat(3) == 0.5 && rsLeave.getString(4) != null)
var = "HF";
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
}//end of if(date equals)

else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of if(isRs)
else if(isHoliday)
var = "H";
else
{
if(rsLeave.next())
var = rsLeave.getString(2).trim();
else if(Utility.isOnShortLeave(colDateDB, nic, conn))
var = "ShL";
else //absent no pay
var = "NP";
}
}//end of weekday


if(rsHoliday!=null)
rsHoliday.close();
}//end general emp
if ("NP".equals(var) && "N".equals(processAttendance) ){
var = "";
}


if(rsLeave!=null)
rsLeave.close();

if(rsRoster!=null)
rsRoster.close();

[sukatoa]

Thanks rjuyal

@OP, Just a quick....

When optimizing such code,
The fastest loop that i've ever read is the while loop that compares the value to zero. second is the boolean.

while( something != 0){ ... }

while( true ){ ... }

Any other process that can just initialize once, put them away from the loop.

Code:

while( something != 0){
      Double d = value / ( 3 + 2 * 3);
      Something executes
}

instead,

Code:

Double d;
Double t1 = 9.0  ( MDAS rule )
while( something != 0){
      d = value / t1;
      Something executes
}

My example really too far from the given codes, but you can get some ideas... ( i hope )

[Eranga]

Loops, always make troubles on any applications. Infinite loop specially.

[danielstoner]

What about your DB? Are tables indexed? How are they linked? What DB is that?
Two things affect the performance of this kind of scenario:
- What DB you use and how the schema is organized
- How many round trips you do from application to DB server