Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Unanswered: how to reduce excution time

    the following code runs fine
    Code:
    public class Final
    {
       private static PreparedStatement preparedStatement;
       private static ResultSet rs;
       public static void main(String[] args) throws NamingException, SQLException
       {
          int objectId;
          int clientId;
          DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
          // String gpstime = null;
          PreparedStatement preparedStatement = null;
          Connection conn=null;
            long Total=0;
            int b;
            int b2;
            int nano=0;
            int nano1=0;
             String disHour="";
             String disMinu="";
             String disSec="";
     ArrayList arr = new ArrayList();
             ArrayList arr1 = new ArrayList();
           try
          {
             Class.forName("com.mysql.jdbc.Driver");
             Connection conn1=DriverManager.getConnection("jdbc:mysql://108.108.8.4:3306/navl?","root", "ajmani@%");
             preparedStatement = conn1.prepareStatement("select gpstime from xydata a inner join fm4features f on a.ID=f.ID where a.objectId= '18'  AND a.clientId = '1' AND a.gpstime >= '2010-05-01 00:00:00'  AND a.gpstime <= '2010-05-25 23:59:59' AND f.DataId='1' AND f.value = '1'");
             rs = preparedStatement.executeQuery();
             long totalTimeInMillis=0;
            
             Set<String> dates = new HashSet<String>();
             ArrayList<Integer> l=new ArrayList<Integer>();
             ArrayList<Integer> m=new ArrayList<Integer>();
             ArrayList<Long> n=new ArrayList<Long>();
             Map <String, Long> datesAndTotal = new HashMap<String, Long>();
             DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
             Timestamp t1 = null;
             Timestamp t2 = null;
             int count  = 0;
             long sum = 0;
             long result=0;
             long results=0;
             while ( rs.next() )
             {
    
                String gpstime = rs.getString("GpsTime");
                if (dates.add(gpstime))
                {
                   Date d2 = rs.getDate(1);
                   t2 = rs.getTimestamp(1);
    
                   count++;
                   if (t1 == null )
                   {
                      nano = t2.getSeconds();
                      System.out.println(t2);
                      System.out.println(nano);
                    }
    
                   else
                   {
    
                      totalTimeInMillis = (t2.getTime() - t1.getTime()) / 1000;
                       String dateString = df.format(t2);
                               System.out.println("GPSTime " +gpstime+ " Time in seconds: " + totalTimeInMillis+ " seconds.");
                      if (datesAndTotal.containsKey(dateString))
                      {
                         m.add((totalTimeInMillis < 80) ? (int) totalTimeInMillis : (int) (totalTimeInMillis % 60));
                       
                          for (int y=0; y<m.size();)
                          {
                          sum+=m.get(y);
                          y++;
                        }
                          sum+= (datesAndTotal.get(dateString))/-1;
                          System.out.println(sum);
                          
                      }
                      datesAndTotal.put(dateString, sum);
                   }
                   t1 = t2;
                  
                }
    
    
    
    
             }           
                         List mapKeys = new ArrayList(datesAndTotal.keySet());
                         List mapValues = new ArrayList(datesAndTotal.values());
                         Collections.sort(mapValues);
                          Collections.sort(mapKeys);
                        LinkedHashMap Maps = new LinkedHashMap();
                        Iterator valueIt = mapValues.iterator();
                        while (valueIt.hasNext()) {
                        Object val = valueIt.next();
                         Iterator keyIt = mapKeys.iterator();
                        while (keyIt.hasNext()) {
                        Object key = keyIt.next();
                        if (datesAndTotal.get(key).toString().equals(val.toString()))
                        {
                        datesAndTotal.remove(key);
                        mapKeys.remove(key);
                        Maps.put(key, val);
                        break;
                                   }
                              }
                        }
    
                        
                        Iterator it1 = Maps.entrySet().iterator();
                        String prevKey="";
                        while (it1.hasNext()) {
                           Map.Entry entry = (Map.Entry) it1.next();
                           String presentKey=(String)entry.getKey();
                           if(prevKey.length()==0){
                                   System.out.println(presentKey+"=>"+entry.getValue());
                                   results=(Long) entry.getValue()+60;
                                   System.out.println(results);
                                 arr1.add(presentKey);
                                 int hours = (int) (results / 3600),
                                     remainder = (int) (results % 3600),
                                     minutes = remainder / 60,
                                     seconds = remainder % 60;
                                     
                                 disHour = (hours < 10 ? "0" : "") + hours;
                                 disMinu = (minutes < 10 ? "0" : "") + minutes;
                                 disSec = (seconds < 10 ? "0" : "") + seconds;
                                if (results==0 ||count<2)
                                         {
                                            //int se = Integer.parseInt(disSec);
                                            //String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
                                            System.out.println("00" + ":" +  00 + ":" + 00 + " hh:mm:ss");
                                            arr.add(00 + ":" +  00 + ":" + 00);
                                         }
                                    System.out.println(disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
                                    arr.add(disHour + ":" + disMinu + ":"+ disSec);
                                  
                         
                           }
                           else{
                                   Long present=(Long)entry.getValue();
                                   Long prev=(Long) Maps.get(prevKey);
                                   result=(Long) (present - prev)+30;
                                   System.out.println(""+presentKey+"=>"+result);
                                 System.out.println(result);
                                  arr1.add(presentKey);
                                 int hours = (int) (result / 3600),
                                     remainder = (int) (result % 3600),
                                     minutes = remainder / 60,
                                     seconds = remainder % 60;
    
                                 disHour = (hours < 10 ? "0" : "") + hours;
                                 disMinu = (minutes < 10 ? "0" : "") + minutes;
                                 disSec = (seconds < 10 ? "0" : "") + seconds;
                                       if (result==0||count < 2)
                                         {
                                            int se = Integer.parseInt(disSec);
                                            String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
                                            System.out.println("00" + ":" +  min + ":" + se + " hh:mm:ss");
                                            arr.add(00 + ":" +  00 + ":" + 00);
                                         }
    
                                     System.out.println(disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
                                    arr.add(disHour + ":" + disMinu + ":"+ disSec);
                                     
                                   }
                           prevKey=(String)presentKey;
    
                        
                          n.add(result);
                         }
    
                          n.add(results);
    
    
                             for (int i=0; i<n.size();)
                          {
                              Total+=n.get(i);
                              i++;
    
                          }
                             System.out.println(Total);
                                     int hours = (int) (Total / 3600),
                                     remainder = (int) (Total % 3600),
                                     minutes = remainder / 60,
                                     seconds = remainder % 60;
    
                                  disHour = (hours < 10 ? "0" : "") + hours;
                                  disMinu = (minutes < 10 ? "0" : "") + minutes;
                                   disSec = (seconds < 10 ? "0" : "") + seconds;
                                       if (Total==0||count < 2)
                                         {
                                            int se = Integer.parseInt(disSec);
                                            String min = (t2.getSeconds() < 10 ? "0" : "") + t2.getSeconds();
                                            System.out.println("00" + ":" +  min + ":" + se + " hh:mm:ss");
                                            arr.add(00 + ":" +  00 + ":" + 00);
                                         }
    
                                     System.out.println("Total Time=>" +disHour +":"+ disMinu+":"+disSec+ " hh:mm:ss");
                                    
                 
          }catch(Throwable th)
    
          {
             Logger.getLogger(testing1.class.getName()).log(Level.SEVERE, null, th);
          }finally
          {
             if(preparedStatement!=null)
                preparedStatement.close();
             if(conn!=null)
                conn.close();
    
          }
          for(int i = 0; i < arr.size(); )
    {  arr.get(i);
       System.out.println(arr1.get(i));
       System.out.println(arr.get(i));
    
      i++;
      }
      
    }
    
    }
    whenever i put this into jsp page and run the server it takes so much time to take run the query
    how to reduce the execution speed

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Could be a lot of factors(network congestion, bandwidth, number of rows involved, etc.),
    These are just some ideas to help the database portion:
    -make sure there is an index on the 'id' column in both tables
    -optionally have index on other columns especially(gpstime)


    Code:
    select gpstime
     from  xydata a inner join fm4features f on a.id  =  f.id
    where  a.objectid  =  '18'
     and   a.clientid  =  '1'
     and   a.gpstime  >=  '2010-05-01 00:00:00'
     and   a.gpstime  <=  '2010-05-25 23:59:59'
     and   f.dataid    =  '1'
     and   f.value     =  '1'
    hth

Posting Permissions

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