0

My client wants a pivot table, showing the performance of each month (column headers) per department (row headers). It has to be be possible to insert a 'as-of date' as a parameter, so the user (PHP) can pass that date and the pivot only shows months after that date. My first thought was to write a function. But the pivot has to show a "Totals" column (and a "Totals" row, and a grand total as well). So I wrote a stored procedure, which dynamically puts the pivot together.

The proc works fine, but takes too long to process (which is unsurprising given it's dynamic nature). So I figured I should base an mview on it, or as Microsoft calls it, an indexed view. My approach is to first create a view based on the proc, and then figure out how to materialize it.

It seems that for the first step I need to call the proc inside my view using openquery. That only works if data access is enabled though. So I ran:

SELECT 
  name,
  is_data_access_enabled 
FROM sys.servers;

and it turns out is_data_access_enabled = FALSE on our local server (A), but it is TRUE on the another server we use (B). Oddly I can use openquery on B referring to A, something I don't understand but which is probably irrelevant to my question.

I know it's folly (or at least bad practice) to use openquery on server A referring to that same server A, so that's how I got to the point where I ask the community (you people). Would you know a better approach for achieving what I'm trying to do? I use SQL Server 2014.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Paul de Roos
  • 47
  • 5
  • 2
    *"The proc works fine, but takes too long to process (which is unsurprising given it's dynamic nature)."* Dynamic statements can be fast too; just because it's dynamic doesn't inherently mean it's slow. – Thom A Aug 20 '21 at 08:45
  • 3
    If, however, you want an indexed view, you can't use `OPENQUERY` in the `VIEW`'s definition. This is stated in the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15#additional-requirements). – Thom A Aug 20 '21 at 08:47
  • 1
    What you are asking for cannot be done: indexed views cannot be dynamic, for obvious reasons. That the dynamic procedure is slow is most likely due to indexing and/or the way it is written, not the fact that it is dynamic per se. But without any code, table and index definitions and query plans, cannot advise – Charlieface Aug 20 '21 at 11:09
  • A view, like a table, has a fixed set of columns. If your procedure requires the use of dynamic sql, then presumably it also is generating a dynamic pivot which returns both a variable number of columns and a set of columns whose names vary based on the input. So that rules out the use of view (materialized or not). Nor can a view be parameterized. And I doubt your client wants a pivot **table** but rather desires information presented in that arrangement. Most report tools can do this more easily. Some dev environments have controls to do this as well. – SMor Aug 20 '21 at 11:11
  • When you have a performance issue you don't go straight to a materialised view, you optimise what you have – Nick.Mc Aug 20 '21 at 12:39

0 Answers0