• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

insert statement is very slow in maria db

 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi all i am using Maria DB in my project. in which i have to process 2000000 records.
insertion is very slow (almost 10 times slow) which make project very bad.
i checked this on another linux machine there the insertion is ok.
but the machine which will be used for production in that insertion is very slow, but deletion, selection, update speed is ok.
i am using java in my project to insert the records in tables.
i searched in Google by couldn't find any solution yet.

need help what should i configure in my db or what to write in my code so that insertion is fast.

Thanks in advance
 
Campbell Ritchie
Sheriff
Posts: 51344
86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you mean by slow? How long does it take to insert those records? Please show us the insertion instructions.

Moving discussion to our databases forum.
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Campbell Ritchie wrote:What do you mean by slow? How long does it take to insert those records? Please show us the insertion instructions.

Moving discussion to our databases forum.



Hi Ritchie,

by slow i mean to say that

if in Linux system A(Ram size 4 GB) it takes 15 seconds to inserts 20000 records then in Linux system B(Ram size 16 GB) it takes about 2 minutes 55 seconds. you can see how much it slow is in case of Linux system B.
though B has got more RAM size than A.

insertion instructions : for any insertion instruction it give same result, for example you can see bellow. i think its not issue of insertion instruction.

String insertQuery = "insert into vm_missedcalls (SUBSCRIBER,CALLER,CALLEDTIME,PROCESSED,REASON) values (?,'2222222222',sysdate(),'N','U')";

somewhere i saw to use BEGIN and END statement, but i dont know how to use it in my java code. you can find the link bellow.

https://mariadb.com/kb/en/mariadb/how-to-quickly-insert-data-into-mariadb/
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
abhinas raj wrote:
Campbell Ritchie wrote:What do you mean by slow? How long does it take to insert those records? Please show us the insertion instructions.

Moving discussion to our databases forum.



Hi Ritchie,

Please find bellow the sample code to insert 20000 records.
I have used BEGIN and END statement but its not working properly.
Please see the comment at line pStmt.execute(end);




 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And running that same code on Linux A is faster than on Linux B?

I just want to make sure we are comparing the same things.

In addition, are we talking the exact same database contents and set up?
Is this over a network?
If so, are A and B both on the same bit of the network?

I'm really not sure BEGIN and END work like that on any DB driver. It's also not what your link is saying. Your link is talking about using a block of SQL code to do multiple inserts, it's not talking about single JDBC insert statements.
 
Knute Snortum
Bartender
Pie
Posts: 2884
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My understanding is that each statement between the BEGIN and END must end with a semicolon (;). I don't see where you're doing that.
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Knute Snortum wrote:My understanding is that each statement between the BEGIN and END must end with a semicolon (;). I don't see where you're doing that.


If we put semicolon (;) then also its same.
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:And running that same code on Linux A is faster than on Linux B?

I just want to make sure we are comparing the same things.

In addition, are we talking the exact same database contents and set up?
Is this over a network?
If so, are A and B both on the same bit of the network?

I'm really not sure BEGIN and END work like that on any DB driver. It's also not what your link is saying. Your link is talking about using a block of SQL code to do multiple inserts, it's not talking about single JDBC insert statements.



[I just want to make sure we are comparing the same things] -> yes we are comparing same things.

[In addition, are we talking the exact same database contents and set up?] -> yes every thing is same just system is different

[Is this over a network?] -> I checked by using local database (Maria DB) on both systems A and B. so network involvement is not there

[If so, are A and B both on the same bit of the network?] -> it does not matter because both systems have its own local database.

[Your link is talking about using a block of SQL code to do multiple inserts, it's not talking about single JDBC insert statements] -> yes in my code also multiple inserts are there, you can see insert is inside nested for loops.
this insert statement will be executed 2000000 times to insert 2000000 records in DB.
and i am executing "BEGIN" before execution of each 1000 insert statements, as the link says.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I did so many times hit and trial and finally made the code working for me on the system on which insert was very slow(almost 10 times).
following is the working code.




Above code inserts 2000000 records in DB and takes about 15 minutes.
this code does not use "END" still it works, (i am not sure how)
in the loop i executed "BEGIN" two times to make it work (i dont know the reason how it started working)

however the code is working, but i am bit scared to use it in my real project. because i did not understand the reason why it started working, will it fail in future or will be running always in production mode.

I want to know that is this a good solution or it will fail latter on. I need to know how "BEGIN" and "END" is used in java code to make the insert statements faster.
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
abhinas raj wrote:
Dave Tolls wrote:And running that same code on Linux A is faster than on Linux B?

I just want to make sure we are comparing the same things.

In addition, are we talking the exact same database contents and set up?
Is this over a network?
If so, are A and B both on the same bit of the network?

I'm really not sure BEGIN and END work like that on any DB driver. It's also not what your link is saying. Your link is talking about using a block of SQL code to do multiple inserts, it's not talking about single JDBC insert statements.



[I just want to make sure we are comparing the same things] -> yes we are comparing same things.

[In addition, are we talking the exact same database contents and set up?] -> yes every thing is same just system is different

[Is this over a network?] -> I checked by using local database (Maria DB) on both systems A and B. so network involvement is not there

[If so, are A and B both on the same bit of the network?] -> it does not matter because both systems have its own local database.

[Your link is talking about using a block of SQL code to do multiple inserts, it's not talking about single JDBC insert statements] -> yes in my code also multiple inserts are there, you can see insert is inside nested for loops.
this insert statement will be executed 2000000 times to insert 2000000 records in DB.
and i am executing "BEGIN" before execution of each 1000 insert statements, as the link says.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I did so many times hit and trial and finally made the code working for me on the system on which insert was very slow(almost 10 times).
following is the working code.




Above code inserts 2000000 records in DB and takes about 15 minutes.
this code does not use "END" still it works, (i am not sure how)
in the loop i executed "BEGIN" two times to make it work (i dont know the reason how it started working)

however the code is working, but i am bit scared to use it in my real project. because i did not understand the reason why it started working, will it fail in future or will be running always in production mode.

I want to know that is this a good solution or it will fail latter on. I need to know how "BEGIN" and "END" is used in java code to make the insert statements faster

-> Is there something that i can configure in my.cnf file to make the insertion fast

.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, so the same setup on different boxes.
There has to be something different I'm afraid, and it's likely not something we can debug here.

As to the SQL statements themselves I think you misunderstood me.
I'm saying the the MariaDB page is talking about a block of SQL, not a load of individual INSERT statements:

You cannot do that with individual JDBC calls.

Forget about that. Have you tried using batches and executeBatch?

The number of INSERTS you should call will depend on the db.
You might be able to do all of them in one go.
 
abhinas raj
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:OK, so the same setup on different boxes.
There has to be something different I'm afraid, and it's likely not something we can debug here.

As to the SQL statements themselves I think you misunderstood me.
I'm saying the the MariaDB page is talking about a block of SQL, not a load of individual INSERT statements:

You cannot do that with individual JDBC calls.

Forget about that. Have you tried using batches and executeBatch?

The number of INSERTS you should call will depend on the db.
You might be able to do all of them in one go.


I tried with batch also, it gave same result. something is wrong with DB configuration, that i am looking for.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic