StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJavascript Database Mass Insert
text
Body
copied!<p>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?</p> <pre><code><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> </code></pre> <p><b>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:</b></p> <pre><code>var zipcodes = {"55437":["MINNEAPOLIS","BLOOMINGTON"]}; //truncated alert('Cities in 55437: ' + zipcodes['55437'].join(', ')); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload