0

I have a BI admin that created a tool-generated query from Tableau, connecting to a custom database application that tracks issue reports for follow-up action. I can make almost any changes to the database, though I'm not the owner.

The requirement we're having trouble with is that certain "admin users" need to be able to see ALL database items, while "normal users" should only see items that pertain to them (that they reported or they're responsible for resolving). The BI admin handles this with a Cross Join to the "Groups" table, filtering for the Admins key - and this takes the result set from 94K rows to 2.4M rows, and the query response time from 12s to 5m12s.

Obviously Cross Join is to be avoided wherever possible, but I'm not familiar enough with Tableau or other reporting suites to suggest an alternative way to handle admin access vs normal user access. What's the proper way to do something like this?

Edit: After reading initial comments and looking into this closer with the BI guy, the query Tableau generated is very poor and could use significant cleanup. The database has a total of ~3900 rows in all tables, so even the 94k means a significant amount of crossing and duplication is already occurring from one-to-many relationships and Left Join.

I simplified the query a bit, removing about half the columns in the SELECT portion.

  SELECT [AreaAccess].[ID] AS [ID (AreaAccess)],
  [AreaAccess].[FunctionalArea] AS [FunctionalArea],
  [AreaAccess].[EmplId] AS [EmplId],
  [CEO_OR].[ID] AS [ID (CEO_OR)],
  [CEO_OR].[Project_ID] AS [Project_ID],
  [CEO_OR].[Title] AS [Title],
  [CEO_OR].[Materials] AS [Materials],
  [CEO_OR].[Four_Blocker] AS [CEO_OR_FourBlocker],
  [MemberAccess].[ID] AS [ID (MemberAccess)],
  [MemberAccess].[Project_ID] AS [Project_ID (MemberAccess)],
  [MemberAccess].[Milestone_ID] AS [Milestone_ID],
  [MemberAccess].[EmplID] AS [EmplID (MemberAccess)],
  [Metrics].[ID] AS [ID (Metrics)],
  [Metrics].[Project_ID] AS [Project_ID (Metrics)],
  [Metrics].[Metric_Name] AS [Metric_Name],
  [Metrics].[Metric_Value] AS [Metric_Value],
  [Milestones].[ID] AS [ID (Milestones)],
  [Milestones].[Project_ID] AS [Project_ID (Milestones)],
  [Milestones].[Name] AS [Name],
  [Milestones].[Start_Date] AS [Start_Date (Milestones)],
  [Milestones].[End_Date] AS [End_Date],
  [Milestones].[Status] AS [Status (Milestones)],
  [NextSteps].[ID] AS [ID (NextSteps)],
  [NextSteps].[Project_ID] AS [Project_ID (NextSteps)],
  [NextSteps].[NextSteps] AS [NextSteps],
  [NextSteps].[Comments] AS [Comments (NextSteps)],
  [NextSteps].[Four_Blocker] AS [NextStepsFour_Blocker],
  [Projects].[ID] AS [ID],
  [Projects].[Project_Name] AS [Project_Name],
  [Projects].[Priority] AS [Priority],
  [Projects].[Start_Date] AS [Start_Date],
  [Projects].[Completion_Date] AS [Completion_Date],
  [Projects].[LastUpdated] AS [LastUpdated],
  [Status].[ID] AS [ID (Status)],
  [Status].[Project_ID] AS [Project_ID (Status)],
  [Status].[Accomplishment] AS [Accomplishment],
  [Status].[Comments] AS [Comments (Status)],
  [Status].[Four_Blocker] AS [StatusFour_Blocker],
  [Groups].[ID] AS [GroupID],
  [Groups].[EmplId] AS [GroupAdminEmplId],
  [Groups].[Type] AS [GroupAdminType],
  [Actions].[Project_ID] AS [A_Project_ID],
  [Actions].[Name] AS [A_Name],
  [Actions].[Owner] AS [A_Owner],
  IIF ([Projects].[Operating_Rhythm] = 0,NULL,'OpRhythm') AS OpRythmTrue,
  IIF ([Projects].[Frequency] IS NOT NULL,'Frequency',NULL) AS FrequencyTrue,
  IIF ([Projects].[Priority] IS NOT NULL,'Priorities',NULL) AS PrioritiesTrue,
  IIF ([Milestones].[Status] = 0,NULL,'MilestoneStatus') AS MilestoneStatusTrue,
  IIF ([Actions].[Status] = 0,NULL,'ActionStatus') AS ActionStatusTrue,
  [Projects].[Scope] AS [Scope]

