👀 Views: 0 💬 Answers: 1 📅 Created: 2025-09-13

sql accessibility refactoring performance aspnetcore

I've looked through the documentation and I'm still confused about I've been struggling with this for a few days now and could really use some help. While refactoring a legacy codebase for a web application, I've come across some complex SQL queries that are not only difficult to maintain but also seem to hinder accessibility features we’re implementing. The application is built using ASP.NET Core and Entity Framework, and I'm trying to improve both query performance and how data is presented to assistive technologies. I initially tackled the problem by breaking down the queries into smaller, more manageable components. For instance, I had a query that fetched user data along with roles and permissions, which looked something like this: ```sql SELECT u.Id, u.Name, r.RoleName, p.PermissionName FROM Users u JOIN UserRoles ur ON u.Id = ur.UserId JOIN Roles r ON ur.RoleId = r.Id JOIN RolePermissions rp ON r.Id = rp.RoleId JOIN Permissions p ON rp.PermissionId = p.Id WHERE u.IsActive = 1; ``` This query worked but was returning a large dataset that included unnecessary information, making it harder for screen readers to interpret. To enhance accessibility, I decided to filter the results further based on user context and privilege levels. I also considered using Common Table Expressions (CTEs) to simplify the joins and make the structure clearer: ```sql WITH ActiveUsers AS ( SELECT u.Id, u.Name FROM Users u WHERE u.IsActive = 1 ), UserRoles AS ( SELECT ur.UserId, r.RoleName FROM UserRoles ur JOIN Roles r ON ur.RoleId = r.Id ) SELECT au.Id, au.Name, ur.RoleName, p.PermissionName FROM ActiveUsers au JOIN UserRoles ur ON au.Id = ur.UserId JOIN RolePermissions rp ON ur.RoleName = rp.RoleId JOIN Permissions p ON rp.PermissionId = p.Id; ``` While this approach improved readability, I noticed a performance drop due to the increased complexity of the joins. After profiling the application, I realized that the number of records returned was still too high for the current pagination method. To tackle the performance issue, I tried implementing pagination directly in SQL to limit the number of rows returned: ```sql SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY u.Name) AS RowNum, u.Id, u.Name, r.RoleName FROM Users u JOIN UserRoles ur ON u.Id = ur.UserId JOIN Roles r ON ur.RoleId = r.Id WHERE u.IsActive = 1 ) AS Result WHERE RowNum BETWEEN @StartRow AND @EndRow; ``` Using this method significantly reduced the load on the application, but I'm still concerned about the clarity of the data for users utilizing assistive technologies. The final step I’m contemplating is to add more descriptive labels and ARIA attributes in the front end for the data presented from these queries. Has anyone else faced similar challenges with legacy SQL refactoring for accessibility? What strategies did you find effective in balancing both performance and clarity in data presentation? My development environment is Linux. Has anyone else encountered this? My development environment is macOS. Any ideas what could be causing this?