Solved

Javascript query too slow

Posted on 2014-12-13
11
223 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
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…

821 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