Speed issues Crystal Reports X 1

[scottpdann]

It is taking an unbearably long time to read the records for this report. Is there anything I could do better in the record selection formula?

{ARF.SQL_TRANS_DATE} in Date (2005,01,01) to Date (2005, 12,31) and
isnull ({FAMILY.SQL_DEACTIVATION_DATE})and
{ARF.SQL_BILL_CODE} in ["0001"to "0041","0100"to"0299" ,"0331" to "0332","0500" to "0700", "0997" to "0997"] and
({ARF_SQL_ACCT_ASSOC.SQL_CREDIT_ACCT} = "01-00-21100" or {ARF_SQL_ACCT_ASSOC.SQL_DEBIT_ACCT}="01-00-21100" )

Any suggestions would be appreciated.

 

[synapsevampire]

Please post your database and connectivity used, you're asking how to speed up a database but not supplying basic technical information.

I would guess that you aren't getting the filtering to pass to the database, you can check what is being passed by using Database->Show SQl Qeury, depending upon the database and your means for accessing it.

One means is to create a SQL Expression for the {ARF.SQL_BILL_CODE} to convert it to a numeric:

CAST({ARF.SQL_BILL_CODE} as int)

Or whatever the syntax is for your database (this also assumes that it is always a numeric), and then compare against it.

Another would be to rewrite your selection formula as:

(
isnull ({FAMILY.SQL_DEACTIVATION_DATE})
)
and
(
{ARF.SQL_TRANS_DATE} in Date (2005,01,01) to Date (2005, 12,31)
)
and
(
{ARF.SQL_BILL_CODE} in "0001" to "0041"
or
{ARF.SQL_BILL_CODE} in "0100" to "0299"
or
{ARF.SQL_BILL_CODE} in "0331" to "0332"
or
{ARF.SQL_BILL_CODE} in "0500" to "0700"
or
{ARF.SQL_BILL_CODE} in "0997" to "0997"
)
and
(
{ARF_SQL_ACCT_ASSOC.SQL_CREDIT_ACCT} = "01-00-21100"
or
{ARF_SQL_ACCT_ASSOC.SQL_DEBIT_ACCT}="01-00-21100"
)

The key is to make sure that everything is passed to the database for processing.

-k