FROM ([dbo].[Projects] [Projects]
  LEFT JOIN [dbo].[AreaAccess] [AreaAccess] ON ([Projects].[Function_Area] = [AreaAccess].[FunctionalArea])
  LEFT JOIN [dbo].[CEO_OR] [CEO_OR] ON ([Projects].[ID] = [CEO_OR].[Project_ID])
  LEFT JOIN [dbo].[Metrics] [Metrics] ON ([Projects].[ID] = [Metrics].[Project_ID])
  LEFT JOIN [dbo].[Milestones] [Milestones] ON ([Projects].[ID] = [Milestones].[Project_ID])
  LEFT JOIN [dbo].[MemberAccess] [MemberAccess] ON (([Projects].[ID] = [MemberAccess].[Project_ID]) AND ([Milestones].[ID] = [MemberAccess].[Milestone_ID]))
  LEFT JOIN [dbo].[NextSteps] [NextSteps] ON ([Projects].[ID] = [NextSteps].[Project_ID])
  LEFT JOIN [dbo].[Status] [Status] ON ([Projects].[ID] = [Status].[Project_ID])
  LEFT JOIN [dbo].[Actions] [Actions] ON ([Projects].[ID] = [Actions].[Project_ID]))
  CROSS JOIN [dbo].[Groups] [Groups] WHERE [Groups].[TYPE] IN (1,2)

The Groups table is very simple, just containing ID, EmplId, Name, Type (types 1 and 2 are admins)

Improve this question
4
  • It might be helpful if you could add a simplified query to the question that demonstrates the problem. The table structure for the Groups table could help as well.
    – Joe Obbish
    Apr 17, 2017 at 22:17
  • Why a cross join to the groups? Seems like it should be an inner join and then you can just use USERNAME() from Tableau to limit the results returned to the application. i.e. your query select... from table inner join groups... where groupPersonUname = @userSentFromTableauLogin or @userSentFromTableauLogin in ('admin1', 'admin2', etc...) The second part of the where would return all items for the admins. Here is a good rundown but you just need to code in the admins.
    – S3S
    Apr 18, 2017 at 15:45
  • While the query posted does provide some insight, we don't really have enough there to try to improve it. It does seem likely to produce a certain amount of redundancy. Several tables (Milestones, NextSteps, Actions) seem likely to have more than one matching row for each Projects row. If so, then within each project, you're getting a cross-product of the relevant rows for those tables. Groups doesn't attach to anything, so its not at all clear how that would be used to limit anything....
    – RDFozz
    Apr 18, 2017 at 20:36
  • ...(cont) - That said, if the result set is being used in some sort of report, then the report is presumably designed to consume this result set. Trying to remove redundant data from the result set could break the report. For instance, if the report explicitly de-dupes the data in the NextSteps columns, in the Milestones columns, etc., before displaying it, then the report's creator has come up with a (rather kludgy) mechanism to store multiple logical resultsets in a single actual resultset. Significant changes to the query must be tied to changes to the report.
    – RDFozz
    Apr 18, 2017 at 20:42

1 Answer 1

Reset to default
0

One option may be to run one query to determine if the user should get the full results (admin), or results filtered by their user account data. Once you know that, you might actually have two distinct queries; one guesses that a query that will always bring back the full results might perform better than one that sometimes brings back the full results, and sometimes brings back a limited subset. This would eliminate the possibility of a "parameter-sniffing" issue, where the query plan generated for the query for an non-admin user doesn't work very well for admin users.

(Bandwidth - I had originally suggested that it might be useful to compare the amount of time it takes to bring back to the 2.4 million rows from this query vs 2.4 million rows from a simple query that shouldn't require any significant work to build (for instance, SELECT * FROM sys.objects o1 CROSS JOIN sys.objects o2). This was to see if the time the query took to respond was due simply to the amount of data being returned, or due to the complexity of the query. However, some basic math answers that. 100K rows took 12 seconds to return. 24*100K rows took 312 seconds to return. 12 * 24 = 288 seconds, close enough to the actual time. The increase in time seems explained by the additional rows being returned)

Improve this answer
1
  • The time it takes (and number of resulting rows) has to be a result of the joins and logic in this particular query; I checked the entire database and it has a total of 13 tables with ~4,000 rows; selecting all rows only takes about 2 seconds. I'll add a query sample to my question.
    – Fredric Shope
    Apr 18, 2017 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.