Speed of rendering html data (10,000+ rows)

[Vineet]

Dear All,
I wish to share some of my findings (may be useful to someone).
And one question (at the bottom of this post).

Task: Displaying a report with a large amount of data returned from
controller.

A resultset is fetched from MySQL
custdata = db.executesql(qry, as_dict=True)
There are 10,000+ records in it.
----------------------------------------------
Earlier, I was parsing 'custdata' using 'for' loop---
----------------------------------------------
for i in custdata:
mystr2 = ''.join([mystr2, '<tr><td>' , str(i[0]) , '</td>'])
mystr2 = ''.join([mystr2, '<td>' , str(i[1]) , '</td>'])
mystr2 = ''.join([mystr2, '<td>' , str(i[2]) , '</td>'])
......more omitted......
mystr2 = ''.join([mystr2, '<td>' , str(i[12]) , '</td></tr>'])

mystr3 = '</tbody></table>'
mystr = ''.join([mystr1, mystr2, mystr3])
----------------------------------------------
It took 175 seconds to process the data.
1) Using 'for' loop is slowing down the processing.
2) Evaluating ''.join everytime in 'for' loop was costly.
----------------------------------------------
Now, I parsed 'custdata' like ---
----------------------------------------------
1) Used list comprehension instead of 'for' loop
2) Used jn=''.join (i.e. local variable instead of global variable)

jn = ''.join
lst = [ jn(['<tr><td>' , str(i[0]) , '</td>',
'<td>' , str(i[1]) , '</td>',
'<td>' , str(i[2]) , '</td>',
...... more omitted......]) for i in res]

mystr=jn(lst)
----------------------------------------------
The performance boost is amazing......
It took ONLY 0.078 seconds
----------------------------------------------
My Question is this---
----------------------------------------------
If I send the big string output (which contains html code) to a View
from a Controller like this---
return dict(mystr=mystr)

and render it like this---
{{=XML(mystr)}}

it takes again 1 minute to display the plain html data.
(not to speak of using dataTables; it haults the task).

For testing purpose, I saved the output string in .html file like
this---
flnm=open('myfile.html', 'w')
flnm.writelines(mystr)
When I loaded the .html file in browser (outside of web2py), it loads
within 5 seconds.

In Web2Py, how do I render it quickly in View?

Thanks,
Vineet

[cjrh]

Raymond Hettinger recently tweeted that .join() was O(n) but concats are O(n^2), or some other super-linear factor like that, so your speedup makes perfect sense.   I think you've discovered a performance bottleneck in the rendering system of web2py.  Shouldn't be too hard to find with a profiler.  I wish I had time to play with this, but I don't.   But I am very interested in this issue, which is why I am replying (so I can tick "Email updates to me :)

[Massimo Di Pierro]

Please open an issue in google code and link this thread. This should
be improved.

[cjrh]

Hi Veneet

Following Massimo's recommendation that an issue be opened, I have taken the liberty of creating one on your behalf here (Issue 307):

http://code.google.com/p/web2py/issues/detail?id=307&sort=-id

I am impatient and I couldn't wait.  Please bookmark the issue and follow progress there.  

regards

Caleb

[Vineet]

Caleb,
Thanks for opening an issue on my behalf.
I am eagerly following the developments on this.
Thanks,
Vineet

[VP]

> for i in custdata:
>    mystr2 = ''.join([mystr2, '<tr><td>' , str(i[0]) , '</td>'])

...

> lst = [ jn(['<tr><td>' , str(i[0]) , '</td>',
>            '<td>' , str(i[1]) , '</td>',
>            '<td>' , str(i[2]) , '</td>',
>         ...... more omitted......]) for i in res]
>
> mystr=jn(lst)
> ----------------------------------------------
> The performance boost is amazing......
> It took ONLY 0.078 seconds
> ----------------------------------------------

This makes sense because a crude analysis shows that the first
approach is O(n^3) while the second one is O(n).

(I think)

[Vineet]

Ref. to issue 307 in google code.
http://code.google.com/p/web2py/issues/detail?id=307&sort=-id

As suggested by "Caleb", I prepared the HTML myself (in the
controller).
Then returned the generated html to the View and rendered it there.
==========
My finding:---
==========
Still it takes long time to load the page (using either of the
following 2 ways).

