title | description | ms.date | ms.reviewer | ms.topic | author | ms.subservice | ms.author | search.audienceType | contributors | ||
---|---|---|---|---|---|---|---|---|---|---|---|
Optimize performance using QueryExpression |
Learn how to optimize performance when you retrieve data from Microsoft Dataverse using QueryExpression. |
05/12/2024 |
jdaly |
how-to |
pnghub |
dataverse-developer |
gned |
|
|
This article describes ways you can optimize performance when retrieving data using QueryExpression.
[!INCLUDE cc-query-antipatterns]
Important
Only apply these options when recommended by Microsoft technical support. Incorrect use of these options can damage the performance of a query.
Microsoft SQL Server supports many query hints to optimize queries. QueryExpression supports query hints and can pass these query options to SQL Server using the QueryExpression.QueryHints property.
[!INCLUDE cc-query-options]
In earlier versions, the QueryExpression.NoLock property used to prevent shared locks on records. It's no longer necessary to include this property
You can improve performance when adding a FilterExpression that sets the ConditionExpression
for columns in different tables by setting the FilterExpression.FilterHint property to union
. But there are some restrictions:
- The FilterExpression.FilterOperator must use LogicalOperator
.Or
. - Each query can contain only one
union
hint. - If a
FilterExpression
withunion
hint isn't at top level filter, Dataverse transforms the query and move the filter with aunion
hint to root filter. - If a
union
hint is more than three levels deep, it's ignored.
The following example sets a filter with the union
hint on the telephone1
column for both the account and contact tables.
QueryExpression query = new("email")
{
ColumnSet = new ColumnSet("activityid", "subject"),
Criteria = new FilterExpression(LogicalOperator.And)
{
Conditions = {
{
new ConditionExpression(
attributeName:"subject",
conditionOperator:ConditionOperator.Like,
value: "Alert:%")
},
{
new ConditionExpression(
attributeName:"statecode",
conditionOperator:ConditionOperator.Equal,
value: 0)
}
},
Filters = {
{
new FilterExpression(LogicalOperator.Or){
FilterHint = "union",
Conditions = {
{
new ConditionExpression(
attributeName:"telephone1",
conditionOperator:ConditionOperator.Equal,
value: "555-123-4567"){
EntityName = "ac"
}
},
{
new ConditionExpression(
attributeName:"telephone1",
conditionOperator:ConditionOperator.Equal,
value: "555-123-4567"){
EntityName = "co"
}
}
}
}
}
}
}
};
LinkEntity linkToAccount = query.AddLink(
linkToEntityName: "account",
linkFromAttributeName: "regardingobjectid",
linkToAttributeName: "accountid",
joinOperator: JoinOperator.LeftOuter);
linkToAccount.EntityAlias = "ac";
LinkEntity linkToContact = query.AddLink(
linkToEntityName: "contact",
linkFromAttributeName: "regardingobjectid",
linkToAttributeName: "contactid",
joinOperator: JoinOperator.LeftOuter);
linkToContact.EntityAlias = "co";
Query data using QueryExpression
Select columns using QueryExpression
Join tables using QueryExpression
Order rows using QueryExpression
Filter rows using QueryExpression
Page results using QueryExpression
Aggregate data using QueryExpression
Count rows using QueryExpression
[!INCLUDE footer-banner]