 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Performance issue | Login/Join |
| Platinum Member |
I guess it depends upon a couple of things. Do you know where specifically the time is consumed 5 minutes to output PDF - Data Retrieval from RDBM's - Sorting by Reporting Server - Generating the PDF Without specifics it could be anyone's guess. | |||
|
| Expert |
as Fortune says, run the report without pdf or html output, just holding the report files as ON TABLE HOLD. see how long that takes. if it takes a very short time, then you know your time is being consumed by your network in delivering the output to the enduser's pc. What is the form ofyour orginating database? are there indexes on all the 14 (now 15)fields you sort by? Edit your profile please to say where you are and who you work for.
| |||||
|
| Virtuoso |
What type of database is it. Can you post the master file. you say "on 14 fields to do a page break when any of those 14 fields changes." So this might create a new page for every record in the database...is that correct? 11.000 pages will take some time.....
| |||||||
|
| Master |
Since your records are already sorted and you are just using the BY fields for control breaks, you could try using TABLEF instead of TABLE. This would eliminate the actual sorting. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| <Helpme> |
Thanks for all your posts. Most of the time is consumed in sorting. Reading just the records is taking approximately 60 secs and the table command that does the sorting is taking more than 3 mins. I forgot to mention that we use DB2 database. for 11 thousand records, around 350 page report is generated as there will be around 65 records for each combination of those 14 fields and each combination takes one and half pages. But since there is a pagebreak, the next combination is started on the next page. I will try TABLEF like PBrighWell suggested and hopefully it reduces the processing time... Thanks again for all your posts. | ||
|
| Master |
60 secs to read 11K rows? That seems like an very long time. First thing I'd do is use TABLEF and SQL tracing to make sure the SQL being generated is as efficient as it can be. If you can't get efficient SQL, write your own using SQL passthru. In my environment, just returning a large spreadsheet with 11K rows or a similar PDF can take a long time and if there are a lot of columns, it can take a very long time. I don't mean execution time, I mean delivery time. One of the thing I do so that the user is not just sitting at his screen waiting, is use DSTRUN to run a RC job immediately and deliver the report via email. What I do is write the parameters, user id, email address, etc. to a file. Then I run the RC job using DSTRUN. This frees up user's browser. It also eliminates the possibility of the user's browser timing out. In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux. | |||
|
| Master |
Also, make sure you are not selecting on defined fields. With DB2, you will define the field to every row in the database and then do the selection. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| <Helpme> |
Thanks for your posts. We generally distribute the reprot to library and show it on the application queue page using API. But when I run it through Dev Studio itself I found it taking this long time. Moreover the TABLEF command does not allow me few stylesheet settings. | ||
|
| Master |
helpme, In DB2, you can create an index over your sort fields using the basic SQL CREATE INDEX SCHEMA.INDEXNAME ON SCHEMA.TABLENAME (FIRSTSORTFIELD ASC, SECONDSORTFIELD ASC, THIRDSORTFIELD ASC...); "To improve performance we have modified the table to have an extra column whose value changes when any of those 14 columns value changes" This is where you use something called an EVI (or Bitmap if you're on Oracle) for fields that are very high cardinality and used in your where clause. CREATE ENCODED VECTOR INDEX SCHEMA.INDEXNAME ON SCHEMA.TABLENAME(FLAG_FIELD); you're running db2, which has some other really great features that will really help you, such as materialized query tables. Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database. | |||
|
| Platinum Member |
if the code isn't too much you may want to post it and see if we can offer any additional suggestions. Prod: WebFOCUS 7.1.6, Windows 2003 Dev: WebFOCUS 7.6.2, Windows 2003 | |||
|
| <Helpme> |
Thanks Jason. We have an index created on the sort field. Now, we are sorting only one field. I think we can give a thought about materialized query tables... FortuneCookie, I will not be able to post the code for some reasons. Thanks again. | ||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |