I answered two questions this week related to commands and thought the answers would make a good blog post.

1) Is it bad to link a command to other tables using the Database Expert?
In general it is more efficient to build all of the tables into the command when possible. This allows the database to optimize the query and tap into the indexes. When you link a command to a table in the Database Expert, Crystal has to request two separate datasets, load the results into memory and then try to match the records on your PC.  This is usually going to slower since you don’t have the power of the server.

But there are cases where this might work acceptably.  For instance, if the results are small and if you are going FROM the command TO an indexed table. If the command is coming from a different server than the tables, then you have no choice but to link them within Crystal.

2) If you use a command and then add a  selection formula in the report, does the criteria get added to the SQL?
No.  In a normal table-based report the selection formula is usually transferred into the WHERE clause.  But in a command-based report the command will return results based on the WHERE clause in the command itself.  Any criteria that you add in the selection formula will be applied as a second step, as Crystal reads the records in from the database.  So it is best to move the criteria from your selection formula into the command’s WHERE clause.

This is also true for parameters that are used as filters.  It is best to add these parameters directly into the command’s WHERE clause.  Otherwise, like the selection criteria mentioned above, they are applied to the data as it is read into Crystal.