Snippets
Share your snippets!
Do you want to contribute and add your own snippets?
Start sharing your information here!
You don't have an account?
Create it here.
Increase DQL performance using RETURN_TOP and OPTIMIZE_TOP hints
A DQL's performance can be improved by using the RETURN_TOP and OPTIMIZE_TOP keywords if a limited set of results is required. According to the DQL Reference Guide : OPTIMIZE_TOP "On SQL Server and DB2, you can include an integer value (as N) to define how many rows you want returned quickly. On Oracle, the number is ignored. The OPTIMIZE_TOP hint is not available on Sybase. If the query sorts the results or includes the DISTINCT keyword reduces the efficiency of the hint" RETURN_TOP : "If you include RETURN_TOP in a query running against Oracle or Sybase, the returned results are not limited at the database level, but by Content Server itself. Consequently, using RETURN_TOP on Oracle or Sybase results in no database performance gains, but may reduce network traffic."
SELECT * FROM dm_document ENABLE(RETURN_TOP 100, OPTIMIZE_TOP 100)
Find unused TBO modules
Identify dmc_module TBO elements which don't have a referenced Type in the repository. The identified modules can be removed safely.
SELECT * FROM dmc_module WHERE folder('/System/Modules/TBO') AND object_name NOT IN (SELECT name FROM dm_type)
Default a time attribute to the current creation-time
Attributes can have a default value. This default value is set automatically by the content server when the attribute has no value on the initial save of an object. The default value for a time attribute can be set to the time of initial save using this default value behaviour. Note : The default value for a time attribute cannot be set in Composer. When the types are re-deployed, the ALTER TYPE statement must be re-executed
ALTER TYPE my_type MODIFY ("my_time_attribute" (SET DEFAULT = DATE(NOW)))
Limit DQL results
The number of results from a DQL query can be limited by using the RETURN_TOP keyword. Since multiple rows can be returned for one object, the result limit does not always limit the results to a number of objects, but to a number of rows. The database does not know about 'Objects', therefore the result limit of returned objects is implemented in the content server. The database can only limit the result of rows. The way result limitation is executed is controlled by the 'return_results_row_based' parameter in the server.ini file : - return_results_row_based = False -> Returns Objects. - return_results_row_based = True -> Returns Rows. Default = True (Rows)
DQL Hint : enable(RETURN_TOP N) Example : SELECT * FROM dm_document enable(RETURN_TOP 100)
General workflow information
SELECT task_name, task_state, actual_start_date, dequeued_date FROM dmi_queue_item WHERE router_id = ‘workflowId’