Tips for SPs and Search Definitions

Consider the following when using SPs and search definitions:

Avoid using DISTINCT and ORDER BY, particularly in queries that may generate a very large result set. These require the full results set to be built before any results can be returned and may never return any results before time-out limits are reached.
For Oracle databases, use the /*+FIRST_ROWS*/ optimizer hint to return results as soon as they are available, but not in conjunction with DISTINCT or ORDER BY! This can significantly reduce response times.
Limit the size of the result set using SELECT TOP (for SQL Server) or WHERE rownum < 'n' (for Oracle) in the SQL to avoid runaway queries.