Archive

Posts Tagged ‘Performance’

Ripplestone Performance Tips

October 16th, 2012 No comments

When publishing Crystal Reports to Ripplestone; there are a few simple options within the Crystal Reports designer that can be checked to increase the performance of the reports when they are run from within Ripplestone.

Page Setup

The first is to set the No Printer option on the Page Setup dialog box. This will stop the report from looking for the selected printer each time the report is run. It also has the added bonus of making the report look better within the web viewer. Open the Page Setup by clicking on Files and then Page Setup

SNAGHTML1d9286d6

Figure 1 – Page Setup Dialog Box

Report Options

The report option dialog box has several options that can help the report process quicker. Open the Report Option by clicking on File and then Report Options.

  • Perform Grouping On Server – This works with the “Use Indexes Or Server For Speed” option to allow the server to perform much of the processing for the report. This reduces the processing and memory used by the Ripplestone server.
  • Use Indexes Or Server For Speed – Using this option will allow the SQL query to use the database indexes and can increase the speed when selecting large amounts of data.
  • Verify on First Refresh –Unchecking this option will stop the report from first checking the database to refresh the schema. This will save a trip to the database.
  • Verify Stored Procedure on First Refresh – Unchecking this option will stop the report from first checking the stored procedure to refresh the schema. This will save a trip to the database.
  • Save Data With Report – Unchecking this option will keep the file size small and will allow the report to be loaded by the Crystal Report engine quicker.
  • Perform Query Asynchronously – Check this box to allow the report to execute the SQL statement without requiring Ripplestone to wait for the select statement to finish.

SNAGHTML1d9554de

Crystal Report Performance Tip – Stored Procedures

June 5th, 2009 No comments

As I work with clients and their Crystal Reports, one of the items that will come up sooner than later is how to increase the performance of their reports.  The performance issue becomes more noticeable once the reports are published into Ripplestone and the users are interactively running the reports.  If the report takes 5-10 minutes to run the users will let you know that the reports are “taking forever” to run.

One of the suggestions I usually have is to look into using stored procedures to do a lot of the work and have the stored procedure return a table that is easy to report from.  What I mean by “easy to report from” is that the table returned by the stored procedure has as much processing completed within the stored procedure so the report will not need to do the processing.

One of the techniques that I have used often is to have the stored procedure create a temporary table that I will fill with the data needed for the report and return the temporary table as the output of the stored procedure.

To create the temporary table, you declare the table name and the fields that the table will contain.  The example below is for a financial report for profit and loss.

Declare @tblProfitAndLossSummary Table
    (LocationName varchar(50),
    ActualExpense smallmoney,
    ActualRevenue smallmoney,
    NonProductivityPercentage decimal(9,1),
    PaidProductivity decimal(9,1),
    WorkedProductivity decimal(9,1),
    HourlyRate smallmoney)

If the data that will be going into the table will be coming from multiple locations or tables then you might need to do some type of calculation within the stored procedure.  In the above Profit and Loss example I needed to create a SQL Server cursor and then loop through the records and insert a record into the temporary table for each record within the cursor.

        /* Initialize the variables */
        SELECT @ActualRevenue = 0
        SELECT @RegularHours = 0
        SELECT @ActualExpense = 0
        SELECT @NonProductivityPercentage = 0
        SELECT @PaidProductivity = 0
        SELECT @WorkedProductivity = 0

To hold the data for each record, I created a variable for each of the fields in the temporary table and then would insert the data into the temporary table with the following code.

INSERT INTO @tblProfitAndLossSummary
(LocationName,
ActualExpense,
ActualRevenue,
NonProductivityPercentage,
PaidProductivity,
WorkedProductivity,
HourlyRate)
VALUES
(@LocationName,
@ActualExpense,
@ActualRevenue,
@NonProductivityPercentage,
@PaidProductivity,
@WorkedProductivity,
@HourlyRate)

I was then able to run calculations within the Cursor loop such as

/* This gets the Revenue amount for a store */
SELECT @ActualRevenue = SUM(BillingCharges.ChargeAmount)
FROM BillingCharges INNER JOIN BillTo ON BillingCharges.BillNumber = BillTo.BillNumber
WHERE (BillTo.StoreNumber = @StoreNumber)
AND (BillingCharges.PeriodFrom >= CONVERT(DATETIME, @StartDate, 102)) AND (BillingCharges.PeriodTo <= CONVERT(DATETIME, @EndDate, 102))

/* Calculate the Regular Hours */
SELECT @RegularHours = ((@RegularTime + @Category1Time) / 60)

In the above example there were many additional queries and calculations needed to get the values for the 7 fields being inserted into the temporary table.

In the end all the data that the report needs is contained in the temporary table and the last step would be to return the data with a final SELECT statement.

SELECT LocationName,
    SUM(ActualExpense) AS Expense,
    ActualRevenue,
    AVG(NonProductivityPercentage) AS AvgNonProdPercent,
    AVG(PaidProductivity) AS AvgPaidProd,
    AVG(WorkedProductivity) AS AvgWorkedProd,
    AVG(HourlyRate) AS AvgHourlyRate
FROM @tblProfitAndLossSummary
GROUP BY LocationName, ActualRevenue
ORDER BY LocationName

RETURN

The crystal report has the added bonus of being easy to format since most of the data is ready for the report.

In this example the report before the stored procedure was very slow with many formulas and sub-reports.  After the report was re-written with the stored procedure it runs from within Ripplestone in under 5 seconds.