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]

Hello,

I broke your post into 2 parts, anything over 1500 characters and Forum removes formatting. You can check this by clicking on the Preview tab above.

The reason is is because CR is a RELATIONAL database reporting tool. When you use Commands and tables you should have got a pop indicating some features would be disabled etc. You are not relating each data source together with links.

By not using an indexed field the ODBC driver actually does the linking. If you use a third party ODBC test tool you'll likely see the same results, but depends on how you format the SQL. Another issue is if CR can't determine what the WHERE portion of the SQL should be, which also is if the tables are not linked, then CR does the filtering client side or on the second pass.

Bottom line is your database server is much more efficient at collecting data than CR will ever be, it's not the designers job to optimize the SQL. So always do all of your complex SQL generation server side using a stored Procedure and simply send just the resulting data set to CR to use. Then all CR has to do is format it for you.

Thank you

Don

[Adam_Alsop]

Hi Dan!

My apologies on the initial post - thanks for the assist (it was a last-minute post before I left for the day, and I didn't know about the character limit).

I figured it was something like that, but wasn't sure. I try to do as much of the work inside of Crystal to take the load off of my DBAs - I haven't encountered many reports that take very long to run, until this one - and I am totally unfamiliar with the EXTERNAL Join command (have to read up on that). The only pop-ups I got were a warning about multiple starting points not being generally supported (which I also get when using the Command Data Sources just as parameter value generators, with no links to the main tables/views), and one that indicates: More than one datasource or a stored procedure has been used in this report; Please make sure that no SQL Expression is added and no server-side group-by is performed. Nothing about any features being disabled.

[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!