Using the TOP N function

Be as specific as possible when querying the server. For example, when using the asterisk operator (*) in a SELECT statement, always ensure that you use the TOP N function and/or the WHERE clause to limit the selection to a small number of objects. If the APS is forced to select all objects, it may cause the system to become overloaded with requests; if it selects all properties for a particular object, it may take up to ten times longer to retrieve the data.

It is recommended that you never use * without TOP N, unless you are retrieving a small number of objects. For example, SELECT * FROM CI_INFOOBJECTS WHERE SI_ID=128 will retrieve only one object.

Note:    

• In Crystal Enterprise, SELECT * returns an upper limit of 1000 objects. You can specifically ask for more than 1000 object with the TOP N function.
• The InfoObjects collection's ResultCount Property contains the number of objects found on the APS that match a particular query. This number can be compared to the actual number of objects returned from the APS.

Example of bad use

Consider the following example where a variety of report details needs to be retrieved from every report in the APS InfoStore:

Select * From CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report'

This query returns all properties for all report objects (with an upper limit of 1000 objects).

Examples of good use

If a large number of reports exist, it will be significantly faster to use the following statement with the TOP N operator attached to the asterisk:

Select TOP 100 * From CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report'

You could also use a statement with TOP N attached to a list of properties:

Select TOP 100 SI_NAME, SI_ID, SI_CHILDREN, SI_DESCRIPTION, SI_HASTHUMBNAIL _

From CI_INFOOBJECTS Where SI_PROGID = 'CrystalEnterprise.Report'

Although this query may seem bulky, to replace the asterisk with the TOP N operator and the desired properties may mean a retrieval time measured in seconds rather than minutes.

If you do use SELECT *, you can use TOP N to limit the number of records that get returned by a query. For example, you can do the following:

Select TOP 20 * From CI_SYSTEMOBJECTS Where SI_PROGID = 'CrystalEnterprise.User' ORDER BY SI_NAME

This will send back the first 20 users in alphabetical order (for example, "Anna" to "Joanna"). You can then retrieve the next 20 users:

Select TOP 20 * From CI_SYSTEMOBJECTS Where SI_PROGID = 'CrystalEnterprise.User' AND SI_NAME > 'Joe' ORDER BY SI_NAME