Results 1 to 7 of 7
  1. #1
    iresha is offline Member
    Join Date
    May 2008
    Posts
    1
    Rep Power
    0

    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();

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

    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.

  3. #3
    sukatoa's Avatar
    sukatoa is offline Senior Member
    Join Date
    Jan 2008
    Location
    Cebu City, Philippines
    Posts
    556
    Rep Power
    7

    Default

    Please, use codetags...

    put [/code] on the bottom of your code and [code] above your code.
    freedom exists in the world of ideas

  4. #4
    rjuyal's Avatar
    rjuyal is offline Senior Member
    Join Date
    Mar 2008
    Location
    Delhi, India
    Posts
    194
    Rep Power
    7

    Default let me do the honor

    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();
    i am the future

  5. #5
    sukatoa's Avatar
    sukatoa is offline Senior Member
    Join Date
    Jan 2008
    Location
    Cebu City, Philippines
    Posts
    556
    Rep Power
    7

    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.

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

    Java 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 )
    Last edited by sukatoa; 05-09-2008 at 01:26 AM.
    freedom exists in the world of ideas

  6. #6
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    19

  7. #7
    danielstoner's Avatar
    danielstoner is offline Senior Member
    Join Date
    Apr 2008
    Location
    Canada
    Posts
    191
    Rep Power
    7

    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

Similar Threads

  1. Hello, first time here.
    By ludragon in forum Introductions
    Replies: 2
    Last Post: 01-03-2008, 05:03 AM
  2. help with dates and time
    By osval in forum New To Java
    Replies: 3
    Last Post: 12-12-2007, 12:41 PM
  3. DataObject with the time given by me
    By garinapavan in forum New To Java
    Replies: 2
    Last Post: 08-07-2007, 06:33 PM
  4. Replies: 1
    Last Post: 08-07-2007, 07:53 AM
  5. Error: convert from String to long
    By bbq in forum New To Java
    Replies: 1
    Last Post: 06-29-2007, 07:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •