server

Branch: renovate/microsoft.data.sqlclient-7.x

Commit Graph

Author	SHA1	Message	Date
Rui Tomé	acd556d56f	[PM-21031] Optimize GET Members endpoint performance (#5907 ) * Add new feature flag for Members Get Endpoint Optimization * Add a new version of OrganizationUser_ReadByOrganizationIdWithClaimedDomains that uses CTE for better performance * Add stored procedure OrganizationUserUserDetails_ReadByOrganizationId_V2 for retrieving user details, group associations, and collection associations by organization ID. * Add the sql migration script to add the new stored procedures * Introduce GetManyDetailsByOrganizationAsync_vNext and GetManyByOrganizationWithClaimedDomainsAsync_vNext in IOrganizationUserRepository to enhance performance by reducing database round trips. * Updated GetOrganizationUsersClaimedStatusQuery to use an optimized query when the feature flag is enabled * Updated OrganizationUserUserDetailsQuery to use optimized queries when the feature flag is enabled * Add integration tests for GetManyDetailsByOrganizationAsync_vNext * Add integration tests for GetManyByOrganizationWithClaimedDomainsAsync_vNext to validate behavior with verified and unverified domains. * Optimize performance by conditionally setting permissions only for Custom user types in OrganizationUserUserDetailsQuery. * Create UserEmailDomainView to extract email domains from users' email addresses * Create stored procedure Organization_ReadByClaimedUserEmailDomain_V2 that uses UserEmailDomainView to fetch Email domains * Add GetByVerifiedUserEmailDomainAsync_vNext method to IOrganizationRepository and its implementations * Refactor OrganizationUser_ReadByOrganizationIdWithClaimedDomains_V2 stored procedure to use UserEmailDomainView for email domain extraction, improving query efficiency and clarity. * Enhance IOrganizationUserRepository with detailed documentation for GetManyDetailsByOrganizationAsync method, clarifying its purpose and performance optimizations. Added remarks for better understanding of its functionality. * Fix missing newline at the end of Organization_ReadByClaimedUserEmailDomain_V2.sql to adhere to coding standards. * Update the database migration script to include UserEmailDomainView * Bumped the date on the migration script * Remove GetByVerifiedUserEmailDomainAsync_vNext method and its stored procedure. * Refactor UserEmailDomainView index creation to check for existence before creation * Update OrganizationUser_ReadByOrganizationIdWithClaimedDomains_V2 to use CTE and add indexes * Remove creation of unique clustered index from UserEmailDomainView and related migration script adjustments * Update indexes and sproc * Fix index name when checking if it already exists * Bump up date on migration script	8 months ago

Author

SHA1

Message

Date

Rui Tomé

acd556d56f

[PM-21031] Optimize GET Members endpoint performance (#5907 )

* Add new feature flag for Members Get Endpoint Optimization

* Add a new version of OrganizationUser_ReadByOrganizationIdWithClaimedDomains that uses CTE for better performance

* Add stored procedure OrganizationUserUserDetails_ReadByOrganizationId_V2 for retrieving user details, group associations, and collection associations by organization ID.

* Add the sql migration script to add the new stored procedures

* Introduce GetManyDetailsByOrganizationAsync_vNext and GetManyByOrganizationWithClaimedDomainsAsync_vNext in IOrganizationUserRepository to enhance performance by reducing database round trips.

* Updated GetOrganizationUsersClaimedStatusQuery to use an optimized query when the feature flag is enabled

* Updated OrganizationUserUserDetailsQuery to use optimized queries when the feature flag is enabled

* Add integration tests for GetManyDetailsByOrganizationAsync_vNext

* Add integration tests for GetManyByOrganizationWithClaimedDomainsAsync_vNext to validate behavior with verified and unverified domains.

* Optimize performance by conditionally setting permissions only for Custom user types in OrganizationUserUserDetailsQuery.

* Create UserEmailDomainView to extract email domains from users' email addresses

* Create stored procedure Organization_ReadByClaimedUserEmailDomain_V2 that uses UserEmailDomainView to fetch Email domains

* Add GetByVerifiedUserEmailDomainAsync_vNext method to IOrganizationRepository and its implementations

* Refactor OrganizationUser_ReadByOrganizationIdWithClaimedDomains_V2 stored procedure to use UserEmailDomainView for email domain extraction, improving query efficiency and clarity.

* Enhance IOrganizationUserRepository with detailed documentation for GetManyDetailsByOrganizationAsync method, clarifying its purpose and performance optimizations. Added remarks for better understanding of its functionality.

* Fix missing newline at the end of Organization_ReadByClaimedUserEmailDomain_V2.sql to adhere to coding standards.

* Update the database migration script to include UserEmailDomainView

* Bumped the date on the migration script

* Remove GetByVerifiedUserEmailDomainAsync_vNext method and its stored procedure.

* Refactor UserEmailDomainView index creation to check for existence before creation

* Update OrganizationUser_ReadByOrganizationIdWithClaimedDomains_V2 to use CTE and add indexes

* Remove creation of unique clustered index from UserEmailDomainView and related migration script adjustments

* Update indexes and sproc

* Fix index name when checking if it already exists

* Bump up date on migration script

8 months ago

1 Commits (renovate/microsoft.data.sqlclient-7.x)