-
-
Notifications
You must be signed in to change notification settings - Fork 24
Query Hints
SQL 4 CDS supports query hints and table hints to control how the query is executed, similar to SQL Server. Currently, join hints are not supported.
The only currently supported table hint is NOLOCK. This applies the no-lock option to any generated FetchXML.
SELECT name
FROM account (NOLOCK)generates
<fetch xmlns:generator='MarkMpn.SQL4CDS' no-lock='true'>
<entity name='account'>
<attribute name='name' />
</entity>
</fetch>Attempting to use any other table hint will generate an "Unsupoprted table hint" error.
Any SQL Server query hint can be used, but only the following hints currently have any effect in SQL 4 CDS:
Overrides the maximum number of worker threads used for INSERT UPDATE and DELETE queries defined in the Settings dialog.
Must be set to a value of 1 or greater.
UPDATE contact
SET firstname = 'Mark'
OPTION (MAXDOP 10)This hint has no effect on other query types.
A table spool may be added to cache the results of an inner query used by a nested loop operator. This option disables this and runs the inner query for each record in the outer result set, which may be more efficient if the outer query produces fewer results than the query plan anticipates.
SELECT name,
(SELECT TOP 1 fullname
FROM contact
WHERE parentcustomerid = account.accountid
ORDER BY createdon DESC) AS LatestContact
FROM account
OPTION (NO_PERFORMANCE_SPOOL)This custom SQL 4 CDS hint disables plugins when running an INSERT, UPDATE or DELETE query, and can be used instead of
enabling the option to bypass plugins for all queries in the Settings dialog.
UPDATE contact
SET firstname = 'Mark'
OPTION (USE HINT ('BYPASS_CUSTOM_PLUGIN_EXECUTION'))Dataverse provides a fast method of getting the total number of records in a table using the RetrieveTotalRecordCount message,
but the values returned by this message are cached and therefore don't provide a reliable figure when accuracy is required.
If a table contains a large number of records and a fast but possibly out of date result to a simple SELECT COUNT(*) FROM table
query is required, this hint can be used to activate this optimization.
SELECT COUNT(*)
FROM account
OPTION (USE HINT ('RETRIEVE_TOTAL_RECORD_COUNT'))Generally used for debugging the SQL 4 CDS engine itself and not for normal use, this skips the entire query optimization step and allows the naive query plan to be reviewed.
SELECT COUNT(*)
FROM account
OPTION (USE HINT ('DEBUG_BYPASS_OPTIMIZATION'))Sets the number of records to be retrieved in each page of a FetchXML request. By default the maximum 5,000 records will be retrieved at once, but if a large number of columns are requested this will be reduced automatically to improve responsiveness. If over 100 columns are requested only 1,000 records will be retrieved at once, and if over 500 columns are requested the page size is reduced automatically to 500. This hint can be used to override this to any value between 1 and 5,000.
SELECT name
FROM account
OPTION (USE HINT ('FETCHXML_PAGE_SIZE_2000'))Forces the query to be executed by SQL 4 CDS even if the query optimizer would normally choose to use the TDS Endpoint.
SELECT name
FROM account
OPTION (USE HINT ('FORCE_SQL_4_CDS'))FORCE_SQL4CDS can also be used as a synonym.
Disables the state transition logic which is normally applied to UPDATE queries. This logic normally attempts to apply a
sequence of updates to change a record from one state to another if
restricted state transitions are enabled
for the entity.
For example, if an opportunity record is currently in Lost status and a query attempts to change it to Won, SQL 4 CDS will
execute the following query by applying an initial Update request to change the state to Open, followed by a WinOpportunity
request to change the state to Won:
UPDATE opportunity
SET statecode = 1,
statuscode = 3
WHERE opportunityid = '...'This hint disables this logic and allows the query to be executed as-is, which may be useful if the state transition logic is not required or if the query is being used to update a record to a state which is not normally allowed.
UPDATE opportunity
SET statecode = 1,
statuscode = 3
WHERE opportunityid = '...'
OPTION (USE HINT ('DISABLE_STATE_TRANSITIONS'))Sets the number of records to be updated in each batch when executing an INSERT, UPDATE or DELETE query. By default the
global settings for SQL 4 CDS determine the batch size, but this hint can be used to override this to any value between 1 and
1,000 for an individual query.
UPDATE contact
SET firstname = 'Mark'
OPTION (USE HINT ('BATCH_SIZE_100'))This is an option on unique indexes in SQL Server, but is used as a query hint in SQL 4 CDS to get the same effect. When inserting records, this hint indicates that any duplicate key errors should be silently ignored instead of stopping the batch with an error. For example, you could use this query to copy security roles from one user to another without worrying about whether the new user already has any of those roles:
INSERT INTO systemuserroles (systemuserid, roleid)
SELECT @NewUser,
roleid
FROM systemuserroles
WHERE systemuserid = @OldUser
OPTION (USE HINT ('IGNORE_DUP_KEY'))When updating owner or state fields, this hint forces SQL 4 CDS to generate AssignRequest or SetStateRequest requests
instead of the standard UpdateRequest messages. If other fields are being updated at the same time, those fields will still
use an UpdateRequest. If multiple requests are needed to update all the required fields, the requests for each individual
record will be combined in a transaction.
UPDATE contact
SET ownerid = CURRENT_USER,
statecode = 1
WHERE ...
OPTION (USE HINT ('USE_LEGACY_UPDATE_MESSAGES'))In addition to the hints listed above which affect the query plan generated by SQL 4 CDS, FetchXML also supports a number of query hints itself which can be passed through to the underlying SQL Server database. If any of these hints are used in your query they will automatically be added to the generated FetchXML. See the list of supported query options.