Performance using MSSQL stored procedures

[Gabor Nyul]

Hi,

I'm a complete beginner on Web2py.
I have to write a complex reporting app (no CRUD, just read only reports (tables, graphs and Excel download possibilities), around 50 reports.
The database is already given, so I do not want to create any table, instead of that I have to run big and complex selects, mainly included in the stored procedures hosted on our big MSSQL database

My question is: Is Web2py designed to be used for such purpose and can it be used to display to the users several thousands rows (of course paginated or displayed as a graph based on that data)?
So how you are dealing with the (possibly client side) pagination as of course I do not want to run the whole stored proc again and again just to retrieve the next 25 rows.
The database is big (several hundreds of tables with lots of them over 100 millions of rows.
Some complex stored procs are running 3-5 secs and once the data is ready to be delivered to the client, I would like to treat it on the client side (filtering, sorting, exporting, etc)

If the answer is yes, what table component is used for that and how a client side paging is implemented

Any help would be appreciated.
If you could drive to an already working example that would be fantastic

[Alex Glaros]

with so many reports, why not just put them in a reports generator?

this way, clients could tweak or create new simple reports themselves without programmer

http://blog.capterra.com/5-awesome-free-and-open-source-reporting-software-packages/

Alex Glaros

[Gabor Nyul]

Well, my users have as good as no computer literary, so the reports (lists) must be predefined for them.
Theyhave just to enter some parameters (like date from, date to) Nd run the reports.
That's why I'm looking for an easy way to present those lists to them. And of course, all the normal security staff (only specifically user groups van see specific reports, where, I think web2py gives a correct solution) should be provided.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/Re4SUDrq1HQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Brian M]

Can you use web2py for a reporting site - sure, a lot of what I do with it is reporting. I've been using DataTables.net which can provide pagination, sorting, search, aggregation and even basic export (via TableTools) for almost free. While it can enhance regular old HTML tables, with large numbers of rows I'd definitely pass it JSON datasets instead because it'll perform much better. You could also do all sorts of client side manipulation with AngularJS, React, Ractive, etc. too. Depends on your skills and needs. As for graphing, sure you can - there are plenty of good graphing libraries out there. Lately I've been using http://www.amcharts.com/javascript-charts/ 

You'll need to use executesql() to run your stored procedures since the normal DAL doesn't do them itself. 

One thing that concerns me is your mention of needing to display thousands of rows. I can't imagine such a large report being very user friendly. Even if it is paginated there is no way anybody can scan through that much stuff and come up with much for useful/actionable conclusions. You probably need to be looking at far better filtering and/or aggregation so that you're providing quality over raw quantity.

Like Alex said though - it might be worth while to look into a dedicated reports generator. You're on MSSQL so why not investigate Microsoft SQL Server Reporting Services? You can build nice interfaces in that too that'd let your users fill out simple forms. Just because it's a reports generator doesn't mean that your users have to be computer savvy enough to be able to build the reports from scratch - that's were you come in and setup the types of reports they need so that they're easy for the user to access in a way that's easy for them.

~Brian

To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Gabor Nyul]

First of all thanks for the hints.
So it means that the web2py's integrated grid will not be suitable for my needs. Bad luck. :-(
Then here is a stupid and somehow provocative ( :-) ) question: What is the value add of web2py (compared for example to flask or bottle) if in every case I have to use an external javascript library like Datatable, or slickgrid, or Tabulator?

Now regarding your concern (normally I am saying exactly like you, nobody is able to check some thousands rows, but in fact... well, yes. They are doing it. Actually in Excel) about the number of rows, here are some explanations:
Those reports are about our activity and each department is analyzing their activities for the past, as well as for the coming several weeks.
So the initial reports are extracted from a huge database of hundreds of millions of records. So the size of the reports are from some hundred to at most, let's say 25000 records.
After that the users are starting "working" on the data. They are sorting and filtering them for after that do what they have to do with the information they have got.
There is always a sentence I hate: "Can I export it to Excel?" :-) I'm teaching them to use a central too and database and do not export it into a tool like Excel to manipulate the data to see "exactly" how they want them.

That's why I'm looking for a user friendly table (grid) solution where I an able to format (color) the data based on some rules, sort and filter them.

As a background information: I'm coming from a windows desktop development world (.NET, Delphi), where all this is not an issue, everything is performing well and fast. But.. It is not web based so the deployment of the updates are a kind of headache. And I like Python. :-)

[Alex Glaros]

Unstructured spreadsheet processing is a world-wide problem.

Consider creating a small prototype in web2py to replace the unstructured spreadsheet-based processes focused on demonstrating interoperability advantages.

If there are data-quality/time improvements, and clearly articulated innovation opportunities arising from integrated data, then perhaps exec will let you replace spreadsheet processes with enterprise-wide web2py system.

[Gabor Nyul]

This is exactly what I'm looking for. I do not want to Excel in that reporting circle.
But to achieve this I need tools to help me.
And those tools shouldn't give me and the team too much work compared to that Excel based reporting.
That why I'm asking questions.

[Alex Glaros]

Not a reporting tool, but as a tool to replace silo-oriented spreadsheet systems, web2py is the easiest-to-implement, best tool around.

Hopefully it would not take much to build an example where web2py creates new value by virtue of integrating the data. Look for places to eliminate batch jobs, backlogs, bottlenecks, redundancy, improve data quality, create new dashboards, etc.

Reports are doable in web2py, probably as fast as any dedicated reports tool, but why re-invent the wheel?

[Willoughby]

I like Python too but heck if you're that well versed in .NET I'd just do ASP.NET and use a nice grid:

https://www.devexpress.com/Products/NET/Controls/ASP/Grid/

