I've written a report in Crystal 10 that takes over an hour to run. The same report written wtih Microsoft Access takes seconds to run. They are both using the same ODBC datasource and are being run on the same PC at the same time.

I'm using Commands and have tried including selection criteria in the Commands versus in the report. I've moved as many calculations as I can to the Commands. I see no improvement.

Is this difference in run time for Crystal verus Access just something we have to live with or is there something more I can do to improve performance in Crystal?

How many commands are you using I have noticed a slowdown if you have multiple commands.

How many records is your report having to rummage through. The more records the longer it takes.

Can you post the sql? Any subreports in the Crystal report?

I've got four commands, one for each table. They're all pretty simple, the catch, I believe is that I have to sum the amounts in all 13 periods for both the credit & debit fiels (a total of 26 fields) to get a fiscal YTD total for my report. The example below is getting the YTD total amount from the GLAMOUNT (DBGLGAM) table for the fiscal year 2007. I added the other selection criteria (accounting unit and account #) to see if it helped.

Is there a better way to get a fiscal YTD total for GL amounts, budget and commitment/encumbrance than to sum those 36 fields?


SELECT "DBGLGAM"."FISCAL_YEAR", ("DBGLGAM"."DB_AMOUNT_01"+ "DBGLGAM"."DB_AMOUNT_02"+ "DBGLGAM"."DB_AMOUNT_03"+ "DBGLGAM"."DB_AMOUNT_04"+ "DBGLGAM"."DB_AMOUNT_05"+ "DBGLGAM"."DB_AMOUNT_06"+ "DBGLGAM"."DB_AMOUNT_07"+ "DBGLGAM"."DB_AMOUNT_08"+ "DBGLGAM"."DB_AMOUNT_09"+ "DBGLGAM"."DB_AMOUNT_10"+ "DBGLGAM"."DB_AMOUNT_11"+ "DBGLGAM"."DB_AMOUNT_12"+ "DBGLGAM"."DB_AMOUNT_13"+ "DBGLGAM"."CR_AMOUNT_01"+ "DBGLGAM"."CR_AMOUNT_02"+ "DBGLGAM"."CR_AMOUNT_03"+ "DBGLGAM"."CR_AMOUNT_04"+ "DBGLGAM"."CR_AMOUNT_05"+ "DBGLGAM"."CR_AMOUNT_06"+ "DBGLGAM"."CR_AMOUNT_07"+ "DBGLGAM"."CR_AMOUNT_08"+ "DBGLGAM"."CR_AMOUNT_09"+ "DBGLGAM"."CR_AMOUNT_10"+ "DBGLGAM"."CR_AMOUNT_11"+ "DBGLGAM"."CR_AMOUNT_12"+ "DBGLGAM"."CR_AMOUNT_13") as Actuals, "DBGLGAM"."ACCT_UNIT", "DBGLGAM"."ACCOUNT", "DBGLGAM"."SUB_ACCOUNT"
FROM "SCHOOL"."LAWAPP8DBJ"."DBGLGAM" "DBGLGAM"
WHERE "DBGLGAM"."FISCAL_YEAR"=2007 AND ("DBGLGAM"."ACCOUNT">=100 AND "DBGLGAM"."ACCOUNT"<=999) AND ("DBGLGAM"."ACCT_UNIT" >='100' AND "DBGLGAM"."ACCT_UNIT" <= '100999')

Forgot to answer the question about how many records the report is rummaging through.

It starts with the accounting unit/account combinations in the GL Master, which has about 13,000 records. The end result is 6,800 of those records.

It then reads the GL amount, budget and encumbrance files which have 18,000, 33,000 and 3,000 respectively. Big for us, but I suspect not so big compared to others.

The GL Master is my primary and the other three are outer-left joins because there could be accounts with budget but no actuals or encumbrances, accounts with actuals but not budgets or encumbrances and accounts with encumbrances but not budget or actuals - and I have to pick them all up.

Check out GLCONSOL (DBGLDCN) instead of GLAMOUNTS - it has actuals and budget by fiscal year/period/account. It may simplify things for you for actuals/budget amounts. You will still need to add each period to get a YTD though. Where are you pulling encumbrances from, ACCONSOL?

Regarding the differences in query responses...it's been a while since I've used Access, but I believe that you may get the initial results of a query quickly, but if you were to view all of the results of the query, this would take SIGNIFICANTLY longer.

Thanks for the tip on GLCONSOL. I'll take a look at that.

I'm getting commitments/encumbrances from GLCOMMIT (DBGLGOT).

So I think I understood you to say that each individual query might take the same amount of time whether run in Access or Crystal, but the entire report would probably take much longer in Crystal. Right?

Thanks for your help and advice.

When you save a query in Access, it creates and saves an "execution plan" that describes how the query will run. The execution plan depends on a variety of things, the number of tables, summaries, whether other queries are linked, etc.

Some execution plans result in the entire result recordset being made available at once, others make results available as they are produced.

You can see this when you execute a select query in the Access program. Sometimes you can use the "end" navigation button at the bottom of the query window to move to the last record in the result immediately, other times it can take forever, and even scrolling down to the next page of results is tedious.