Poor Performance help needed 1

[src2]

I'm running a crystal report (CR 8.5) against a Notes R6 database via a system ODBC connection. The report runs correctly but is pretty slow. The record selection I'm using is as follows:

Select {?TimePeriod}
Case "":
{CR_View_for_Steve.OpenedOn} >= minimum({?DateRange}) and {CR_View_for_Steve.OpenedOn} <= maximum({?DateRange})
Case &quot;All&quot;:
{CR_View_for_Steve.OpenedOn} >= Date(1900,01,01)
Case &quot;Today&quot;:
{CR_View_for_Steve.OpenedOn} in CurrentDate
Case &quot;Last 7 Days&quot;:
{CR_View_for_Steve.OpenedOn} in Last7Days
Case &quot;This Month&quot;:
{CR_View_for_Steve.OpenedOn} in MonthToDate
Case &quot;Last Month&quot;:
{CR_View_for_Steve.OpenedOn} in LastFullMonth
Case &quot;Last 90 Days&quot;:
{CR_View_for_Steve.OpenedOn} <= currentdate and {CR_View_for_Steve.OpenedOn} >= (currentdate - 90)
Case &quot;Last 6 Months&quot;:
{CR_View_for_Steve.OpenedOn} <= currentdate and {CR_View_for_Steve.OpenedOn} >= (currentdate - 183);

and Uppercase({?SearchString}) in Uppercase({CR_View_for_Steve.ShortDescription})


I have checked to see what SQL expression was created and this is all that was created:

SELECT
CR_View_for_Steve.&quot;OpenedOn&quot;, CR_View_for_Steve.&quot;Resolution&quot;, CR_View_for_Steve.&quot;ShortDescription&quot;, CR_View_for_Steve.&quot;Ticket&quot;, CR_View_for_Steve.&quot;User&quot;
FROM
&quot;CR_View_for_Steve&quot; CR_View_for_Steve
ORDER BY
CR_View_for_Steve.&quot;OpenedOn&quot; ASC

Can anyone suggest how to improve my performance speed?

Thanks

 

[synapsevampire]

It might be because you're using a case.

I can't recall if Notes databases are datetimes, but I get very explicit:

Here's a @startdate formula:

// If Yesterday
If {?Predefined Date Range} = &quot;Yesterday&quot; Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),0,0,0)

// If Last Week
Else
If {?Predefined Date Range} = &quot;Last Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)-6

and there's lots more, more than I should post here. Check my bio for the email address and I'll send a very comprehensive list which will demonstrate the record selection formula too, which is key.

-k

 

[rhinok]

Try using a Switch statement for your Date parameters and a conditional If-Then-Else Boolean for your Search string:


//Switch statement used to pass Date values to the database
{CR_View_for_Steve.OpenedOn} In
Switch
(
//Use 'Range' as a parameter value intead of &quot;&quot;
//Set {?DateRange} as a Ranged Date parameter
{?TimePeriod} = 'Range', {?DateRange},
{?TimePeriod} = 'All', Date(1900,01,01) To CurrentDate,
{?TimePeriod} = 'Today', CurrentDate,
{?TimePeriod} = 'Last 7 Days', Last7Days,
{?TimePeriod} = 'This Month', MonthToDate,
{?TimePeriod} = 'Last Month', LastFullMonth,
{?TimePeriod} = 'Last 90 Days', CurrentDate To (CurrentDate - 90),
{?TimePeriod} = 'Last 6 Months', CurrentDate To (CurrentDate - 183)
//we can figure out a better formula to find data by month if necessary
)

//If-Then-Else Boolean allows for wildcard searches, if necessary.
//Users will need to enter the appropriate wildcard characters during their search
//The default value of 'All' will force all records to be returned if a search is not required
And
(
If
//Use 'All' as the default parameter value instead of &quot;&quot;
{?SearchString} <> 'All'
Then
{CR_View_for_Steve.ShortDescription} Like {?SearchString}
Else If
{?SearchString} = 'All'
Then
True
)

Even with the boolean statement, the Uppercase isn't going to be passed to the database. You could try setting your Report Options to use case-insensitive SQL if its recognized by your database. If not, you might try building a SQL Expression that performs the uppercase conversion on your ShortDescription field.

 

[putts]

I was having similar performance issues on a report based
off a table of a couple million records.

Through ASP, what I did was use the following code
Session(&quot;oRpt&quot.RecordSelectionFormula = WHERE_String
Where the WHERE_String was dynamically built via the ASP page.

The reason I went with this is because if you attempt to do most functions (CDATE,DATE,IN,etc.) through the report's Record Selection Formula, it will still go through all the records and then just filter the ones out that matched the formula.

With the code I used the report only pulls the records that match the search, thus greatly increasing the performance of the report.

This might be helpful to you, if you need more info out, just ask.

 

[synapsevampire]

Very efficient use of the switch, rhinok*, star by me.

I haven't thoroughly tested it's ability to pass SQL when coexisting with other complex formulas, but the preliminary looks great.

-k

 

[rhinok]

Don't give me a star too soon, SV - I found a mistake. Replace the last 2 lines of the Switch statement with the following:

{?TimePeriod} = 'Last 90 Days', (CurrentDate - 90) to CurrentDate,
{?TimePeriod} = 'Last 6 Months', (CurrentDate - 183) to CurrentDate

Switch is my best friend We're using it extensively on my current project.

 

[synapsevampire]

The content wasn't the point, it was the use of Switch AND passing the SQL that I liked.

-k

 

[src2]

Hi and thanks to everyone who answered my post. I would have responded sooner but haven't been able to get back to my report for a few days. After studying the problem and looking at the suggestions which I thought were great btw, I think my problem isn't so much how I'm selecting my records but in my connection to the database. When I refresh the report it says accessing database in the lower left hand corner. This message stays on for over half the time the entire report runs. I'm thinking there is a problem with my ODBC notes connection causing it to be very slow. If anyone has in great ideas on what could be the problem I'd be really happy to hear them.

Thanks again