Forum Discussion

jacovg91's avatar
jacovg91
Frequent Visitor
3 years ago

Creating/Updating/Deleting roles through XMLA extremely slow

Hello,

 

I'm trying to run a simple XMLA query to delete a role. For example:

 

 

 

{
  "delete": {
    "object": {
      "database": "Database",
      "role": "RoleName"
    }
  }
}

 

 

However, this is extremely slow. It takes about 2 minutes to delete the role.

 

Also, creating or updating a role takes equally long. How is this possible? Does anyone know a solution?

It used to be much faster on Azure analysis services. 

Edit: I checked and with an empty dataset its much faster. If I have a filled dataset, it is really slow. They are RLS roles with members and table permissions. Is there any way to speed it up? 

 

Thanks in advance! 

 

Need Help

1 Reply

  • jaweher899's avatar
    jaweher899
    Icon for Impactful Individual rankImpactful Individual
    3 years ago

    The slow performance you are experiencing when deleting or updating a role in an Analysis Services database can be caused by a number of factors. Here are a few things you can try to speed up the process:

    1. Reduce the number of members in the role: If your role has a large number of members, deleting or updating the role can take longer. Try reducing the number of members in the role to see if this improves performance.

    2. Optimize the role: If your role has a large number of table permissions, it can also slow down the process. Try optimizing the role by removing any unnecessary table permissions or consolidating them into fewer permissions.

    3. Check for database fragmentation: Over time, the database may become fragmented, which can slow down performance. Check for database fragmentation and defragment the database if necessary.

    4. Use partition switching: If you are deleting a large number of rows from a table, consider using partition switching to remove the rows more quickly. Partition switching can be faster than traditional delete operations.

    5. Optimize the query: If you are using a complex query to delete or update the role, try optimizing the query to improve performance.