Page 1 of 1
Downloading Historical Data
Posted: Mon Jul 07, 2014 7:53 am
by Fanienel
Hi,
Is it possible to download historical data from the portal (ie the data used to make the graphs)?
Thanks
Fanie
Re: Downloading Historical Data
Posted: Mon Jul 07, 2014 7:55 am
by rimai
Up to 7 days
You can get a json stream. Does it work for you?
Re: Downloading Historical Data
Posted: Wed Jul 16, 2014 12:19 am
by Fanienel
Hi Roberto, yup I think it would, how do I download the Json data?
Regards
Fanie
Re: Downloading Historical Data
Posted: Wed Jul 16, 2014 8:10 am
by rimai
Code: Select all
http://forum.reefangel.com/status/jsonp.aspx?id=reefangel&filter=t1
Change the id to your forum username and the filter to what you want to download
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 5:30 am
by phrusher
What is the main bottleneck in jsonp.aspx? Around 20s for a request feels like a long time.
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 5:31 am
by lnevo
+1 it does take a long time to get the values for any of the charts too... Would love to see if this can be improved
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 8:14 am
by rimai
I think it is the server or the db structure
It was way worse in the past. I've made a few changes from recommendations of other members and it got better, but I also agree that it could be improved.
We just need someone that knows DB structure and optimization to really dig and take a look at it.
It could be the aspx page that is causing this too, in which case, we would need someone with vb.net knowledge to optimize it for us.
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 9:27 am
by phrusher
Is the source available somewhere?
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 9:38 am
by rimai
I think it is much more of db optimization than anything else.
Here is the test I have done.
This query takes 9 seconds:
Code: Select all
select logdate, tags.name, data from reefangelportal_dbo.datalog inner join reefangelportal_dbo.datatagslog on datalog.id=datatagslog.datalogid inner join reefangelportal_dbo.tags on datatagslog.tagsid=tags.id where myreefangelid='reefangel' and logdate>='2014-07-21' and tags.name = 'T1'
And this takes 0.14 seconds:
Code: Select all
select logdate, tags.name, data from reefangelportal_dbo.datalog inner join reefangelportal_dbo.datatagslog on datalog.id=datatagslog.datalogid inner join reefangelportal_dbo.tags on datatagslog.tagsid=tags.id where myreefangelid='reefangel' and logdate>='2014-07-21'
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:08 am
by phrusher
What db are you using?
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:08 am
by rimai
mysql
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:18 am
by rimai
Well, I guess the .NET MySQL driver is not so good either.
I tested it by using the faster sql query and it boggled down on the .NET page.
I guess I'll try php when I get some time.
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:24 am
by rimai
Duh... Never mind. I had limiting of fetch when I was using MySQL workbench.
If I fetch all the records, it took almost 25s for the first query and it yielded 2607 rows. It took about the same time for the second query and it yielded 106458 rows.
So, back to the MySQL server optimization.
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:25 am
by binder
rimai wrote:Well, I guess the .NET MySQL driver is not so good either.
I tested it by using the faster sql query and it boggled down on the .NET page.
I guess I'll try php when I get some time.
my portal is using php. so i have the xml sending working. i can easily create a json version of it to test out. i should be able to have this tomorrow if you guys want me to.
Sent from my iPad mini
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:31 am
by rimai
How did you structure your db?
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:35 am
by phrusher
I'm not very familiar with optimizations of mysql but have you tried adding an index?
Code: Select all
ALTER TABLE 'tags' ADD INDEX 'name' ('name')
To remove index:
Code: Select all
ALTER TABLE 'tags' DROP INDEX 'name'
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 10:50 am
by binder
rimai wrote:How did you structure your db?
i think very similar to yours but im using sqlite3 and not a larger scale db. mine is just a flat file. irs pretty simplistic in terms of structure.
Sent from my iPad mini
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 11:37 am
by binder
it could even be your script to generate the data to be sent
Sent from my Moto X
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 11:46 am
by rimai
phrusher wrote:I'm not very familiar with optimizations of mysql but have you tried adding an index?
Code: Select all
ALTER TABLE 'tags' ADD INDEX 'name' ('name')
To remove index:
Code: Select all
ALTER TABLE 'tags' DROP INDEX 'name'
Yes, I already have an index for column name on table tags
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 12:41 pm
by rimai
Try it again now??
Did it improve?
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 12:47 pm
by phrusher
Hell yeah! What did you do? All json request are now roughly 400ms
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 12:49 pm
by rimai
I guess there was a setting on MySQL that was overlooked since the beginning
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 1:20 pm
by phrusher
Good thing you found it. Great work!
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 1:20 pm
by binder
rimai wrote:I guess there was a setting on MySQL that was overlooked since the beginning
that's awesome you got it figured out. better late than never.
Re: Downloading Historical Data
Posted: Tue Aug 05, 2014 3:39 pm
by lnevo
Awesome!