This information is being shared with you to help with report development and resource utilization. During the 2009 ICIS National meeting we found there is a need for more standardized reports. OECA is addressing this concern. In the meantime, we are recommending the practices below to address the need to reduce the amount of data returned by some ad hoc queries, especially for NPDES universe users. Recommendations #2 and #3 below are important but often misunderstood.
The number of rows returned is the data that is returned over the network to your PC, it is not how much data is processed on the database server to get your desired results. When users experience problems it is generally from the amount of data that they are pulling back in the report, it is not how much processing is done to get the results. ICIS can handle reports processing against millions of database records, this is no problem, what you actually retrieve into your report is where the issue lays. If you have a report that pulls back, this means actually what will be displayed in your report, 200,000 rows of data this would be approximately 6,450 pages of information.
It is a good practice to return just the data you need to see. If there is a need to return millions of records it is generally for counting purposes, this is where you would use crosstab reports (Excel users know these as pivot tables). Crosstab reports will just pull back the totals of records that meet a criteria, not the actual data. Please contact ICIS support for assistance if
you have a need to return extremely large amounts of data.
The following documents contain recommended suggestions (does not apply to ICIS Standard Reports):
1) Attached are reporting Best Practices XI Guide (See attachment)
2) Attached is how to uncheck "Retrieve Duplicate Rows". Very important for users of the NPDES universe, because of the very large amount of data, this will help reduce the amount of data that is retrieved without effecting report output. (See attachment)
3) A recommended practice is to restrict data returned to reports, try to get just the data you need. This is done by using filters in your query, e.g., filter by a region, statute or section of law, use date ranges. You can also make your response time during report development and testing faster by restrict the amount of data returned. When the report has been tested and you are ready for use then turn the restriction off. Attached is a document showing how to restrict rows of data returned. (See Attachment)
4) If you know or expect that your report will return a very large amount of data and therefore run for more than a few minutes please schedule your report. Scheduling does not mean you must wait overnight, you can schedule to execute immediately and get your report in a short time. Attached are instructions on how to determine how many rows your report will return. Instructions on how to schedule reports are found in the attached Best Practices XI Guide. (See attachment)