Home » Developer & Programmer » Forms » report not giving output
| report not giving output [message #79238] |
Fri, 17 May 2002 01:08  |
hnetscape
Messages: 30
Registered: December 2001
Location: India
|
Member |
|
|
Hello sir,
I have a problem in executing a report. I have created indexes also. The report is showing as running. Can you please look into the query.
SELECT SUM(INVOICEQTY*BASICPRICE) VALUE
From SaInvoice0 a, SaInvoice1 b
where a.compcode=b.compcode
and a.branchcd=b.branchcd
and a.seriescd=b.seriescd
and a.regioncd is not null and a.regioncd<>'FA'
and a.invoiceno=b.invoiceno
and b.BranchCd = 'A1'
and aoseriescd in ('AO','SAO','ASC','A2E')
and invoicedt between to_date('01-apr-02') and to_date('30-apr-02')
and (a.compcode,customercd) in (select compcode,customercd from keycustomers
where category='M')
Regards,
Raju.
|
|
|
|
| Re: report not giving output [message #79255 is a reply to message #79238] |
Mon, 20 May 2002 07:04  |
Siz
Messages: 2
Registered: May 2002
|
Junior Member |
|
|
Hi Raju,
If it does not run properly on SQL*plus, then u need to tune your query.
SELECT SUM(INVOICEQTY*BASICPRICE) VALUE
From SaInvoice0 a, SaInvoice1 b
where a.compcode=b.compcode
and a.branchcd=b.branchcd
and a.seriescd=b.seriescd
and a.regioncd is not null and a.regioncd<>'FA'
and a.invoiceno=b.invoiceno
and b.BranchCd = 'A1'
and aoseriescd in ('AO','SAO','ASC','A2E')
and invoicedt between to_date('01-apr-02') and to_date('30-apr-02')
and (a.compcode,customercd) in (select compcode,customercd from keycustomers
where category='M')
1. Recheck your indexes. Do u have a composite index on compcode,branchcd,seriescd in tables a and b?
2. If yes in which order?
3. Are they referenced in the join in the correct order?
3. try to put your joins immediately after the WHERE clause and other filtring conditions like "a.regioncd is not null and a.regioncd<>'FA' " away from the where clause.
4. Try to put as many filters as possible in the query itself. It runs faster when the records are filtered at the database itself, rather than selectiog it to the report and then filtering at the front end.
5. Instead of "IN" try to use "EXISTS"
Analyse the explain plan.
From SQL PLUS try
>set autotrace traceonly explain
This will show u the plan and hide the output of the query, so u don't need to wait for the qury to run.
Check out OTN for materials on performance tuning.
Here are some extracts from OTN...(Tuning Reports)
Performance Tuning in Reports
3.4.2.2 Eliminate Redundant Queries
Ideally, an application should have no redundant queries (queries which return data which is not required), since they will clearly diminish performance. However, situations can arise where an application not only needs to produce a different format for different users, but also needs to utilize different query statements. Clearly this could be achieved by developing two different applications, but it may be desirable to have a single application for easier maintenance.
For example, in a report, you could disable redundant queries by use of the SRW.SET_MAXROW() procedure. The following code in the Before Report trigger will disable either Query_Emp or Query_Dept, depending on a user parameter:
IF :Parameter_1 = `A' then
SRW.SET_MAXROW(`Query_Emp',0);
ELSE
SRW.SET_MAXROW(`Query_Dept',0);
END IF;
There are several points to remember when using SRW.SET_MAXROW():
 The only meaningful place to use SRW.SET_MAXROW() is in the Before Report trigger (after the query has been parsed). If SRW.SET_MAXROW() is called after this point, then the SRW.MAXROW_UNSET packaged exception is raised.
 The query will still be parsed and bound, but no data will be returned to the report.
Perform Calculations within the Query SQL
When performing calculations within an application, the general rule of thumb is that the more calculations that can be performed within the query SQL the better. When calculations are included in the SQL, they are performed by the database before the data is returned, rather than the data being returned and cached before the calculation is performed by the application. From Oracle 7.1 onwards, you can include server-stored user-defined PL/SQL function calls in the query select list. This is more efficient then using a local PL/SQL function (e.g., in a formula column), since the calculated data is returned as part of the result set from the database, so no further calculations are required.
In Oracle8, calls to methods can use the SELF parameter, which simplifies and speeds the passing of arguments
3.4.4.2 Reduce Layout Overhead
When generating a default layout, Reports puts a frame around virtually every object to protect it from being overwritten when the report is run. At runtime, every layout object (frames, fields, boilerplate, etc.) is examined to determine the likelihood of that object being overwritten. In some situations (for example, boilerplate text column headings), there is clearly no risk of the objects being overwritten, and hence you can remove the immediately surrounding frame. This reduces the number of objects that Reports has to format, and hence improves performance.
Similarly, when an object is defined as having an undefined size (variable, expanding or contracting in either or both the horizontal and vertical directions) then extra processing is required, because Reports must determine that instance of the object's size before formatting that object and those around it. Where feasible, set this sizing to fixed, which will eliminate this additional processing, since the size and positional relationships between the objects is already known.
Format triggers should also be placed at the highest level possible in the object/frame hierarchy, so that the trigger fires at the lowest possible frequency. For example, if there are four fields in a frame, a format trigger at the field level will fire four times, whereas a format trigger at the frame level will need to fire only once.
If the display attributes of a field are to change dynamically (for example, to draw attention to values outside the norm), then all attribute changes can be set in a single call to SRW.ATTR(), or in multiple calls to SRW.SET built-ins with each call setting a separate attribute. Although the latter technique makes for more readable code, runtime efficiency is usually better with a single SRT.ATTR() call -- especially if many attributes need to be set.
3.4.4.4 Consider Linking Tables
As with most operations, there are a number of ways to create data models that include more than one table. Consider, for example, the standard case of the department-employee join; i.e., the requirement is to list all the employees in each department in the company. In Reports Developer, the programmer can either create a single query, or two queries and use a master-detail relationship between the two.
On the application side, when designing the data model it is preferable to minimize the actual number of queries by using fewer, and larger (multi-table) queries rather than more, and simpler (single-table) queries. Each time a query is issued, Reports Developer needs to parse, bind and execute a cursor. A single query is therefore able to return all the required data in a single cursor rather than many. Also be aware with master-detail queries that the detail query will be re-parsed, re-bound and re-executed for each master record retrieved. In this instance it is often more efficient in a report to merge the two queries and use break groups to create the master-detail effect.
It should be noted, however, that the larger and more complex a query becomes, the more difficult it can be to maintain. Each site needs to decide how to balance its performance and maintenance requirements
3.4.4.6 Turn Off Debug Mode
When your application is in regular, production use, make sure it is not running in debug mode.
In debug mode, the runtime products will gather information and perform other internal operations. Once your application is debugged, these operations are no longer needed and detract from performance.
In Reports, debug mode is controlled through the runtime parameter RUNDEBUG; this should be set to NO.
3.4.4.7 Use Transparent Objects
Give layout objects (e.g., frames and repeating frames) a transparent border and fill pattern.
Transparent objects do not need to be rendered in a PostScript file. As a result, processing is faster when objects are transparent.
3.4.4.13 Limit Your Use of Break Groups
Ensure that the break order property is set for as few columns in the break group as possible (break order is indicated by a small triangle to the left of the column name in the group). Each break group requires at least one column within in to have break order set.
If sorting is necessary for a break group, use an ORDER BY clause in its SQL. This will cause the rows to be returned already sorted by break order, and improve performance by reducing the amount of sorting that must be done on the client.
For each column that has break order set, Reports places an extra column into the appropriate query's ORDER BY clause. The fewer columns in the ORDER BY, the less work the database server has to do before returning the data. The creation of a break group may make the ORDER BY clause defined in the query redundant. If this is the case, then the redundant ORDER BY should be removed, since this will require extra processing on the database.
Break order columns should be as small as possible, and should also be database columns (as opposed to summary or formula columns) wherever this is feasible. Both of these conditions can help the local caching that Reports does before the data is formatted to be as efficient as possible. Clearly, these conditions can not always be met easily, but are worth considering all the same.
3.4.4.16 Use PL/SQL instead of SRW.DO_SQL for DML
Use PL/SQL for DML, unless you want to pass parameters to your DML statements.
SRW.DO_SQL() should be used as sparingly as possible, because each call to SRW.DO_SQL() necessitates parsing and binding the command and opening a new cursor (just as with a normal query). Unlike the query, however, this operation will occur once each time the object owning the SRW.DO_SQL() fires. For example, if a PL/SQL function calls SRW.DO_SQL(), and the group where the function resides returns 100 records, then the parse/bind/create cursor operation will occur 100 times. It is therefore advisable to only use SRW.DO_SQL() for operations that cannot be performed within normal SQL (for example, to create a temporary table, or any other form of DDL), and to use it in places where it will be executed as few times as possible (for example, in triggers that are only fired once per report).
Writing DML statements in PL/SQL is faster than an SRW.DO_SQL call containing the same statement. The reason to use SRW.DO_SQL for DML statements is that it can concatenate bind parameters to construct the DML statement. For example, you can have SRW.DO_SQL create a table whose name is determined by a parameter entered on the runtime parameter form:
SRW.DO_SQL (`CREATE TABLE' || :tname || `(ACOUNT NUMBER
NOT NULL PRIMARY KEY, COMP NUMBER (10,2))');
Usage Notes: You can also use the dbms_sql package that comes with Oracle 7.1 or later for DML. Refer to your Oracle database server documentation for more information.
3.4.4.19 Adjust the ARRAYSIZE Parameter
The value of array processing has been noted earlier.
For Report Builder's ARRAYSIZE executable argument (e.g., ARRAYSIZE=10), enter as large a value as you can. Note that the array size is measured in kilobytes, not rows. ARRAYSIZE means that Report Builder can use that number of kilobytes of memory per query in executing your report. Report Builder uses Oracle's array processing, which fetches multiple records in batches, instead of one record at a time. As a result, you can control the amount of data to be fetched by the batch processes.
3.4.4.20 Adjust the LONGCHUNK Parameter
For Report Builder's LONGCHUNK executable argument (e.g., LONGCHUNK=10), enter as large a value as you can. Refer to the Oracle installation information for your operating system for the recommended amount for your machine. LONGCHUNK determines the size of the increments in which Report Builder will retrieve a LONG value. The LONGCHUNK size is measured in kilobytes.
By increasing LONGCHUNK as much as possible, you can reduce the number of increments it takes Report Builder to retrieve LONG values.
3.4.4.22 Avoid Fetch-Aheads in Previewing
Report Builder provides you with the ability to display data such as total number of pages, or grand totals in the report margins or on the header pages. This is an extremely useful function, but has the requirement that the entire report must be processed before the first page can be displayed.
Avoiding "fetch-ahead" operations when designing a report for the Previewer or Live Previewer will help speed the display of the first page of the report.
The following items can result in fetching ahead when referenced before the data on which they rely:
 total number of pages/panels
 grand totals
 break columns that are formulas
 break columns that have Value if Null specified
When you use a total number of pages field source, Report Builder must save all of the pages in temporary storage in order to determine the total number of pages. This can significantly increase the amount of temporary disk space used by Report Builder, and the additional writing to files can slow performance.
Cross-product groups also cause fetching ahead. In order to cross-tabulate the data in a cross-product group, Report Builder must first fetch all of the data. It should be noted that these items are not really performance "problems." They slow down the Previewer or Live Previewer, but they do not affect performance when writing to a file or some other destination.
Note: A column can cause fetching ahead even if it is not displayed. For example, a grand total may not appear in the report output, but, since it is in the report, fetching ahead may still occur when Report Builder calculates it.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 04 08:18:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
