Optimise Crystal Reports

Hi,

Im running Windows 2003 Server/SQL 2005 (32bit 4gb) with Goldmine Corporate 6.7. Im using BOE XI to schedule reports and Crystal 9 to design the reports.

When I run reports which only integrate the Goldmine tables, the reports run very quickly (10 seconds at worst). However, when I utilise a table which is not a generic to goldmine, the report runs at a snails pace taking hours to complete.

Ive noticed the reports can speed up if a play around with the joins but Im not sure why, could someone explain the joins?

to give you and example of one of my reports. We have a large customer database and within each customer record, there is a status field which is stored in Key2 of the goldmine database table known has Contact1 - this allows us to see at a glance how far the customer is through our processes (there are about 12 statuses all together).

When I run a report to pull all customer details who has a KEY2 status of DCW this runs in seconds. When I add a 2nd bit of logic to the select statement to detect WHEN the key2 status changed to DCW this is when things go slow. Each time the KEY2 status is change, we also record what date it was changed. So for example, I might need to run a report which picks up all customers records which were changed to DCW status yesterday.

This is the select statement.
{CONTACT1.KEY2} = "REJ" and
{LogDistinct.WhatTimeIsNow} = today -1

Simply put, Im after a way of running these reports quickly rather than waiting the hours in takes to run currently, maybe you guys have a clever way of doing this?

Thanks
daiwhyte