Javascript query too slow

Hi All,

I have the following code that gets the JSON data from http://data.philhowell.co.uk/index.install.json.php?pin=1111&id=0 and insterts it into a local WebSQL database.

This works however the live version has 20,000 records and it takes nearly an hour to insert them all.

Could someone help me write it more efficiently?

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title></title>
		<script type="text/javascript" src="js/jquery-2.1.1.min.js"></script>
		<script type="text/javascript">
		// <create database>
			var db = openDatabase('my_db', '1.0', 'MY CRM', 2 * 1024 * 1024);
			var msg;
			db.transaction(function (tx) {
			  tx.executeSql('DROP TABLE IF EXISTS CRM');
			  tx.executeSql('CREATE TABLE IF NOT EXISTS CRM (id unique, customer, contact)');
			});
		// </create database>

		// <BUILD INSERT STATEMENT>
			$(document).ready(function(){
				//data url
				var url="http://data.philhowell.co.uk/index.install.json.php?pin=1111&id=0";
				
				$("#status").html("Loading data... ");

				$.getJSON(url, function(data){
					$.each(data.users, function(i,user){
						var sql_id			= user.id;
						var sql_company		= user.company.replace("'","\\'");
						var sql_contact		= user.contact.replace("'","\\'");

						db.transaction(function (tx) {
									tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
						});
					});					
				});

				$("#status").html("Loading complete... ");

			});
		// </BUILD INSERT STATEMENT>
		</script>

	</head>
	<body>
	<div id="status">Preparing database...</div>

	<input type="button" value="Back" onclick="window.history.back()" /> 
    </body>
</html>

Open in new window



My guess is the issue is this loop

db.transaction(function (tx) {
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
});

Open in new window


which would work better if it executed all the records in one go, like this

db.transaction(function (tx) {
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
	tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
});

Open in new window



but i'm struggling to get t to work.  So any help would be greatly appreciated.


If it helps.  I'm not fussed about any of the code.  I just want a way to sync the JSON data offline on a browser (WebSQL, IndexedDB etc...)


Many thanks
LVL 2
detox1978Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Robert SchuttConnect With a Mentor Software EngineerCommented:
Alexandre may be on to something, but I'm not sure if it's feasible for you to change this right now. For an answer to your original question, try moving the entire $.each loop into the transaction function.

so instead of:
				$.getJSON(url, function(data){
					$.each(data.users, function(i,user){
						var sql_id			= user.id;
						var sql_company		= user.company.replace("'","\\'");
						var sql_contact		= user.contact.replace("'","\\'");

						db.transaction(function (tx) {
									tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES ('+sql_id+', "'+sql_company+'","'+sql_contact+'")');
						});
					});					
				});

Open in new window

use:
				$.getJSON(url, function(data){
					db.transaction(function (tx) {
						$.each(data.users, function(i,user){
							var sql_id			= user.id;
							var sql_company		= user.company;
							var sql_contact		= user.contact;

							tx.executeSql('INSERT INTO CRM (id, customer, contact) VALUES (?, ?, ?)', [sql_id, sql_company, sql_contact]);
						});
					});					
				});

Open in new window

This gave me a 400 time improvement for about 20000 records (it ran in about 7 seconds). Note I have also changed your code to use parameters instead of sql string concatenation.
0
 
leakim971PluritechnicianCommented:
use server code to insert the data in the database, don't javascript
0
 
detox1978Author Commented:
I'm not sure I understand.

WebSQL and IndexedDB are local (browser) storage.  How can I do this using server code?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Alexandre SimõesManager / Technology SpecialistCommented:
I would try using the api instead of websql.

Something like the following taken from https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_API/Using_IndexedDB
const dbName = "the_name";

var request = indexedDB.open(dbName, 2);

request.onerror = function(event) {
  // Handle errors.
};
request.onupgradeneeded = function(event) {
  var db = event.target.result;

  // Create an objectStore to hold information about our customers. We're
  // going to use "ssn" as our key path because it's guaranteed to be
  // unique - or at least that's what I was told during the kickoff meeting.
  var objectStore = db.createObjectStore("customers", { keyPath: "ssn" });

  // Create an index to search customers by name. We may have duplicates
  // so we can't use a unique index.
  objectStore.createIndex("name", "name", { unique: false });

  // Create an index to search customers by email. We want to ensure that
  // no two customers have the same email, so use a unique index.
  objectStore.createIndex("email", "email", { unique: true });

  // Use transaction oncomplete to make sure the objectStore creation is 
  // finished before adding data into it.
  objectStore.transaction.oncomplete = function(event) {
    // Store values in the newly created objectStore.
    var customerObjectStore = db.transaction("customers", "readwrite").objectStore("customers");
    for (var i in customerData) {
      customerObjectStore.add(customerData[i]);
    }
  }
};

Open in new window

0
 
detox1978Author Commented:
Thanks.

I've tried doing it, but have been unable.  Could you show me what it would/should look like?
0
 
detox1978Author Commented:
Perfect thanks.  loads in seconds....
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Hi mate, I was just curious about the performance, did a small test and it's actually super fast.
I leave you the code sample I used and the link to the working code.
The thing to keep in mind is to create only one transaction for the bulk operation.
if (!window.indexedDB) {
    window.alert("Your browser doesn't support a stable version of IndexedDB. Such and such feature will not be available.");
}

// open the database connection
var request = indexedDB.open("MyTestDatabase", 5);

request.onerror = function(event) {
  console.log(arguments);
};
request.onsuccess = function(event) {
  console.log('open connection: success');
  var db = event.target.result;
    
  var objStore = db.transaction(["customers"], "readwrite").objectStore("customers");
  insertData(objStore);
};
request.onupgradeneeded = function(event) {
  console.log('db upgrade: success');
  var db = event.target.result;
  var objectStore = db.createObjectStore("customers", { keyPath: "ssn" });
}

var insertData = function(objStore){
  
  // let's clear the objectStore first
  var clearRequest = objStore.clear();
  
  clearRequest.onsuccess = function(){
    // insert 1000 records
    for(var i=0;i<1000;i++){
      objStore.add({ssn: i, name: 'user'+i, address: 'the address of user'+i});
    }
  };
};

Open in new window

0
 
detox1978Author Commented:
The performance of WebSQL isnt bad on my browser, but this is for a Blackberry.  INSERT is almost instant, however SELECT takes nearly 2 minutes to return 20,000 records.

So I will have to review my options.
0
 
Alexandre SimõesManager / Technology SpecialistCommented:
Do you really have to get the 20.000 records in one shot? or at all?
Can't you create some indexes and query the ObjectStore in order to retrieve smaller resultsets?

Paging or Infinite Scrolling can also be an option in case you need to show a list of records.
0
 
detox1978Author Commented:
I can reduce it to 4000 records by grouping the data, but unfortunately I have to get it all in one hit.
0
 
detox1978Author Commented:
Need some more help adding error/success handlers to the script I put together.  If anyone gets the chance to look at it, it'd be greatly appreciated.

http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28580551.html
0
All Courses

From novice to tech pro — start learning today.