Done and done.

[Dave S]

On Thursday, December 1, 2016 at 11:08:18 AM UTC-8, Gabor Nyul wrote:

First of all thanks for the hints.
So it means that the web2py's integrated grid will not be suitable for my needs. Bad luck. :-(
Then here is a stupid and somehow provocative ( :-) ) question: What is the value add of web2py (compared for example to flask or bottle) if in every case I have to use an external javascript library like Datatable, or slickgrid, or Tabulator?

Every case?

Answer 1:  For many cases you can use the bundled tools.  Your case isn't typical, it seems, because of the processing time your queries require.  The normal case for pagination is to use ajax to update the table, with the query having a limitby stipulation; this is typically fast enough for most people.  Improvement can be done by using caching, but the size of your results may make caching impractical.

Answer 2:  web2py provides a simple backend that often can get a website done quickly.  The default frontend tools provide a good starting point for  Datatables provides advanced frontend features that can be used to make the user experience feel more like a spreadsheet.  Your users in particular may appreciate that, since they are manipulating the data presentation.

If you had insight into what the users were looking for during their examination of the data, you may be able to provide a better query that could simplify the data presentation.  But perhaps there is no pattern shared by the different users, and you'd end up with a separate query for each user.  

/dps

[Brian M]

What Dave S. said is key 

"If you had insight into what the users were looking for during their examination of the data, you may be able to provide a better query that could simplify the data presentation." 

Sit down with your users and find out what it is that they actually need to get their jobs done efficiently. Really take the time to work with them to understand what they're doing and why. If they're needing to take hundreds or even thousands of rows and mess with them in Excel then they're clearly not being given the information they actually want/need. You might be able to come up with improved Stored Procedure, queries or DB Views that run faster but more importantly you can come up with improved processing and presentation which is what ultimately matters.

tldr;

Be careful with this

 "I'm teaching them to use a central too and database and do not export it into a tool like Excel to manipulate the data to see "exactly" how they want them."

if you aren't giving them something that is at least pretty darn close to what they want, or more importantly need, then you're going to have a hard time winning them over. They won't give a hoot if it is a central tool with a fancy database if it isn't more usable and a clear benefit to them vs Excel, which, lets be honest, lets people do a whole lot and feel in control. If instead you can give them a system where what they're shown is immediately actionable that'll get them interested. Then when they see that they didn't have to mess around in Excel, there aren't any more problems with weird errors popping up because somebody overwrote the vital formula in BJ:59 or a range changed messing up their summaries and they don't have to remember how they set things up last time then they'll really be sold. At that point they probably won't even care if the database took 5 seconds to get the data because you just saved them 10+ minutes of fiddling with Excel just to get started doing their job. ;) 

Why use web2py over flask, bottle, asp.Net or whatever if you've got to use JavaScript stuff for display too? Well lets be honest, a lot of picking frameworks really just comes down to personal preference because ultimately they can all do the job. I happen to think that web2py is great because it is full stack with pretty much everything included, yet isn't overly opinionated, it's very easy to learn and work with and most importantly I can get new screens/features up quickly. I really like having a DAL instead of an ORM because it just seems more sensible to me, others may not, and some like you who have to use Stored Procedure might be left out (though the ability to easily use executesql to run custom SQL or Stored Procs is another benefit of the DAL). The views system works well when good old HTML is enough but you can also easily create the JSON you need to drive newer JavaScript based stuff when needed. The FORM, SQLFORM, and the grid/smartgrid are great, sometimes magical, especially when you're just wanting to get something basic working quickly. But for some uses, which unfortunately seems to include yours, those built-in grids aren't the end-all, be-all and you really are better off with one of the client-side JS grids that have been mentioned. Fortunately, web2py's still great for getting the raw data, doing some server-side manipulation and transforming it into the JSON that the JS grids, or something like AngularJS, need.  

[Gabor Nyul]

Brian,

In fact this is already done. I have a complete specification of each report.

Just to give you an example: We are  a railway company, having several hundreds of train per day. There are a lots of modifications (above 60%, Don't ask why) to the original plan where we are assigning the engines and the staff.

The planning staff needs a report to see for the coming month all the tracks and track segments, with all the statuses to be able to react (definitely assign or remove/reallocate resources, etc.).

So from the UI point of view even the paging is bad as they have to smoothly scroll among the data.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/Re4SUDrq1HQ/unsubscribe.

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

[Dave S]

On Friday, December 2, 2016 at 12:33:46 AM UTC-8, Gabor Nyul wrote:

Brian,

In fact this is already done. I have a complete specification of each report.

Just to give you an example: We are  a railway company, having several hundreds of train per day. There are a lots of modifications (above 60%, Don't ask why) to the original plan where we are assigning the engines and the staff.

The planning staff needs a report to see for the coming month all the tracks and track segments, with all the statuses to be able to react (definitely assign or remove/reallocate resources, etc.).

So from the UI point of view even the paging is bad as they have to smoothly scroll among the data.

I'm not sure SQLFORM has a limit to the number of rows it can display; you can leave off the limitby and give everything.  You can do the same when supplying Datatables; using an external library allows you to use the additional features a dedicated UI toolmaker can provide. The transfer of data may be longer than you like, but you've probably already measured that, and it may be small compared to the stored procedures time.

For a more reasonable download time (as a later enhancement, perhaps), ajax can be used to provide "smooth scrolling", such as you get when using Google Groups (the thread list view) or Google Image, or a bunch of other places.  I think the Datatables site has an example of this, but it's been a while since I looked there ... my handful of users don't have a lot of slice-and-dice needs.

/dps

[Brian M]

https://datatables.net/extensions/scroller/examples/ if you must have huge numbers of rows displayed.