1) Without XML helper, it displays html code as it is (i.e.
<table><th>MyName</th>....etc.)
Takes long time to display the page.

2) With XML, it displays properely formatted table.
Takes long time to display the page.

Whether anybody else had a similar situation like the same?
Any suggestions/comments highly appreciated.

---Vineet

[pbreit]

If you really need to return 10,000 records to the browser, you should be returning json and then rending with some sort of data grid.

[Sebastian E. Ovide]

why you need to return 10k records in one go ?




--
Sebastian E. Ovide

[Vineet]

@Sebastian, @pbreit,
I understand what you mean to say.
If I get you rightly, I should fetch only a limited no. of rows
through pagination.
It makes sense for most of the situations.

But for some cases, rendering all the records on single page is
required.
e.g. consider a big automobile workshop.
A report for spare parts price list (with >12,000 parts) is to be
viewed.
For the customer, using pagination & clicking "next", "next",... is
unsuitable.
After rendering the total parts on single page, he/she may apply a
desired filter and analyze the data.


On Jun 21, 9:50 pm, "Sebastian E. Ovide" <sebastian.ov...@gmail.com>
wrote:

[pbreit]

We used YUI DataTables at my last company and they worked really well but we only got up to around 1000 records on a page (with very quick paging, filtering and sorting). I've been wanting to hook them up to Web2py but haven't needed it so far.

http://developer.yahoo.com/yui/datatable/

[Chris May]

Have you tried pasring the data using your 0.078 second method into a
json format and using jQuery to parse it?

[Anthony]

Maybe check out DataTables new virtual scrolling feature: http://datatables.net/blog/Introducing_Scroller_-_Virtual_Scrolling_for_DataTables. I think jQgrid may have a similar feature as well.

 

Anthony

 

On Tuesday, June 21, 2011 1:26:50 PM UTC-4, Vineet wrote:

@Sebastian, @pbreit,
I understand what you mean to say.
If I get you rightly, I should fetch only a limited no. of rows
through pagination.
It makes sense for most of the situations.

But for some cases, rendering all the records on single page is
required.
e.g. consider a big automobile workshop.
A report for spare parts price list (with >12,000 parts) is to be
viewed.
For the customer, using pagination & clicking "next", "next",... is
unsuitable.
After rendering the total parts on single page, he/she may apply a
desired filter and analyze the data.

On Jun 21, 9:50 pm, "Sebastian E. Ovide" <sebasti...@gmail.com>

[Jason Brower]

Or print it. I have had to do massive amounts of data in html and pdf
format.
BR,
Jason Brower

On 06/21/2011 08:26 PM, Vineet wrote:
> @Sebastian, @pbreit,
> I understand what you mean to say.
> If I get you rightly, I should fetch only a limited no. of rows
> through pagination.
> It makes sense for most of the situations.
>
> But for some cases, rendering all the records on single page is
> required.
> e.g. consider a big automobile workshop.
> A report for spare parts price list (with>12,000 parts) is to be
> viewed.

> For the customer, using pagination& clicking "next", "next",... is

[contatog...@gmail.com]

It can paginate data?
_____________________________________________
Gilson Filho

Web Developer
http://gilsondev.com

[Vineet]

As suggested by the community, I am trying to export json data to
dataTable.
But only blank dataTable is rendered (with desired formatting, search
box, etc.)
Pl. introspect into my code as to where I have a mistake.
Data is not displayed (as returned by "get_data" method).
I have made sure that the tables in MySQL have been populated.

-------------------
Controller
-------------------
def show_data():
return dict()


def get_data():
custdata = db.executesql(qry, as_dict=True)
return dict(mydata=custdata)

# For testing purpose, I returned response.json(custdata) in a
separate method & validated the output on "jsonlint.com".
It is valid json.
But, here the returned value is dict(mydata=custdata).
How do I reference the custdata object in View?
Already tried {{=mydata}} in <script> tag in View

-------------------
View (show_data.html)
-------------------
{{extend 'layout.html'}}

<script>
$(document).ready(function() {
var oTable = $('.smarttable').dataTable( {
"sScrollY": "200px",
"sAjaxSource": "{{=URL('MIS','get_data.json')}}",
"sDom": "frtiS",
"bDeferRender": true
} );
} );
</script>


# html table tags go here

<h1>Customer Database</h1>
<br />
<table class="smarttable">
<thead>
<tr>
<th>Customer Name</th><th>Vehicle No</th>....etc.
</tr>
</thead>
<tbody>

