Downloading Historical Data

[Fanienel]

Hi,
Is it possible to download historical data from the portal (ie the data used to make the graphs)?
Thanks
Fanie

[rimai]

Up to 7 days
You can get a json stream. Does it work for you?

[Fanienel]

Hi Roberto, yup I think it would, how do I download the Json data?
Regards
Fanie

[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

[phrusher]

What is the main bottleneck in jsonp.aspx? Around 20s for a request feels like a long time.

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

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.

[phrusher]

Is the source available somewhere?

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

[phrusher]

What db are you using?

[rimai]

mysql

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

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

[binder]

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]

How did you structure your db?

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

[binder]

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]

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

Sent from my Moto X

[rimai]

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

[rimai]

Try it again now??
Did it improve?

[phrusher]

Hell yeah! What did you do? All json request are now roughly 400ms

[rimai]

I guess there was a setting on MySQL that was overlooked since the beginning

[phrusher]

Good thing you found it. Great work!

[binder]

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.

[lnevo]

Awesome!