report data optimization

[brianj774]

I have some reports that generate really large data sets. In particular, we're dealing with transaction data, for say, 1 day, week, month, quarter, or year.

I am concerned that I'm getting reports from some of my larger customers that they cannot even generate a 1 day report in a two minute period. After some investigation, I have found the following timings for a 20,000 row dataset.

Code: Select all

execution of query, and return of dataset .................... 1.5 s
composition of data set into dataTable object ................ 7.5 s
preparation of reporting objects in browser .................. 5.0 s
callback to retrieve constructed report previously stored ... 17.5 s

We can see that the majority of the time is spent simply pushing the completed report (approximately 1100 pages) to the client. The question is, can it be done any faster? The query itself is plenty fast. There's probably some improvements that I could make to the part of the code that composes the datatable, but that's a pretty tight loop (and, I think, it comprises both assembly of the DataTable object AND of the actual report assembly). The third element is going to be pretty static, regardless of datasize. But the 4th element is killing us, especially on larger data sets.

I notice that the data it sends back to the client is all raw text. My thought was that there must be some kind of toggle to enable compression of the report data, but a quick search didn't turn up any.

Is this a feature that could be added? Is there a better way to handle these kinds of massive reports?

Please advise.

[Alex K.]

Hello,

Please clarify - the time specified it is the time of sending a entire report or one page of the large report? Alternatively, you can enable data compression on the server side.

Thank you.

[brianj774]

I *believe* the time specified is for the entire report. I am unaware of how to configure things so that it only pulls one page at a time.

Can you provide info as to how to enable data compression? The "Programming Manual" suggests that it's enabled by default, but it does not appear to be the case.

[brianj774]

