asked on

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

leakim971

use server code to insert the data in the database, don't javascript

detox1978

ASKER

I'm not sure I understand.

WebSQL and IndexedDB are local (browser) storage. How can I do this using server code?

Alexandre Simões

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

detox1978

ASKER

Thanks.

I've tried doing it, but have been unable. Could you show me what it would/should look like?

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

detox1978

ASKER

Perfect thanks. loads in seconds....

Alexandre Simões

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

detox1978

ASKER

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.

Alexandre Simões

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.

detox1978

ASKER

I can reduce it to 4000 records by grouping the data, but unfortunately I have to get it all in one hit.

detox1978

ASKER

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.

https://www.experts-exchange.com/questions/28580551/Add-JavaScript-Error-Handling.html