</tbody>
</table>


# This link is for testing json data returned.
<a href='{{=URL('MIS','get_rows.json')}}'>my_json</a>

Thanks,
Vineet

On Jun 22, 1:51 am, "contatogilson...@gmail.com"

<contatogilson...@gmail.com> wrote:
> It can paginate data?
> _____________________________________________

> *Gilson Filho*
> *Web Developerhttp://gilsondev.com*

[Vineet]

I tried doing like this:--

In controller--

def get_data():
custdata = db.executesql(qry, as_dict=True)

return response.json(custdata)

And in View,

....other code is as earlier (hence omitted)
"sAjaxSource": "{{=URL('MIS','get_data')}}",


Still, the dataTable is blank.
Pl. help.

---Vineet

> > *Web Developerhttp://gilsondev.com*- Hide quoted text -
>
> - Show quoted text -

[Vasile Ermicioi]

try

def get_data():
   custdata = db.executesql(qry, as_dict=True)

   return response.json(custdata.as_list())

also go to this link   http://yoururl/app/controller/get_data 

you should see a json response

[Anthony]

On Saturday, June 25, 2011 1:07:39 PM UTC-4, elffikk wrote:

try

def get_data():
   custdata = db.executesql(qry, as_dict=True)
   return response.json(custdata.as_list())

 

He's already using as_dict=True in the executesql call, so doing custdata.as_list() shouldn't be necessary.

 

[Anthony]

Is MIS your app name, or the controller name? If it's the app name, your URL call also needs the controller name, or it will think MIS is the controller and the Ajax call will fail.

On Saturday, June 25, 2011 1:02:50 PM UTC-4, Vineet wrote:

I tried doing like this:--

In controller--

def get_data():
    custdata = db.executesql(qry, as_dict=True)
    return response.json(custdata)

And in View,

....other code is as earlier (hence omitted)
"sAjaxSource": "{{=URL('MIS','get_data')}}",


Still, the dataTable is blank.
Pl. help.

---Vineet

> On Jun 22, 1:51 am, "contatog...@gmail.com"

[Vineet]

a='MyWheels', c='MIS', f='get_data'.
So, {{=URL('MIS','get_data')}} should work.
But I do not get the data in dataTable.
---Vineet

> > > > *Web Developerhttp://gilsondev.com*-Hide quoted text -

[Anthony]

What happens if you go to /MyWheels/MIS/get_data? Do you get the proper JSON returned? If so, then the problem is probably in your client-side JS code. Also, on the client side, check in Firebug (or similar) to see if the Ajax call is getting made properly, and if it is returning the JSON. You may need to work on debugging the client-side JS/DataTables code.

 

Anthony

On Saturday, June 25, 2011 10:57:23 PM UTC-4, Vineet wrote:

a='MyWheels', c='MIS', f='get_data'.
So, {{=URL('MIS','get_data')}} should work.
But I do not get the data in dataTable.
---Vineet

> > > On Jun 22, 1:51 am, "conta...@gmail.com"

[Anthony]

Looks like DataTables requires your JSON to include a few special parameters (http://datatables.net/usage/server-side), so you'll probably need to manipulate the JSON on the server side before returning it to the client.

 

Anthony

On Saturday, June 25, 2011 11:21:59 PM UTC-4, Anthony wrote:

What happens if you go to /MyWheels/MIS/get_data? Do you get the proper JSON returned? If so, then the problem is probably in your client-side JS code. Also, on the client side, check in Firebug (or similar) to see if the Ajax call is getting made properly, and if it is returning the JSON. You may need to work on debugging the client-side JS/DataTables code.

 

Anthony

On Saturday, June 25, 2011 10:57:23 PM UTC-4, Vineet wrote:

a='MyWheels', c='MIS', f='get_data'.
So, {{=URL('MIS','get_data')}} should work.
But I do not get the data in dataTable.
---Vineet

> > > On Jun 22, 1:51 am, "cont...@gmail.com"

[Anthony]

Note, you might also check out PowerTable (https://bitbucket.org/rochacbruno/powertable), which is a web2py plugin for DataTables. There's also a jqGrid widget that's part of plugin_wiki (you can use it even on non-wiki pages) -- see http://web2py.com/book/default/chapter/13#Current-Widgets.