Crystal Report Performance SLOW

I am not doing any grouping which is probably why the Database -> perform grouping on server option is greyed out.

My command object is pretty simple and only returns about 5 records.  Would it still help to turn that into an SP?  Should I convert my SQL Views into SPs also?  Is that better for performance reasons?

I consider Crystal a fairly robust product and I don't think there is so much formatting that it would have this affect, but maybe I am wrong.  If this helps, here is the result of the Database -> Show SQL query option attached.

There a few things that have a bearing on report performance. A big one is TABLE INDEXING..make sure ALL tabel are indexed correctly.

Also, from what I can see you are using TEXT in your WHERE clause..here:

WHERE  "ca_resource_class"."name"=N'ITIL Service' AND "actbool"."sym"=N'Active'

IF you have ID's for these to use instead of TEXT ..that should helpout tremendously.

One thing that is strange to me is the 2 seconds runtime vs 5 minutes.....this leads me to believe that you have a DATASOURCE configuration issue. How is your datasource setup / configured on your workstation? This actually may be the whole issue....what type of datasource are you using..?

START >> CONTROL PANEL >> ADMINISTRATIVE TOOLS >> DATASOURCES (ODBC)....

The text in the query is the result of the two parameter selections.  The first (ITIL Service) is from the command I have and the second is from a static parameter with two options (Active and Inactive).  I don't want to show the id to the person running the report so that is why the names are used.  

I have been using this datasource forever and don't have this problem with other reports.  I am using an ODBC connection to a SQL 2005 database via Windows authentication.

Are you using Crystal Reports SERVER?

Tell me about the LOV's and the VIEWs......I guess we've narrowed it down to them...

The first dynamic LOV is using the following command and returns 7 rows.  I do not have the command object linked to any of my other tables/views:
select name from ca_resource_class where family_id=602 AND inactive=0

The second is a static parameter that presents two selections: Active/Inactive
Be back about the views - wanted to let you look these over first.

Can you try to COPY your reoprt into a TEST report and then use this SQL in the TEST report and tell me how it acts? Of course using the same params....lovs..etc..etc..

 SELECT "ca_owned_resource"."resource_name", "ca_resource_class"."name", "actbool"."sym", "ca_owned_resource"."dns_name", "ca_owned_resource_1"."host_name"
, "ca_resource_family"."name", "ca_contact"."first_name", "ca_contact"."last_name", "zView_CI_PMs_CORS"."r_attr", "zView_CI_PMs_CORS"."first_name"
, "zView_CI_PMs_CORS"."last_name", "zView_CI_PMs_CORS"."contact_uuid", "ca_owned_resource"."resource_contact_uuid", "ca_owned_resource"."resource_description"
, "zView_CI_ORG_ENV"."r_attr", "zView_CI_ORG_ENV"."org_name"
 FROM   ((((((("mdb"."dbo"."ca_owned_resource" "ca_owned_resource" 
    INNER JOIN "mdb"."dbo"."ca_resource_class" "ca_resource_class" 
                ON "ca_owned_resource"."resource_class"="ca_resource_class"."id") 
    INNER JOIN "mdb"."dbo"."ca_resource_family" "ca_resource_family" 
                ON "ca_owned_resource"."resource_family"="ca_resource_family"."id") 
   INNER JOIN "mdb"."dbo"."actbool" "actbool" 
                ON "ca_owned_resource"."inactive"="actbool"."enum") 
   LEFT OUTER JOIN "mdb"."dbo"."busmgt" "busmgt" 
                ON "ca_owned_resource"."own_resource_uuid"="busmgt"."hier_child") 
   LEFT OUTER JOIN "mdb"."dbo"."ca_contact" "ca_contact" 
                ON "ca_owned_resource"."resource_contact_uuid"="ca_contact"."contact_uuid") 
   LEFT OUTER JOIN "mdb"."dbo"."zView_CI_PMs_CORS" "zView_CI_PMs_CORS" 
                ON "ca_owned_resource"."own_resource_uuid"="zView_CI_PMs_CORS"."own_resource_uuid") 
   LEFT OUTER JOIN "mdb"."dbo"."zView_CI_ORG_ENV" "zView_CI_ORG_ENV" 
                ON "ca_owned_resource"."own_resource_uuid"="zView_CI_ORG_ENV"."own_resource_uuid") 
   LEFT OUTER JOIN "mdb"."dbo"."ca_owned_resource" "ca_owned_resource_1" 
                ON "busmgt"."hier_parent"="ca_owned_resource_1"."own_resource_uuid"
 WHERE  "ca_resource_class"."name"=N'ITIL Service' AND "actbool"."sym"=N'Active'
 GROUP BY "ca_owned_resource"."resource_name", "ca_resource_class"."name", "actbool"."sym", "ca_owned_resource"."dns_name", "ca_owned_resource_1"."host_name"
, "ca_resource_family"."name", "ca_contact"."first_name", "ca_contact"."last_name", "zView_CI_PMs_CORS"."r_attr", "zView_CI_PMs_CORS"."first_name"
, "zView_CI_PMs_CORS"."last_name", "zView_CI_PMs_CORS"."contact_uuid", "ca_owned_resource"."resource_contact_uuid", "ca_owned_resource"."resource_description"
, "zView_CI_ORG_ENV"."r_attr", "zView_CI_ORG_ENV"."org_name"
 ORDER BY "ca_owned_resource"."resource_name"

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

How do I replace the current SQL command with yours?

DATABASE EXPERT >> RIGHTCLICK ON  COMMAND to EDIT IT...
THEN copy paste my SQL there...I thought you said you where using a COMMAND....if you are then this is how you copy/paste it.

If you are using TABLES and linking them....then you may need to test in a TOTALLY new report...configured as much like your original as possible. May be too much trouble to test if you aren't using a command...

I am using a command just for the dynamic LOV that returns 7 rows.  The rest are tables and views.  I attached a .jpg of the tables, views, and the command links above.

I created a new report with just one command object using your syntax.  It was quite a bit faster, but I am not sure how to account for the LOVs since the selection is actually in the command object.

You don't need to convert all of your Views and what not, but if it's only taking 2 seconds or so on SQL you can call the views from a stored procedure.

The differences can be found in this article:
http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

I ended up creating one big command based on information from CRXIuser2005 and my report now runs in about 9 seconds.  MUCH improved.  I'm still not sure what the original reason was for the slow response, but using this method has solved the issue.  Also, thanks for the great article ged325!!