6 Replies - 744 Views - Last Post: 04 August 2008 - 04:00 AM Rate Topic: -----

#1 commanderderjukes  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 28-January 08

query speed

Post icon  Posted 30 July 2008 - 02:40 AM

public static void inserttodatabase(TimeTable[][] tt)
  {  conn=database.createconnection();
	 TimeTable.insertcf(conn);
	 for(int i=0;i<tt.length;i++)
	   { for(int j=0;j<tt[i].length;j++)
		{   if(tt[i][j].isEmpty())
			 continue;
			for(int k=0;k<tt[i][j].size();k++)
			{
  database.update("insert into tt_data(day_id,period_id,venue_id,course_id,session,runtype,tt_type,userid) values("+i
   +","+j+","+tt[i][j].get(k).venue_id  +","+tt[i][j].get(k).course_id +","+session+","+runtype+",'"+tt_type+"'"+","+tt[i][j].get(k).user_id+");",conn);   
	   
		   TBar.out.jTextArea1.append("update succesfull!!!  \n ");
	
			}
		 }
		}  
	 System.out.println("timetable inserted into  database ");




i am writing an application which requires some substantial amount of database access.
the above query enables me to update a table in the database . the problem is that the database updates takes tooooooo long!!!!!! i recorded about 2000 records being put into the database at the end of the day from the
variable in the program..it took about 1 hour to execute all the queries
is there any way of making the execution of my queries faster . i heard about prioritization of queries but i dont know much about that.

also in the course of the program i make a lot of queries into and out of the database.this slows down my app.i am thinking about putting everything that i would need into memory and then running everything from memory with no database access.the catch is the size of the data. its a timetabling program so i would need data about students,venues,preferences,lectures ... all in all roughly 300,000 integers in memory if my calculations are correct .what do you guys think????

Is This A Good Question/Topic? 0
  • +

Replies To: query speed

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: query speed

Posted 31 July 2008 - 02:32 AM

you can do 2 very easy and obvious things to speed things up.
First: remove TBar.out.jTextArea1.append ... i bet that will make a big difference.
Second: put tt[i][j].get(k) in a temporary var and use that instead of all those array accesses everywhere. That might help a litle bit.

But if you realy want to know why things are so slow, why dont you just time the things?
Put the microtime in a variable before you do the query, execute the query, then substract the stored microtime from the current microtime and display "the query took xxx milliseconds"
Same goes for all other stuff in those loops.
That is the only way you are realy going to know what is consuming so much time.
I suspect by the way it is you database, but to be sure you might want to time that TBar.out.jTextArea1.append , interfaces are known to get slow when you pump thousands of lines in them.

You should test with a modest amount of inserts first, say 100. If it is purely your database that is being a slug, you know where to look further.
Was This Post Helpful? 0
  • +
  • -

#3 commanderderjukes  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 28-January 08

Re: query speed

Posted 31 July 2008 - 02:46 AM

[thanks for the hints trogdor :pirate: :pirate: :pirate: . will get back to you when i have tried them out :^: :^: :^:
Was This Post Helpful? 0
  • +
  • -

#4 DeCompile  Icon User is offline

  • D.I.C Regular

Reputation: 19
  • View blog
  • Posts: 301
  • Joined: 20-July 08

Re: query speed

Posted 31 July 2008 - 03:35 AM

You haven't really stated which database you're using to store your data.

I'm guessing it's SQL as most people use this.

Check the condition of the database, there are some pretty simple fixes for sluggish SQL databases.

Check all your tables have Primary Keys etc.
Was This Post Helpful? 0
  • +
  • -

#5 commanderderjukes  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 28-January 08

Re: query speed

Posted 31 July 2008 - 06:36 AM

View PostDeCompile, on 31 Jul, 2008 - 03:35 AM, said:

You haven't really stated which database you're using to store your data.

I'm guessing it's SQL as most people use this.

Check the condition of the database, there are some pretty simple fixes for sluggish SQL databases.

Check all your tables have Primary Keys etc.

the database is indexed and has primary keys? i am wondering what primary keys have got to do with the speed of and insert anyways?
Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: query speed

Posted 31 July 2008 - 09:38 AM

Depends, if there is a foreign key to be checked, the table that contains it better have an index.
But as i said, time the results, then you know where all that processing time is spent.
Was This Post Helpful? 0
  • +
  • -

#7 commanderderjukes  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 28-January 08

Re: query speed

Posted 04 August 2008 - 04:00 AM

View PostTrogdor, on 31 Jul, 2008 - 09:38 AM, said:

Depends, if there is a foreign key to be checked, the table that contains it better have an index.
But as i said, time the results, then you know where all that processing time is spent.

 String query;
	int day_id,period_id,course_id,venue_id,user_id=0;
   int   count=0;
	conn=database.createconnection();
  long cinsert=System.currentTimeMillis();
	  TimeTable.insertcf(conn);
	   System.out.println("THE AMOUNT OF TIME IT TOOK TO INSERT THE CF IS  "+(System.currentTimeMillis()-cinsert)+"milliSECONDS");
   cinsert=System.currentTimeMillis();
  /***
   try{ 
  PreparedStatement prep2=conn.prepareStatement("insert LOW_PRIORITY into " +
	 "tt_data(day_id,period_id,venue_id,course_id,session,runtype,tt_type,userid) VALUES(?,?,?,?,?,?,?,?)")	;
   for(int i=0;i<tt.length;i++)
	   { for(int j=0;j<tt[i].length;j++)
   {   if(tt[i][j].isEmpty())
			 continue;
			for(int k=0;k<tt[i][j].size();k++)
			{count++;
		prep2.setInt(1, i);
		prep2.setInt(2, j);
		prep2.setInt(3,tt[i][j].get(k).venue_id);
		prep2.setInt(4,tt[i][j].get(k).course_id);
	   prep2.setInt(5,session);
	   prep2.setInt(6,runtype);
	   prep2.setString(7,tt_type);	 
	   prep2.setInt(8,tt[i][j].get(k).user_id);
	   prep2.addBatch();
	   if(count%200==0)
		  prep2.executeBatch();
	   
			}
		 }}
	 prep2.executeBatch();
	 System.out.println("THE AMOUNT OF TIME IT TOK TO INSERT TTDATA IS  "+(System.currentTimeMillis()-cinsert)+" milliSECONDS");

   prep2.close();
 
 }
  catch(SQLException ex)   
  {
	ex.printStackTrace();
  }	
	   
 *
  **/

	 query="insert LOW_PRIORITY into tt_data(day_id,period_id," +
			 "venue_id,course_id,session,runtype,tt_type,userid) VALUES";
	 for(int i=0;i<tt.length;i++)
	   { for(int j=0;j<tt[i].length;j++)
		{   if(tt[i][j].isEmpty())
			 continue;
			for(int k=0;k<tt[i][j].size();k++)
			{  count++;
			   if(count==1)
			   {
			   day_id=i;
			   period_id=j;
			   course_id=tt[i][j].get(k).course_id;
			   venue_id=tt[i][j].get(k).venue_id;
			   user_id=tt[i][j].get(k).user_id;
				query=query+"("+day_id+","+period_id+","+venue_id  +","+course_id +","+session+","+runtype+",'"+tt_type+"'"+","+user_id+")";   
				}
			   else 
			   {day_id=i;
			   period_id=j;
			   course_id=tt[i][j].get(k).course_id;
			   venue_id=tt[i][j].get(k).venue_id;
			   user_id=tt[i][j].get(k).user_id;
			  query=query+",("+day_id+","+period_id+","+venue_id  +","+course_id +","+session+","+runtype+",'"+tt_type+"'"+","+user_id+")";   
				 }   
			
			   
	   }
		 }
		}  
   query=query+";";  
System.out.println("THE AMOUNT OF TIME IT TOK TO INSERT TTDATA IS  "+(System.currentTimeMillis()-cinsert)+" milliSECONDS");
		database.update(query, conn);
   
	 System.out.println("timetable inserted into  database ");
	   database.closeconnection(conn); 
	return 1; 
  }


hello . have replaced the array values with variables. have also timed the speed of the database insertions of all the data. removing the component which displayed every insert made it a bit faster. i think the speed is now better.the inserts are also faster. i used a single insert update with multiple values
the comments in my code are prepared statements that are tried writing.it uses the batch prepared statements to send the queries to the database.i add several statements together and when it reaches a particular number it sends the queries to the database to be processed. however,the speed of the prepared statements inserts were slower than putting all the queries into one single insert statement with multiple values.do prepared statements get faster after you reach a particular threshold of the number of queries you want to send or am i missing something because i thought prepared statements were faster than normal statements
:^: :^: :^:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1