Geek Answers Handbook

How to speed up the process when pasting 1000 records in sqlite using HTML5

I am new to HTML5 application development. In this, I want to insert 1000 records into sqlite database using HTML5. This process is very slow. How to use BEGIN / COMMIT before inserting records. So as to speed up the insertion. Please guide me. Thanks in advance. Run this example in a Chrome browser. This is the code for your reference:

<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
//db.transaction(function(tx){tx.executeSql("BEGIN",[]);});
for(var i=0;i<1000;i++)
{
    txquer(i,"test");
}
//db.transaction(function(tx){tx.executeSql("COMMIT",[]);});
db.transaction(function (tx) {
  tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
   var len = results.rows.length, i;
   msg = "<p>Found rows: " + len + "</p>";
   document.querySelector('#status').innerHTML +=  msg;  
 }, null);
});
function txquer(i,test)
{ 
    db.transaction(
    function(tx){
      tx.executeSql('INSERT INTO LOGS (id, log) VALUES (?, ?)',[i,test]);    
    }
    );
} 
</script>
</head>
<body>
<div id="status" name="status">Status Message</div>
</body>
</html>

Regards, Neeraja.

+5
source share
5 answers

This problem is that you are using a separate transaction for each step, instead of reusing it, which is why it does not work well.

db.transaction is a transaction, so BEGIN / COMMIT is not required.

:

<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
    for(var i=0;i<1000;i++)
    {
        txquer(tx, i,"test");
    }
});
db.transaction(function (tx) {
  tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
   var len = results.rows.length, i;
   msg = "<p>Found rows: " + len + "</p>";
   document.querySelector('#status').innerHTML +=  msg;  
 }, null);
});
function txquer(tx,i,test)
{ 
    tx.executeSql('INSERT INTO LOGS (id, log) VALUES (?, ?)',[i,test]);    
} 
</script>
</head>
<body>
<div id="status" name="status">Status Message</div>
</body>
</html>
+7

1000 . , . . this

+1

1000 . - . , !

db.transaction(function(tx){tx.executeSql("BEGIN",[]);});
for(var i=0;i<1000;i++)
{
    txquer(i,"test");
}
db.transaction(function(tx){tx.executeSql("COMMIT",[]);});
0

, , . 'insert select union all' 1000 insert. 500 . , , google chrome, , .

<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
var j=1;
var i=1;
var quer="";
db.transaction(function(tx){tx.executeSql("CREATE TABLE IF NOT EXISTS LOGS (ID INTEGER PRIMARY KEY ASC, todo TEXT)",[]);});
db.transaction(function(tx){tx.executeSql("delete from logs",[]);});
txquer();
showMsg();
function txquer()
{
  quer="insert into logs ";
 for(i=j;i<=j+498;i++)
 {
  quer+=" select "+i+",'test' union all";
  }
  quer+=" select "+i+",'test' ; ";
  j=i+1;
    db.transaction(
    function(tx){
      tx.executeSql(quer,[]);    
    }
    );

}

function showMsg(){
db.transaction(function (tx) {
  tx.executeSql('SELECT count(*) todo FROM LOGS', [], function (tx, results) {
   var len = results.rows.item(0).todo;
   msg = "<p>Found rows: " + len + "</p>";
   document.querySelector('#status').innerHTML +=  msg;  
 }, null);
});
}

0

I wrote a small open source javascript module called html5sql.js that solves this problem. The good news is that html5sql.js is very fast. At the website http://html5sql.com I have a demo that creates a table and inserts 11,000 records into this table, usually in less than a second or two. If someone is still dealing with this problem, I recommend checking out html5sql.js.

0
source

More articles:


All Articles