database driven dynamic web charts - how to?

Does anyone have experience with getting data out of a database and plotting it up online?
Preferably I'd want some sort of AJAX enabled chart that will not reload the whole page if the user makes a single change to dates or scale or whatever. The ability to zoom in and out as well as save the chart to an image would be helpful as well.

Any suggestions?

Make a friendship with Google and all your answers can be found.

There's several good ones out there; it all depends on what technology you want to use (jQuery, Flash, Silverlight, etc.). The Flot jQuery plugin is quite good and is used by Flightaware and several other major websites. Fusioncharts and Visifire are also great alternatives (Flash and Silverlight respectively). Long story short, there's lots of options out there. The only feature that may be more difficult to find is the saving of the chart as an image. That's most likely going to require some server side processing to generate the image and present it for download, so not a standalone jQuery solution; should be easily doable though.

Well, Google is often not all that useful when you are looking for something non-specific, it's the whole Los Links thing. I am more looking for people who have hands on experience with one of the many products out there and who have some insight into how easy or painful it was to work with that product.

For example, Google came up with TeeChart which seems like a decent product, won awards and whatnot, but the documentation is terribad and creates a real hurdle for folks who aren't expert programmers.

I looked at Open Flash Charts but that project seemed to have fizzled in 2009. I honestly don't really care about the technology as long as it can be deployed fairly quickly. I know enough Ruby, Python, C#, whatever to hack my way through anything that doesn't require me to have 5 years of experience or spend 2 weeks monkeying with things.

The only requirement I do have is that PHP isn't an option, all else is fair game.

I've been working on site that's going to make a lot of use of charts. I've been keeping up to date on lots of charting libraries and tested a bunch with my data to get an idea of which I want to use. I listed a few that I'm familiar with.

Flot - pretty standard and easy to use, probably the best tool for most uses.

Flotr - has some relation to Flot, adds some functionality, also really easy to use.

Dygraphs - still a bit young but useful for really detailed numbers and built in smoothing.

jqPlot - lots of little settings, a hassle to set up in my experience.

Emprise Charts - decent but I hate their styling and interface.

Google Charts API - static charts, really easy to use but few choices and customization. Some limitations on data length (can submit via POST to overcome that). Useful for throwing something up quickly.

amCharts - my vote for best flash charts, pretty solid and easy to use, lots of options and customization. I used to use this exclusively. Downside: flash.

AnyChart - haven't used it.

jsChart - wasn't really mature when I used it a year ago, might be better now.

HighCharts - my vote for best overall. Easy to use, tons of options, great documentation, active development with good updates. I now use this exclusively. Downside: cost.

Excellent, thanks a lot for the replies. HighCharts does indeed look pretty easy and is probably what I will monkey with tonight to see what's what. All I need to do now is to load data from SQL 2008 into an array to HighCharts to work with and then figure out whether it can cope with say 100k records.

Thuleman said:

Excellent, thanks a lot for the replies. HighCharts does indeed look pretty easy and is probably what I will monkey with tonight to see what's what. All I need to do now is to load data from SQL 2008 into an array to HighCharts to work with and then figure out whether it can cope with say 100k records.

Click to expand...

Is this internal only, or external? If you're dealing with SQL and have a lot of data, SSRS or a similar tool may be far better for you. It wouldn't be free, but if your company already has SSRS deployed you may be able to tap into it. You also gain the ability of permission trimming data by user at the SQL level if you're in an AD environment and have Kerberos configured.

webdes03 said:

It wouldn't be free, but if your company already has SSRS deployed you may be able to tap into it.

Click to expand...

There are free, client-side equivalents to SSRS; RDL's are reports on the database-side, and RDLC are the equivalent on the "client" (ie: web server). On a past ASP.Net project, I fetched data from Oracle, hydrated a POCO object, and passed it into a RDLC. From the user's perspective, it's the same as a server-side SSRS.

We don't currently have SSRS deployed though it would just be a matter of installing it. The server isn't on AD. What I really need to do is to be able to fairly quickly create a chart from up to 100k records (though most of the time there are fewer records). A consultant created a tool to use for charting but the performance is abysmal (takes about 13 seconds to chart 100k records). I already looked at the server side performance of the SQL server and that's not it. We contacted the consultant and they said that this is just a function of .Net slowing the actual plotting of the graphs down. The consultants used their own code and not some commercial charting app.

