We help IT Professionals succeed at work.

Optimise Crystal Reports

daiwhyte
daiwhyte used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GMGeniusSoftware Development Manager/Developer

Commented:
Hi again,

I suspect the LogDistinct is a view?  does this view by chance have an Order By ?
GMGeniusSoftware Development Manager/Developer

Commented:
In Crystal, select Database > Show SQL Query and post it here please

Author

Commented:
LogDistinct is a db with a number of tables in of which whattimeisnow is one of them.

The dw_email is a view which pulls the customer email address.

Here is the script


 SELECT "CONTACT1"."KEY2", "CONTACT1"."KEY5", "CONTACT1"."CREATEON", "CONTACT1"."DEPARTMENT", "CONTACT1"."LASTNAME", "CONTACT1"."CONTACT", "CONTACT1"."SOURCE", "CONTACT1"."TITLE", "CONTACT1"."PHONE1", "CONTACT1"."PHONE2", "CONTACT1"."ADDRESS1", "CONTACT1"."ADDRESS2", "CONTACT1"."CITY", "CONTACT1"."STATE", "CONTACT1"."ZIP", "CONTACT1"."PHONE3", "CONTACT1"."COUNTRY", "dw_email"."Email", "CONTACT1"."ACCOUNTNO"
 FROM   {oj "GoldMine"."dbo"."dw_email" "dw_email" RIGHT OUTER JOIN "GoldMine"."dbo"."CONTACT1" "CONTACT1" ON "dw_email"."AccountKey"="CONTACT1"."ACCOUNTNO"}
 WHERE  "CONTACT1"."KEY2"='DCW'
GMGeniusSoftware Development Manager/Developer

Commented:
This does not show the LogDistinct part, did you include?

Author

Commented:
Odd, although its in the select select statement, its not in the sql query.

{CONTACT1.KEY2} = "DCW" and
{LogDistinct.WhatTimeIsNow} = today

Ive attached a screenshot of the database links.
db-links.jpg
Software Development Manager/Developer
Commented:
That means Crystal will do the work client side maybe.

LogDistinct is a table or view? , try adding an index on the ACCOUNTNO field if its a table

Unfortunately Its awkward to diagnose speed issues like this without being hands on

All I can do is tell you some issues I have had in the past and things to try.

1. On a view , if you use Order By it will drasticly increase the time the query runs, never sort the view.
2. Check the report options and try the "perform grouping on server" and see if this improves.
3. Try running the SQL directly using the SQL managment studio
4. Index columns used in joins (where possible)
5. using Like '%sometext%' will not use indexes and will scan an entire table.

Author

Commented:
Logdistinct is a table. How would I add an index on the ACCOUNTNO field?

Author

Commented:
Ive taken a look in the sql manager at the table in question and can see there appears to be an index for the logdistinct but not a index for the ACCOUNTNO.
pic.jpg

Author

Commented:
Odd, Ive just change the joins and the report runs in about 5 mins.

I reversed the link of the dw_email table (right outer join with =) and reversed the link on the logdistinct (left outer join with =)

do you know for an online resource explanation on how joins works so I can read up?
GMGeniusSoftware Development Manager/Developer

Commented:
Thats good news.

As for explanations, Google for MSSQL Table Joins gives many hits

I would start

http://www.databasejournal.com/features/mssql/article.php/3618061/MS-SQL-Joins---Part-1.htm

Author

Commented:
Thanks GMG, Ive learnt something today at the same time got the reports running quicker.

Thank you.

Author

Commented:
Top man, thank you.