Remedy odbc report slow

[MJD500]

A report using the remedy odbc driver takes a long time to run. I was able to get 5,000 records in 20 minutes. If I use the oracle ODBC driver, it takes approximately 3-4 minutes. BUT I can't use the oracle ODBC driver because the datetimestamp in the diary fields are displayed in julien date format ex: 7898313928; thus I must use the remedy odbc driver to be able to display the date properly. Could it be my select statement...? any suggestions on how I could re-order the select statement to improve the speed? or any other ideas?

My select statement:
({SRMIS---SRMIS/RG.Create-Date-(Core)} >= datetime(2001,10,01,00,00,00) and {SRMIS---SRMIS/RG.Create-Date-(Core)}
< datetime(2001,11,01,00,00,00))
and
{SRMIS---SRMIS/RG.Submission-Method-(Inc)} = &quot;Phone&quot; and
{SRMIS---SRMIS/RG.PoP-Location-(Inc)} = &quot;SD Moncton&quot; and
{SRMIS---SRMIS/RG.Status-(Inc)} in [&quot;Resolved&quot;, &quot;Closed&quot;]

 

[dgillz]

Jocelyne,

I've never tried it, but there is a JuliandateToDate function on the crystal decisions website. Maybe you can use that UFL in conjunction with the other ODBC driver to solve the performance problem.
Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Cell/VM: 714-348-0964

 

[MJD500]

dgillz

I am aware of the JuliantoDate function; unfortunately it cannot be used in a blob (diary field).. I am not able to manipulate the contents of these types of fields within Crystal so I have to use the remedy odbc driver which is really slow when I am using the date field in the select statement... If I don't use the date field, it is faster. That's why I asked if I can fine tune the select statement in any way to make it faster?

Thank you for response though.

Jocelyne

 

[losthorizon]

Did you try going to &quot;Database&quot; > &quot;Remove Database from Report&quot;? This feature will only let you remove the databases that aren't utilized in the report. It will notify you if the database is in use in the report. These excess databases were likely added when the report was being designed. Eliminating the unused databases from the report should speed things up considerably. Just a thought.

 

[MJD500]

Hi:

I am not using any excess databases. The query is slow only when I add the Date range to the select statement. I was wondering if I re-ordering the select statement will make it go faster?

 

[dgillz]

Another thought. I have done this on a couple of Pervasive, aka Btrieve databases and it worked wonders for performance. I created a new index for the file on the field that I was using the select logic on.

There are some serious downsides to be aware of if you go this route - for instance if the software is updated, and the file(s) you use get changed - POOF -- your new index is gone.

Another thing I have found which only relates to summary reports. If you are not going to display or drill down to details, try the &quot;perform grouping on server&quot; option. It is designed to do the selection and number crunching at the server level, thus reducing server to client communications traffic. As I said though, this is of absolutely zero use if you need to display detail sections.

Good Luck. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine
Cell/VM: 714-348-0964