Register FAQ Social Groups Calendar Search Today's Posts Mark Forums Read

Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 05-08-2008, 11:35 AM
Member
  
Join Date: May 2008
Posts: 1
Rep Power: 0
iresha is on a distinguished road
Default It takes very long time.....
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();
Bookmark Post in Technorati
Reply With Quote
  #2 (permalink)  
Old 05-08-2008, 11:40 AM
Eranga's Avatar
Moderator
  
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 7,513
Rep Power: 11
Eranga has a spectacular aura aboutEranga has a spectacular aura about
Send a message via Yahoo to Eranga
Default
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.
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
Someone helped you? their helpful post.
Help:Forums FAQ|How To Ask Questions The Smart WayResources:The Java Tutorials|Glossary for Java|NetBeans IDE|Sun DownloadsWeb:WritOnceTips:Is your IDE the best?|Which Application Server?
Bookmark Post in Technorati
Reply With Quote
  #3 (permalink)  
Old 05-08-2008, 02:25 PM
sukatoa's Avatar
Senior Member
  
Join Date: Jan 2008
Location: Cebu City, Philippines
Posts: 540
Rep Power: 3
sukatoa is on a distinguished road
Send a message via Yahoo to sukatoa
Default
Please, use codetags...

put [/code] on the bottom of your code and [code] above your code.
__________________
A specific, detailed, simple, well elaborated, and "tested before asking" question may gather more quick replies. hopefully
Bookmark Post in Technorati
Reply With Quote
  #4 (permalink)  
Old 05-08-2008, 02:46 PM
rjuyal's Avatar
Senior Member
  
Join Date: Mar 2008
Location: Delhi, India
Posts: 195
Rep Power: 2
rjuyal is on a distinguished road
Default let me do the honor
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();
__________________
i am the future
Bookmark Post in Technorati
Reply With Quote
  #5 (permalink)  
Old 05-08-2008, 02:56 PM
sukatoa's Avatar
Senior Member
  
Join Date: Jan 2008
Location: Cebu City, Philippines
Posts: 540
Rep Power: 3
sukatoa is on a distinguished road
Send a message via Yahoo to sukatoa
Default
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 )
__________________
A specific, detailed, simple, well elaborated, and "tested before asking" question may gather more quick replies. hopefully
Last edited by sukatoa; 05-09-2008 at 02:26 AM.
Bookmark Post in Technorati
Reply With Quote
  #6 (permalink)  
Old 05-09-2008, 04:28 AM
Eranga's Avatar
Moderator
  
Join Date: Jul 2007
Location: Colombo, Sri Lanka
Posts: 7,513
Rep Power: 11
Eranga has a spectacular aura aboutEranga has a spectacular aura about
Send a message via Yahoo to Eranga
Default
Loops, always make troubles on any applications. Infinite loop specially.
__________________
Use an appropriate Subject. "Help, urgent!" isn't one.
Someone helped you? their helpful post.
Help:Forums FAQ|How To Ask Questions The Smart WayResources:The Java Tutorials|Glossary for Java|NetBeans IDE|Sun DownloadsWeb:WritOnceTips:Is your IDE the best?|Which Application Server?
Bookmark Post in Technorati
Reply With Quote
  #7 (permalink)  
Old 05-11-2008, 03:31 AM
danielstoner's Avatar
Senior Member
  
Join Date: Apr 2008
Location: Canada
Posts: 191
Rep Power: 2
danielstoner is on a distinguished road
Default
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
__________________
Daniel @ [www.littletutorials.com]
Language is froth on the surface of thought
Bookmark Post in Technorati
Reply With Quote
Reply

Bookmarks

« How to set a max response time in webservice client | How to use JWNL in java program »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hello, first time here. ludragon Introductions 2 01-03-2008 06:03 AM
help with dates and time osval New To Java 3 12-12-2007 01:41 PM
DataObject with the time given by me garinapavan New To Java 2 08-07-2007 07:33 PM
Error: Cannot access protected member long getTimeInMillis() in class Calendar cachi Advanced Java 1 08-07-2007 08:53 AM
Error: convert from String to long bbq New To Java 1 06-29-2007 08:23 PM


All times are GMT +2. The time now is 08:31 AM.

Java Tips | Linux Archive

Contact Us - Java Forums - Sitemap - Top

VBulletin, Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2009, Crawlability, Inc.
Copyright ©2006 - 2007, www.java-forums.org