Discoverer資料多,欄位多,匯出到Excel變慢

Discoverer Plus 10.1.2.3 Excel Export On Wide Worksheet With 40 Columns Takes More Than One Hour To Export [ID 1293559.1]
		Modified 09-FEB-2011     Type PROBLEM     Status MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

---

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Discoverer - Version: 10.1.2.3 and later   [Release: 10.1.2 and later ]
Information in this document applies to any platform.

Symptoms

When using Discoverer Plus 10.1.2.55.26 with Cumulative Patch (CP) 7, Excel export of a worksheet
with many columns is taking over one hour to complete.

This crosstab worksheet includes 40 columns and over 11,000 records.

If the worksheet is exported from Desktop, it completes in a few minutes.
It is only when exporting to Excel from Discoverer Plus, that it takes over an hour.

If the worksheet is exported to CSV, it only takes a few minutes.

Cause

Workbook design not well suited for Discoverer Excel export.

Discoverer Plus/Viewer worksheets are intended for high level views of data that can fit within
a standard web page.
Any time, you start to exceed 13 - 15 columns in width, you may notice performance degradation in
rendering pages and especially when exporting to Excel.

When a workbook is exported to Excel, Discoverer needs to format each cell in the Excel spread sheet
and when the workbook has thousands of rows or many columns, it takes that much longer to format
each cell.

Solution

Ideally, the worksheet should be redesigned to limit the number of columns to no more than 12 - 15.
If this is not possible, the following workarounds may be used to export the worksheet without experiencing a performance impact.

• Use Discoverer Plus to Export wide workbook to CSV format and then open resultant .CSV file in Excel.
• When Excel export is desired, run workbook in Discoverer Desktop and then export to Excel.
  
  
  
  Discoverer Desktop involves a single network protocol (SQL*Net) and a connection between the Desktop (PC)
  and the database (client server architecture).
  Any activity (running workbooks or exporting) is usually faster in a client/server environment than in a web server environment.
• You may also be able to achieve a performance gain by applying Discoverer Cumulative Patch 8.  This patch introduced a new pref.txt file setting which may improve performance of Discoverer exports to Excel.
  
  Patch 9694503, CUMULATIVE PATCH 8 : MLR ON TOP OF 10.1.2.3 FOR BUG 9112482 + NEW BUG FIXES
  
  After applying patch as described, please make a back-up copy of the pref.txt file and then add the following setting to the original file under the "Application" heading:
  
  
  
  ExcelExportWithMacros=0
  
  
  Save changes and run "applypreferences.sh" file.

References

PATCH:9694503 - CUMULATIVE PATCH 8 : MLR ON TOP OF 10.1.2.3 FOR BUG 9112482 + NEW BUG FIXES

Related Products Middleware > Business Intelligence > Discoverer > Oracle Discoverer Keywords WORKSHEET; EXPORT TO EXCEL; DISCOVERER PLUS; DISCOVERER EXPORT; PERFORMANCE; EXPORT; DISCOVERER; EXCEL