Javascript数据库批量插入 [英] Javascript Database Mass Insert

问题描述

我试图在JavaScript数据库中插入70,000多行（使用Chrome 5.0.317.2）。插入物需要很长时间。实际的页面会在几秒钟内加载，而且我可以看到进度，因为插入每行的百分比增长非常缓慢。花了大约一个小时完成插入所有记录。有没有一种方法来优化插入，或者以某种方式从预加载的SQLite数据库开始？

 < script src =jquery .1.3.2.min.jstype =text / javascriptcharset =utf-8>< / script> 
< script type =text / javascriptcharset =utf-8> 
 //例如截断为1行。真的有76547行。 
 var zipcodes = var zipcodes = [{city_name：AMHERST，city_alias：AMHERST，zipcode：01002}]; 
 var db; 
函数openMyDatabase（）{
 var shortName ='mydb'; 
 var version ='1.0'; 
 var displayName ='mydb'; 
 var maxSize = 65536; 
 db = openDatabase（shortName，version，displayName，maxSize）; 
 db.transaction（
 function（transaction）{
 transaction.executeSql（
'CREATE TABLE IF NOT EXISTS zipcode'+ 
'（id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT，'+ 
'city_name TEXT NOT NULL，'+ 
'city_alias TEXT NOT NULL，'+ 
'zipcode TEXT NOT NULL）'
）; 
} 
）; 
 $ .each（zipcode，function（i，zipcode）{
 insertZipcode（zipcode.city_name，zipcode.city_alias，zipcode.zipcode，i）; 
}）; 
} 
 
函数errorHandler（transaction，error）{
 alert（'Oops。Error was'+ error.message +'（Code'+ error.code +'）'）; 
返回true; 
 
 
函数insertZipcode（cityName，cityAlias，zipcode，i）{
 db.transaction（
 function（transaction）{
 transaction.executeSql 
'INSERT INTO zipcode（city_name，city_alias，zipcode）VALUES（？，？，？）;'，
 [cityName，cityAlias，zipcode]，
 function（）{
 $（'＃counter'）。html（（100 * i / zipcodes.length）+'％'）; 
}，
 errorHandler 
）; 
} 
）; 
返回false; 
 
 
 $（function（）{
 openMyDatabase（）; 
}）; 
< / script> 
  

解决方案：在PHP方面，我创建了一个关联数组，并将邮政编码用作关键字和城市数组作为值，并通过json_encode运行并将其传递给javascript。在JavaScript方面，我能够通过使用以下代码快速获取特定邮政编码的城市列表：

  var zipcodes = {55437：[MINNEAPOLIS，BLOOMINGTON]}; //截断
 alert（'55437城市：'+ zipcodes ['55437']。join（'，'））; 
  

解决方案

为什么不使用预加载的XML而不是创建所有网页加载时的字段？这样你就可以减少加载时间，并且可以通过某种类型的索引来减少搜索时间，可能是哈希表索引或二进制搜索。

这会降低灵活性，这意味着您必须更改XML并在工具的帮助下编译它 - 我不知道是否有类似的东西存在;但可以提供更好的性能，特别是如果您使用的是iPhone等有限设备。

I am trying to insert over 70,000 rows into a javascript database (using Chrome 5.0.317.2). The inserts are taking a very long time. The actual page loads in a few seconds, and I can see progress as the percent increases very slowly as each row is inserted. It took about an hour to finish inserting all the records. Is there a way to optimize the inserts, or somehow start out with a preloaded SQLite database?

<script src="jquery.1.3.2.min.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript" charset="utf-8">
// Truncated to 1 row for example. There are really 76547 rows.
var zipcodes = var zipcodes = [{"city_name":"AMHERST","city_alias":"AMHERST","zipcode":"01002"}];
var db;
function openMyDatabase() {
    var shortName = 'mydb';
    var version = '1.0';
    var displayName = 'mydb';
    var maxSize = 65536;
    db = openDatabase(shortName, version, displayName, maxSize);
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'CREATE TABLE IF NOT EXISTS zipcode ' +
                '  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' +
                '   city_name TEXT NOT NULL, ' +
                '   city_alias TEXT NOT NULL, ' +
                '   zipcode TEXT NOT NULL)'
            );
        }
    );
    $.each(zipcodes, function(i, zipcode) {
        insertZipcode(zipcode.city_name, zipcode.city_alias, zipcode.zipcode, i);
    });
}

function errorHandler(transaction, error) {
    alert('Oops. Error was '+error.message+' (Code '+error.code+')');
    return true;
}

function insertZipcode(cityName, cityAlias, zipcode, i) {
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO zipcode (city_name, city_alias, zipcode) VALUES (?, ?, ?);',
                [cityName, cityAlias, zipcode],
                function(){
                    $('#counter').html((100 * i / zipcodes.length) + '%');
                },
                errorHandler
            );
        }
    );
    return false;
}

$(function() {
    openMyDatabase();
});
</script>

Solution: On the PHP side, I made an associative array and used the zip code as the key and an array of cities as the value, and I ran it through json_encode and passed that to the javascript. On the javascript side I was able to very quickly get a list of cities for a particular zip code by using the following code:

var zipcodes = {"55437":["MINNEAPOLIS","BLOOMINGTON"]}; //truncated
alert('Cities in 55437: ' + zipcodes['55437'].join(', '));

解决方案

Why not use a preloaded XML instead of creating all the fields when the webpage loads? That way you will reduce the loading time, and the searching time could be reduced by some type of indexing, maybe hashtable indexing or binary search.

This would reduce flexibility, in means that you will have to change the XML and compile it with the help of a tool - Which I don't know if something like that exists; but will allow for better performance, specially if you are working in a limited device like an IPhone.

这篇关于Javascript数据库批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！