Solved

Javascript query too slow

Posted on 2014-12-13
11
212 Views
Last Modified: 2014-12-14
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
0
Comment
Question by:detox1978
11 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 40497896
use server code to insert the data in the database, don't javascript
0
 
LVL 2

Author Comment

by:detox1978
ID: 40498119
I'm not sure I understand.

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

Expert Comment

by:Alexandre Simões
ID: 40498315
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
 
LVL 2

Author Comment

by:detox1978
ID: 40498398
Thanks.

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

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 40498402
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Author Closing Comment

by:detox1978
ID: 40498434
Perfect thanks.  loads in seconds....
0
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 40498446
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
 
LVL 2

Author Comment

by:detox1978
ID: 40498476
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
 
LVL 30

Expert Comment

by:Alexandre Simões
ID: 40498484
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
 
LVL 2

Author Comment

by:detox1978
ID: 40498500
I can reduce it to 4000 records by grouping the data, but unfortunately I have to get it all in one hit.
0
 
LVL 2

Author Comment

by:detox1978
ID: 40498984
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Requirements JQuery 1.6+ HTML CSS Introduction This article was inspired by an EE question (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28372511.html) on how to make a page show some balloons animate up a page…
Introduction JSON is an acronym for JavaScript Object Notation.  It is a text-string data transport mechanism, capable of representing simple or complex data structures in a consistent and easy-to-read manner.  Similar in concept to XML, but more e…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now