Complex Command Data Source vs Simpler Command Data Source+Table

[Adam_Alsop]

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

[former_member11696]

and the single Command Data Source Crystal SQL is:

select

inv.HostName,

inv.ApplicationTypeName,

inv.InstanceDirectory,

inv.Version,

inv.Status,

inv.IsDMZ,

inv.LastRefreshedDate,

remedy.asset_lifecycle_status,

remedy.status_reason,

remedy.system_role

from vw_MiddlewareInventory inv

Left outer join remedydata remedy

on remedy.ci_name = inv.hostname

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'

Cleary the latter is the cleaner version, but why is there a difference? (And I'm figuring it has something to do with the EXTERNAL join, which I'm not familiar with)

Thanks for any assistance!