Downloading Historical Data

New members questions
Post Reply
Fanienel
Posts: 6
Joined: Fri Jul 04, 2014 12:54 am
Location: Johannesburg, South Africa

Downloading Historical Data

Post by Fanienel »

Hi,
Is it possible to download historical data from the portal (ie the data used to make the graphs)?
Thanks
Fanie
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

Up to 7 days
You can get a json stream. Does it work for you?
Roberto.
Fanienel
Posts: 6
Joined: Fri Jul 04, 2014 12:54 am
Location: Johannesburg, South Africa

Re: Downloading Historical Data

Post by Fanienel »

Hi Roberto, yup I think it would, how do I download the Json data?
Regards
Fanie
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

What is the main bottleneck in jsonp.aspx? Around 20s for a request feels like a long time.
User avatar
lnevo
Posts: 5430
Joined: Fri Jul 20, 2012 9:42 am

Re: Downloading Historical Data

Post 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 :)
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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.
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

Is the source available somewhere?
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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'
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

What db are you using?
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

mysql
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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.
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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.
Roberto.
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post 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
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

How did you structure your db?
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post 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'
Last edited by phrusher on Tue Aug 05, 2014 11:01 am, edited 1 time in total.
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post 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
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

it could even be your script to generate the data to be sent

Sent from my Moto X
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post 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
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

Try it again now??
Did it improve?
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

Hell yeah! What did you do? All json request are now roughly 400ms :)
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

I guess there was a setting on MySQL that was overlooked since the beginning :oops:
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

Good thing you found it. Great work!
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

rimai wrote:I guess there was a setting on MySQL that was overlooked since the beginning :oops:
that's awesome you got it figured out. better late than never. :)
User avatar
lnevo
Posts: 5430
Joined: Fri Jul 20, 2012 9:42 am

Re: Downloading Historical Data

Post by lnevo »

Awesome!
Post Reply