I came across an interesting situation today. I was using a Command Data Source for part of my data (as it was limited to only a couple of fields with a link Where condition) and the other part was a table (SQL Server 2005, no index on the joined field).
This returned the correct data, but VERY slow (~4 records/sec). However, when I gelled it all down to a single Command Data Source (with two left outer joins, one to the original conditional table, and the other to the originally linked table), it ran in <10secs.
I don't understand why there would be a difference.
In SQL there should not be a performance difference between using * and specifying each field individually, so why would there be a difference in this situation?
It would seem that Crystal is adding an awful lot of "overhead" to the query when it uses its own join - the Crystal version of the SQL was (where SQL UAT Reporting DB SQL Native is the name of the ODBC connection):
SQL UAT Reporting DB SQL Native
select *
from vw_MiddlewareInventoryWithPatches inv
left outer join websphereinstanceinfo websphere
on inv.instancekey = websphere.instancekey
where (inv.applicationtypename = 'WebSphere' and ((websphere.servername <> 'not found' and websphere.servername is not null)
or inv.isdmz =1))
or applicationtypename <> 'WebSphere'
EXTERNAL JOIN MiddlewareInventoryCommand.HostName={?SQL UAT Reporting DB SQL Native: RemedyCleanView.CI_NAME}
SQL UAT Reporting DB SQL Native
SELECT "RemedyCleanView"."ASSET_LIFECYCLE_STATUS", "RemedyCleanView"."STATUS_REASON", "RemedyCleanView"."SYSTEM_ROLE", "RemedyCleanView"."CI_NAME"
FROM "FTransfer"."dbo"."RemedyCleanView" "RemedyCleanView"
WHERE "RemedyCleanView"."CI_NAME"={?SQL UAT Reporting DB SQL Native: MiddlewareInventoryCommand.HostName}
Edited by: Don Williams on Apr 15, 2010 2:38 PM