Practical limit to number of results in a LiveReport?

I have a client that is having performance problems running certain LiveReports. The query itself is efficient, at least according to the Oracle optimizer, with a cost of only 21. However, the source tables are large, with DTreeCore > 100 million rows and DTreeAncestors having > 800 million. Depending on the report parameters, the result set can be > 100,000 rows, in which case the report essentially never completes (> 24 hours).

Apart from the obvious question about how useful a report that contains a list of >100,000 dataid values is, is there a practical or recommended limit to the number of rows that should be generated by a LiveReport, in terms of impact on the server processing the query result?

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

GregPetti

> @Patrick_McManus said:
> Well, Oracle's optimizer seems to think the query in question is efficient (Explain Plan cost = 24) but it returns a huge number (> 100,000 in some cases) of rows from a join of DTreeCore and DTreeAncestors. Both tables are huge, > 100,000,000 rows. Having said that, the returned rows only contain dataid values from DTreeCore, so there is very little formatting or processing of the returned data taking place.
>
> How does cursor fetch mode vs. transaction mode affect performance of LiveReports?

Turning off cursor fetch mode can improve performance. When it is off, the code does a simple CAPI.exec and passes all of the results back whereas cursor fetch mode actually has oscript looping through the results set, allowing the oScript to limit the number of results. This mode is most useful during development, particularly as the error messages are more useful (if you have verbose turned on) and also if you’re trying to curb the number of results (though you could also put a limit in the SQL.
Transaction mode simply determines if the query executes in a transaction or not. The original default was to do everything in a transaction even if the SQL was read only. We added this mode because sometimes there were deadlock issues with LiveReports and for most queries it is not necessary.

Greg

All comments

Appu_Nair1

In the olden days if you looked at a server you could see many Livelink.exe's queuing up nine times out of ten it was some user running a LR and not getting results. The Livelink.exe was the connection to the browser . We would probably kill those and usually the user would have clicked many times and left as well.IN IIS the process only waits for a maximum of 900 secs I think maybe it has improved so if a LR written producing tremendous amounts of data will definitely fall into that trap as well. Nowadays if one were monitoring admin.threadstatus you probably know who is running and how long its taking for things to come back. As a rule If I were giving a LR for a customer I would almost make it into a background process and mail the results to the user. So really to answer the question the process has no real limit but you probably have to look at the general picture . Many of us employ thread monitoring and grab logs into splunk and things like that and see if we can find trends and go that route. It might be interesting to see in a thread log how much the SQL takes and how much does it take to build an output page I believe there might be some things you may be able to unearth. Now I see that OT is making people go to SmartUI .But that would mean the JavaScript in your browser does the work does it even get it if the threads hasn't provided the output . I know not. Maybe people like @David Templeton or @Kyle_Swidrovich or one of the real big performance guys like @Brad_Bosley_(bradbosley_-_(deleted)) could give you a good compelling answer.

Kyle_Swidrovich

There's no one-size-fits-all answer to this.
The processing of the result set is almost never the issue -- it's almost always the cost of executing the SQL query.
However, large result sets can eat up a lot of memory when Content Server has to transform the raw SQL result into native objects for display in a report.
The impact on the database depends on how the database is configured, whether or not you're making use of cursor fetch mode or transaction mode within your LiveReport, and what the SQL is trying to accomplish. If the core schema isn't tuned for the type of retrieval you're doing (e.g., no index for where/order by clauses) then it's going to run inefficiently. Trying to augment core schema to meet the needs of your report could introduce adverse effects to the overall functioning of the system, and can prevent a Content Server Update from succeeding.

Patrick_McManus

Well, Oracle's optimizer seems to think the query in question is efficient (Explain Plan cost = 24) but it returns a huge number (> 100,000 in some cases) of rows from a join of DTreeCore and DTreeAncestors. Both tables are huge, > 100,000,000 rows. Having said that, the returned rows only contain dataid values from DTreeCore, so there is very little formatting or processing of the returned data taking place.

How does cursor fetch mode vs. transaction mode affect performance of LiveReports?

GregPetti

Patrick_McManus

"Transaction mode simply determines if the query executes in a transaction or not. The original default was to do everything in a transaction even if the SQL was read only. We added this mode because sometimes there were deadlock issues with LiveReports and for most queries it is not necessary."

Since most LiveReports are based around SELECT statements, then they would not represent transactions that need to complete and be committed. Is Transaction mode more efficient than cursor fetch mode for large result sets from an otherwise simple SELECT query?

Jean Philippe Forti

Are you saying that you want to return 100'000+ rows in an html page?
Even if the query is fast, getting this might take a while and you will have a timeout.
Shouldn't you use a BI system for such an amount of data? Or at least a scheduled Webreport that runs during the Week End.
Not mentioning the fact that by offering a Livereport, there is a risk that more than one user clicks on it and it could impact the overall performance of the system.

Patrick_McManus

I wouldn't want to do such a thing, but one client regularly runs a simple LiveReport that can return over 100,000 rows. They've written a report whose sole purpose is to return the DataIDs of all of the descendants of a parent folder (don't ask me why), and they run it on-demand during office hours. They have over 100,000,000 documents in Content Server.