PHP-SQL script takes >9hrs to complete db
Alkyoneus.3917:
This is the 2nd time I’ve tinkered with php-sql and I made a small script to retrieve the items and their sell/buy info into my db so I can sort/rank the items.
It works (yay!) but I feel its hugely inefficient because it takes like 9hours to completely copy TP info to my db is this normal? I don’t see where I can cut corners.
oh i run it in the cli queued as a cronjob
smiley.1438:
Yes, 9h to complete seems highly inefficient – it should actually not take longer than a couple minutes to complete when using the bulk feature. Yor script initiates a new (single) cURL instance for each request and doesn’t queue these up to process them asynchronous which means it has to wait for the whole lifecycle of one request until it fires another one. You might want to take a look at rolling (multi) curl and maybe also at my database project on GitHub which uses it. (takes about half an hour to download the whole items database in 4 languages on my very slow connection)
https://github.com/codemasher/gw2-database/blob/master/classes/rollingcurl.class.php
https://github.com/codemasher/gw2-database/blob/master/classes/gw2items.class.php#L156
€: also you need to connect through https since the http URL redirects you to the https one each time (which probably costs some time) – to make cURL work with https, you need to provide a CA root certificate: http://curl.haxx.se/ca/cacert.pem by passing the parameters:
CURLOPT_SSL_VERIFYPEER = true
CURLOPT_SSL_VERIFYHOST = 2
CURLOPT_CAINFO = ‘path/to/certificates.pem’
Alkyoneus.3917:
Wow this is stuff I would not have guessed in a million years; thank you Smiley! I’ll tinker a bit and post an update later this week, if i can get it down to 30 min id be so happy
smiley.1438:
I’m sure you’ll get below 30 min, RollingCurl is da bomb!
Alkyoneus.3917:
Woo got it down to 25min http://pastebin.com/BCvNeyzr; The rollingcurl.php I used is still a bit of a black box for me but it seems to work fine without the root certificate you mentioned.
smiley.1438:
You’re still requesting the data for each individual item, try using the “ids” parameter and receive the data in chunks of up to 200 items per request. Over here: https://github.com/codemasher/gw2-database/blob/master/classes/gw2items.class.php#L178
Pat Cavit.9234:
Or just using pagination + the Link headers we return (see https://tools.ietf.org/html/rfc5988 for a description)
Alkyoneus.3917:
@Smiley, ohh thanks, I see now in your code (not fluent in reading code); So now I added that (http://pastebin.com/udZ9HMHP) it went down to 21 minutes, woo!! I expected like 30 minutes divided by 4 languages so around 7-8minutes like you mentioned, but i realised I also do some array calculations directly after so I figure that accounts for the extra 13-14 minutes.
@Pat oh i see like this
https://api.guildwars2.com/v2/commerce/listings?page=0&page_size=2
</v2/commerce/listings?page=1&page_size=2>; rel=next,
</v2/commerce/listings?page=0&page_size=2>; rel=self,
</v2/commerce/listings?page=0&page_size=2>; rel=first,
</v2/commerce/listings?page=11076&page_size=2>; rel=last
anzenketh.3759:
I am still a beginner in development. In a attempt to develop a application that is currently on hold as I am trying to figure out the best way to persist the details of a item into the database. I have decided to use the following wrapper. It is not quite on wiki yet(no write access):
https://github.com/EtienneLamoureux/durmand-scriptorium
It uses Guzzle to get the requests and I have got a full item database query down less then 5 Minutes.
StevenL.3761:
Getting the item details db schema right is pretty difficult. But even if you nailed the design, filling your perfectly designed database with API data has its own challenges.
Some items contain references to other entities that may not already be in your database. Examples include upgradable items that reference upgrade item details, recipe sheets that reference recipe details, and dyes that reference color details.
Trying to insert an item like that results in an integrity constraint violation, because the referenced item doesn’t exist in the database. So what do you do? You insert the referenced entity first, and then insert the item that holds the reference.
The opposite can also happen. Multiple combat items can reference the same upgrade item. Trying to insert the referenced upgrade item results in a duplicate key violation, because the referenced item already exists in the database. So what do you do? Some database engines provide an option to silently ignore duplicate key errors. For the ones that don’t provide that option, change the query to insert only if the key doesn’t exist already.
Long story short… the code can (will) get pretty messy, and it’s not your fault.
AysonCurrax.3254:
Getting the item details db schema right is pretty difficult. But even if you nailed the design, filling your perfectly designed database with API data has its own challenges.
Some items contain references to other entities that may not already be in your database. Examples include upgradable items that reference upgrade item details, recipe sheets that reference recipe details, and dyes that reference color details.
Trying to insert an item like that results in an integrity constraint violation, because the referenced item doesn’t exist in the database. So what do you do? You insert the referenced entity first, and then insert the item that holds the reference.
The opposite can also happen. Multiple combat items can reference the same upgrade item. Trying to insert the referenced upgrade item results in a duplicate key violation, because the referenced item already exists in the database. So what do you do? Some database engines provide an option to silently ignore duplicate key errors. For the ones that don’t provide that option, change the query to insert only if the key doesn’t exist already.
Long story short… the code can (will) get pretty messy, and it’s not your fault.
oh god i saved myself that trouble by just not bothering to add constraints for upgrade_items and such. Id much rather toss a second SQL query to grab that bunch of info at my DB that making my data import systematics even more complicated than they already are.
I mean, mapping the v1/item_details to a database scheme was already bad enough by itself.
http://gw2compendium.com/resources/items.png
-cough-
this is not the final result as I noticed some mistakes with it, per se, all name/description/suffix tables have a composite primary key with a lang field instead of having a column for each lang version of the string in one row.
Also, that completely non working relationship between item_infusion_slot and item_armor, item_weapon and such has been replaced with a jointable between each of those 4. probably still not the most beautiful solition, but it does its job.
