APIs importing to MySql logic

[quasiman]

Hi all,

 

I'm working on a project involving an orders set of APIs, and having trouble coming up with the best logic to handle this - with the smallest amount of overhead and client facing slowness.  Here's the jist of what I'm looking at... 

There are 21 API methods to pull different types of orders, and each of them handle paging by requesting a number of records (10 - 500), and a page number.  So I could say in my URI, "page-no=1&no-of-records=50", followed by "page-no=2&no-of-records=50", to get subsequent records.  The API will return in each call the total records returned as well as the total records in their database.

Now, I want to insert all of this into my database, and so that means something like 21 APIs * 3 SQL requests each = 63 sql queries (check it exists, if so update - else insert).

 

This seems to me like way too much work for a client facing application.  Any suggestions?

 

 

[kicken]

You might be able to use a simple INSERT INTO ... ON DUPLICATE KEY UPDATE query to handle the process rather than having to have three separate queries. You can use that with the multi insert syntax as well so you could just do one insert per page or less. Try and request as many items per page as they will allow to minimize the number of API requests you need to make.

 

To make it more client friendly you can combine the process with some JS and Ajax requests so you can provide status updates as the items update. If this is something you just need to do periodically rather than in response to some user action you'd probably be better off making it a CLI script and running it via a cron task (or manually).

[quasiman]

This is a Joomla component, and so natively I can't use ON DUPLICATE, but I've thought about putting it into just standard SQL... I just wouldn't have the portability.  I think I'm going to look into running this through a cron plugin, instead of at the user page views.

 

Thanks!