I Do not see the "DataCompression" property in the StiMvcViewer (The component I'm using).

[brianj774]

As 'proof', here's the data returned from the callback (captured via firebug). It's a simple report, with a header, and a single row of data, and a datestamp in the footer.. In all, there are 22 pieces of data. You can see that the amount of data returned is colossal, compared to the actual field raw data, and the extremely repetitive nature of the table structure should make this data HIGHLY compressible.

It's easy to imagine that a report containing 30 or so of these rows per page, and 1000 pages will be positively enormous. There must be some way to eliminate this issue.

===============================

{"pageNumber":0,"pagesCount":1,"pageMargins":["39px 39px 39px 39px"],"pageSizes":[{"width":850,"height":1100}],"pageBackgrounds":["#ffffff"],"zoom":100,"viewMode":"OnePage","reportGuid":"1fc5466d049b602ba92a192fd67f0ca6"}##.s69a3a3b7{Font:16pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:left;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.sa6059f82{Font:6pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:right;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.s85f50480{Font:10pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:left;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.se30eadcb{Font:bold 8pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:Black;border-bottom-style: solid;border-bottom-width:3px;text-align:left;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.sfe2deab1{Font:8pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:Black;border-bottom-style: solid;border-bottom-width:3px;text-align:left;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.s0a78def7{Font:bold 8pt Arial;border:0px;border-top-color:transparent;border-top-style:solid;border-top-width:1px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:Black;border-bottom-style: solid;border-bottom-width:3px;text-align:right;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.s0f4cca11{Font:8pt Arial;border:0px;border-top-color:Black;border-top-style: solid;border-top-width:3px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:left;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.sa80d22b3{Font:8pt Arial;border:0px;border-top-color:Black;border-top-style: solid;border-top-width:3px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:center;vertical-align:middle;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.s098ae52d{Font:8pt Arial;border:0px;border-top-color:Black;border-top-style: solid;border-top-width:3px;border-left-color:transparent;border-left-style:solid;border-left-width:1px;border-right-color:transparent;border-right-style:solid;border-right-width:1px;border-bottom-color:transparent;border-bottom-style:solid;border-bottom-width:1px;text-align:right;vertical-align:top;line-height:1.15em;overflow:hidden;white-space:nowrap;}
.dtree {font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;font-size:11px;color:#666;white-space:nowrap;}
.dtree img {border: 0px;vertical-align: middle;}
.dtree a {color: #333;text-decoration: none;}
.dtree a.node, .dtree a.nodeSel {white-space: nowrap;padding: 1px 2px 1px 2px;}
.dtree a.node:hover, .dtree a.nodeSel:hover {color: #333;text-decoration: underline;}
.dtree a.nodeSel {background-color: #c0d2ec;}
.dtree .clip {overflow: hidden;}
.dtreeframe {border-right:1px;border-right-style:solid;border-right-color:Gray;}
###STIMULSOFTPAGESEPARATOR###<table cellspacing="0" cellpadding="0" border="0" style="border-width:0px;width:772px;border-collapse:collapse;">
<tr>
<td class="s69a3a3b7" colspan="8" rowspan="2" style="height:30px;width:380px;"><div style="width:380px;height:30px;">TANGERINE TEST 365</div></td><td style="border:0px;height:20px;width:60px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:70px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:61px;"></td><td style="border:0px;height:20px;width:9px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:10px;"></td><td class="sa6059f82" colspan="3" style="height:20px;width:150px;"><div style="width:150px;height:20px;">Page 1 of 1</div></td><td style="border:0px;height:20px;width:2px;"></td>
</tr><tr>
<td style="border:0px;height:10px;width:60px;"></td><td style="border:0px;height:10px;width:10px;"></td><td style="border:0px;height:10px;width:70px;"></td><td style="border:0px;height:10px;width:10px;"></td><td style="border:0px;height:10px;width:61px;"></td><td style="border:0px;height:10px;width:9px;"></td><td style="border:0px;height:10px;width:10px;"></td><td style="border:0px;height:10px;width:10px;"></td><td style="border:0px;height:10px;width:121px;"></td><td style="border:0px;height:10px;width:9px;"></td><td style="border:0px;height:10px;width:20px;"></td><td style="border:0px;height:10px;width:2px;"></td>
</tr><tr>
<td class="s69a3a3b7" colspan="8" style="height:30px;width:380px;"><div style="width:380px;height:30px;">Orders By SKU Report</div></td><td style="border:0px;height:30px;width:60px;"></td><td style="border:0px;height:30px;width:10px;"></td><td style="border:0px;height:30px;width:70px;"></td><td style="border:0px;height:30px;width:10px;"></td><td style="border:0px;height:30px;width:61px;"></td><td style="border:0px;height:30px;width:9px;"></td><td style="border:0px;height:30px;width:10px;"></td><td style="border:0px;height:30px;width:10px;"></td><td style="border:0px;height:30px;width:121px;"></td><td style="border:0px;height:30px;width:9px;"></td><td style="border:0px;height:30px;width:20px;"></td><td style="border:0px;height:30px;width:2px;"></td>
</tr><tr>
<td class="s85f50480" colspan="8" style="height:20px;width:380px;"><div style="width:380px;height:20px;">From 9/29/2011 to 9/30/2013</div></td><td style="border:0px;height:20px;width:60px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:70px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:61px;"></td><td style="border:0px;height:20px;width:9px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:121px;"></td><td style="border:0px;height:20px;width:9px;"></td><td style="border:0px;height:20px;width:20px;"></td><td style="border:0px;height:20px;width:2px;"></td>
</tr><tr>
<td class="s85f50480" colspan="8" style="height:20px;width:380px;"><div style="width:380px;height:20px;">SKU: SkywardHat</div></td><td style="border:0px;height:20px;width:60px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:70px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:61px;"></td><td style="border:0px;height:20px;width:9px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:10px;"></td><td style="border:0px;height:20px;width:121px;"></td><td style="border:0px;height:20px;width:9px;"></td><td style="border:0px;height:20px;width:20px;"></td><td style="border:0px;height:20px;width:2px;"></td>
</tr><tr>
<td class="se30eadcb" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Order ID</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="se30eadcb" style="height:20px;width:130px;"><div style="width:130px;height:20px;">Date</div></td><td class="s0a78def7" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Amount</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="s0a78def7" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Shipping</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="s0a78def7" colspan="2" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Handling</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="s0a78def7" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Tax</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="s0a78def7" colspan="2" style="height:20px;width:70px;"><div style="width:70px;height:20px;">Service Fee</div></td><td class="sfe2deab1" style="height:20px;width:10px;"></td><td class="se30eadcb" colspan="3" style="height:20px;width:140px;"><div style="width:140px;height:20px;">Purchaser</div></td><td class="sfe2deab1" colspan="2" style="height:20px;width:22px;"></td>
</tr><tr>
<td class="s0f4cca11" style="height:20px;width:70px;"><div style="width:70px;height:20px;">114880761</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s0f4cca11" style="height:20px;width:130px;"><div style="width:130px;height:20px;">03/08/2013 11:22:09 AM</div></td><td class="s098ae52d" style="height:20px;width:70px;"><div style="width:70px;height:20px;">$88.50</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s098ae52d" style="height:20px;width:70px;"><div style="width:70px;height:20px;">$0.00</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s098ae52d" colspan="2" style="height:20px;width:70px;"><div style="width:70px;height:20px;">$7.94</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s098ae52d" style="height:20px;width:70px;"><div style="width:70px;height:20px;">$0.00</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s098ae52d" colspan="2" style="height:20px;width:70px;"><div style="width:70px;height:20px;">$0.00</div></td><td class="sa80d22b3" style="height:20px;width:10px;"></td><td class="s0f4cca11" colspan="3" style="height:20px;width:140px;"><div style="width:140px;height:20px;">, </div></td><td class="sa80d22b3" style="height:20px;width:20px;"></td><td class="sa80d22b3" style="height:20px;width:2px;"></td>
</tr><tr>
<td style="border:0px;height:862px;width:70px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:130px;"></td><td style="border:0px;height:862px;width:70px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:70px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:60px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:70px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:61px;"></td><td style="border:0px;height:862px;width:9px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:10px;"></td><td style="border:0px;height:862px;width:121px;"></td><td style="border:0px;height:862px;width:9px;"></td><td style="border:0px;height:862px;width:20px;"></td><td style="border:0px;height:862px;width:2px;"></td>
</tr><tr>
<td style="border:0px;height:16px;width:70px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:130px;"></td><td style="border:0px;height:16px;width:70px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:70px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:60px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:70px;"></td><td style="border:0px;height:16px;width:10px;"></td><td style="border:0px;height:16px;width:61px;"></td><td class="sa6059f82" colspan="4" style="height:16px;width:150px;"><div style="width:150px;height:16px;">9/30/2013 7:57:42 AM</div></td><td style="border:0px;height:16px;width:9px;"></td><td style="border:0px;height:16px;width:20px;"></td><td style="border:0px;height:16px;width:2px;"></td>
</tr><tr>
<td style="border:0px;height:4px;width:70px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:130px;"></td><td style="border:0px;height:4px;width:70px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:70px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:60px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:70px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:61px;"></td><td style="border:0px;height:4px;width:9px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:10px;"></td><td style="border:0px;height:4px;width:121px;"></td><td style="border:0px;height:4px;width:9px;"></td><td style="border:0px;height:4px;width:20px;"></td><td style="border:0px;height:4px;width:2px;"></td>
</tr><tr>
<td style="border:0px;height:1px;width:70px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:130px;"></td><td style="border:0px;height:1px;width:70px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:70px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:60px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:70px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:61px;"></td><td style="border:0px;height:1px;width:9px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:10px;"></td><td style="border:0px;height:1px;width:121px;"></td><td style="border:0px;height:1px;width:9px;"></td><td style="border:0px;height:1px;width:20px;"></td><td style="border:0px;height:1px;width:2px;"></td>
</tr>
</table>

[Vladimir]

Hello, Brian

We had in mind that please try to enable the data compression in IIS settings, the browser should recognize and decompress the data stream automatically.

For example, IIS 6:
http://blogs.microsoft.co.il/blogs/yevg ... erver.aspx

Thank you.

[brianj774]

Ahh, now I understand. Thank you. I've forwarded this info to our decision team.

[HighAley]

Hello.

We are very glad to help you.
Let us know if you need any additional help.

Thank you.