Skip to Content
0
Apr 15, 2010 at 08:53 PM

Complex Command Data Source vs Simpler Command Data Source+Table

29 Views

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