So now what I want to do is see whether I can get it to plot faster, and also actually include the ability to zoom, select different series more easily, print/save the chart, etc.

I will look into whether SSRS can create charts that can be shown online and whether there's some interface that allows user interaction with the data already done for SSRS.

As easy and user friendly as HighCharts is it totally failed once you throw some serious data its way.
I can't even plot 8,760 data points with it, it just won't do it (and 9k points isn't really "serious" from where I am looking).

Perhaps I am a bit naive about this but I would think it's one of the most common web chart uses to simply take a whole bunch of points and plot them up. That really shouldn't be that tricky.

My series have 8,760 data points or 35,040 data points per year. I would like to plot two series per year at a time and later perhaps add the option to plot multiple years. This needs to happen without the user having to wait a couple minutes for the plots to complete so it needs some sort of "fastplot" option where the chart app interpolates across to plot faster rather than plot every single data point.

Is this really something that's just an unreachable pie in the sky type of goal? From where I am looking this doesn't seem too unreasonable of a performance request.

Thuleman said:

Is this really something that's just an unreachable pie in the sky type of goal? From where I am looking this doesn't seem too unreasonable of a performance request.

Click to expand...

Not unreachable, but you'll need to do some further digging on where any bottlenecks could be. What does the query look like? Is it joining across multiple databases (or even database servers)? How long is the query's execution time in Management Studio? How quickly does a web app receive the request? Are there iterative calls to the DB server, such as a "summary" query kicking off a "detail" query from the webserver for every row found in the "summary" query? What about the reporting vendor, what have they had to say?

All of this, and more, can impact the time from click to report completion. I have a hard time believing your consultant's answer that a charting control would choke on 9000 rows, which assumes that everything else was implemented properly and tested thoroughly with realistic data volumes.

PTNL said:

All of this, and more, can impact the time from click to report completion. I have a hard time believing your consultant's answer that a charting control would choke on 9000 rows, which assumes that everything else was implemented properly and tested thoroughly with realistic data volumes.

Click to expand...

It plots 9k rows quickly, 90k not so much. Queries for 90k records executed in management studio are nearly instantaneous Queries for 90k records in the .Net app are taking approximately 10 seconds to complete. The response I got from them about this was "most of the problems we have had are more due to the generation of the queries in .NET", and that's that.

Doesn't really matter one way or another though, their "custom" charting sucks. It's not interactive as in you can't zoom, you can't hover to display a particular data point, you can't export the chart to an image format, you can't print the chart unless you just print the web page (or selection of the web page), the series and time range selection is clunky, and it does the whole Web-Forms-ASP-1.0 the whole page reloads when a change to the chart is requested.

So I took it upon myself to redo the whole thing on my own time, with little prior technical knowledge, and found myself stuck right out of the gate because I have yet to find something that can actually plot 90k records within a web page nevermind all the features I'd like to implement.

(It seems you've already moved on with evaluating other charting alternatives, but I'll ask anyways in case you're willing to share... )

I'm curious as to what SQL Profiler would show during the report generation: either one DB hit or multiple, and any possible performance metrics on the query. What were the webserver perfmon metrics? If you contacted the vendor regarding this issue, what did the vendor reply with?

What would 90,000 points look like plotted? What kind of chart are you making? Is the intent just to draw something with all that detail and let the user zoom in?

PTNL said:

I'm curious as to what SQL Profiler would show during the report generation: either one DB hit or multiple, and any possible performance metrics on the query. What were the webserver perfmon metrics?

Click to expand...

Not sure how to set the Profiler up to show anything useful, never worked with it before. I don't think this is a webserver problem, I quite often run into this error: "Error While Plotting Data: Error Occured in DatabaseFunctions.OpenTable Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

mikeblas said:

What would 90,000 points look like plotted? What kind of chart are you making? Is the intent just to draw something with all that detail and let the user zoom in?

Click to expand...

This is a line chart. I don't think the detail is needed unless the user decides to zoom in. Ideally there would be some option to "plot fast" by using some sort of algorithm that eliminates the majority of the points just to plot the graph quickly and then re-queries the DB if the user zooms in or requests data for a single point.

Thuleman said:

Not sure how to set the Profiler up to show anything useful, never worked with it before. I don't think this is a webserver problem, I quite often run into this error: "Error While Plotting Data: Error Occured in DatabaseFunctions.OpenTable Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Click to expand...

What is it about SQL Profiler that is confusing or not understandable? In fact, that error message tells me that SQL Profiler would be very enlightening about what's really going on with the library and the number of calls originating from the web server and/or recursively from the DB server.

Thuleman said:

Ideally there would be some option to "plot fast" by using some sort of algorithm that eliminates the majority of the points just to plot the graph quickly and then re-queries the DB if the user zooms in or requests data for a single point.

Click to expand...

Yet another reason to see what's happening with SQL Profiler. Or even just contacting the graph vendor.

Thuleman said:

This is a line chart. I don't think the detail is needed unless the user decides to zoom in. Ideally there would be some option to "plot fast" by using some sort of algorithm that eliminates the majority of the points just to plot the graph quickly and then re-queries the DB if the user zooms in or requests data for a single point.

Click to expand...

If there's a single line, then you've got one data point per minute for two whole months. It sounds like you either need to learn how to use the graphing tool or limit your query to shorter time spans. PTNL is on to something asking you to tune your query; SQL Server can return 90,000 rows in less than a second. If it's slower for you, you're doing something wrong.

It's not that a Profiler trace is difficult to set up, it's that the output doesn't really mean anything to me.

Limiting the query to shorter time spans isn't really the answer since some trends only show up over years. Depending on the dataset I have one data point every 15, 30, or 60 minutes, and I have data for a decade or more on some of those. Now, for yearly trends not every 15 minute data point needs to be plotted, in fact not every hourly one needs to either, one a day would work just fine.

One issue is that some data plots slowly, the other is that the tool is too complex and I would like to just generate a simple plot that shows the last 24 hours or the last 7 days or so. The current tool doesn't support that type of "quick view" hence I am looking at a different solution.

If you are really interested in this then I can provide more details per PM. I am not a programmer or a DBA, coming up with a different solution is just something I want to do because it's needed and the original 3rd party coders aren't going to do it.

I'm not suggesting that you should limit the query to shorter spans. I'm suggesting you use a coarser level of detail.

On one hand, you're telling us the query times out. Now, you're saying the data plots slowly.

There are currently 190 series in the DB. Querying different series produces different results in terms of performance. Time-outs are intermittent but they do happen often enough to notice. I did confirm that in the case of querying let's say a decade all data points are returned, but that fits with the application since the plotting tool also offers some statistical analysis which requires that all points are retrieved from the DB.

It's basically the wrong tool for the job if all one wants is to just have a quick look at the current conditions, it's too complex on the user end and too slow performance wise. All that's needed for the task at hand is the ability to retrieve data from the DB and plot it up in the aforementioned Highcharts.

That actually turned a little bit into a bear since there appears to be no reliable method to retrieve data from MSSQL when using Linux/Apache/PhP. Some folks suggest FreeTDS but I came across an ex-dev of FreeTDS on IRC who was very adamant about not using it in a production environment (maybe he is bitter, who knows).

I think it would be interesting to get to the bottom of the performance issue. The people who made the tool acknowledged the problem but like I said before they blame it on .Net "most of the problems we have had are more due to the generation of the queries in .NET" and aren't inclined to fix it since they aren't funded to do so. In the short term though I just need to get the current conditions plots up and integrated into a web page and then I can follow up on other stuff.

When you say "timeout", where are you observing the timeout -- from the reporting tool, from SQL Management Studio, from the web server's event logs, etc.? Also, this is a good reason as to why running SQL Profiler would be of benefit: you'd see exactly what SQL calls the webserver and/or reporting tool is making to the DB, and also see which query/queries are consuming the most time or getting called the most often.

The dismissive attitude of the report vendor is indeed frustrating, and placing a blanket blame statement on .NET is very arrogant. ADO.NET runs the query sent by the user, no matter what the origin (SQL string, stored proc, LINQ-to-whatever, etc.). If the vendor doesn't know what SQL statements their library is making, then that's a failure on their part and not the technology.