Query MS SQL from Crystal Reports takes a longtime

[cyrun]

We have many complex reports accessing MS SQL and it seems to take more time than when we are accessing the data from our GUI. Any suggestions on optimizing how Crystal processes SQL data.

Thanks,

 

[synapsevampire]

Make sure that the SQL is passed to the database, do so by cusing the Database->Show SQL Query.

Stored procedures will net the best performance, in which case the pass through isn't a concern.

If you're going to use tables or views directly, use the following guide:

Never use a variable in any formula that will be referenced by the record selection criteria.

When using parameters (parms) in the record selection criteria (this example has a 'All' hardcoded into the default selection of the parm, and the parm is a multiple selection, so I code for it):

(if {?Node}[1] <> 'All'
and {?Node}[1] <> ''
then
({MyTable.MyField} in {?Node})
else
if {?Node}[1] = 'All'
or {?Node}[1] = ''
then
true)

This can be overkill, but this format is the most consistent at passing the SQL, I explicitly handle the IF and the ELSE.

When using dates:

(
({MyTable.MyDate} >= currentdate-10)
and
({MyTable.MyDate} <= currentdate-1)
)

Note the parens around each part of the record selction criteria.

Hope this helps.

Post some specifics if you'd like more specific help, as in the record selection criteria and the current SQL being generated.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com