How to create a table from a VIEW and update every 15min

Question

I have a very complex view that takes quite a bit to crunch. The data does not update super frequent. However, I make multiple calls to the view. It seems that every call I make from Powerapps, it will regenerate the view every time. I think this is desired, but in this case, I'm making many calls from a gallery, which multiplies the number of calls to the VIEW.

I think I want to make a table that will be the exact output of the VIEW, and update it every 15min. This way, when calls are made to this table, it does not have to generate the view every time. I could have a button in the app to force an update, if the user desires to not wait for the 15min batch.

I'm thinking there is a quick way to do this? Something I can just keeps running, and updates this every 15min for eternity?

Thanks!

Rod

Answer 1

Maybe create an “Indexed View”, because “the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution” [https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views].

Therefore, if CREATE UNIQUE CLUSTERED INDEX… works with your view, then the view will behave like a materialised (persistent) view [https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd171921(v=sql.100)].

Answer 2

Viorel mentioned indexed views, but there are a lot of limitations of what you can put in an indexed view. Also, depending on your edition you may need to add a hint when you query the view.

An alternative is, as you say, to save the result in a table, and schedule this.

Before we go into more details, we need to know where you are working. Can you post the output of "SELECT @@version".

Answer 3

Hi @Rod Martin ,

We can use sp_helptext system stored procedure to get VIEW definition. It returns the complete definition of a SQL VIEW.

A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the database.

Please refer to the blog SQL View – A complete introduction and walk-through to better understood SQL server views.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 4

@CathyJi-MSFT , @Viorel , @Jeffrey Williams ,

Here is my VIEW definition. This has been working great with Powerapps and SQL 2014 Std for quite some time. With the upgrade to SQL 2019 Std and SP1 CU12, it has still worked, but it takes several minutes to update a table in Powerapps. No eerors, but SQL on the server is pegged the CPU, and memory usage shoots to the several gigabytes for the task. View results look appropriate when looking in SSMS (Full results in just over a minute). Not sure what to make of it. It still takes the same length of time to execute in SSMS too. Its just when there are several queries to the table, it acts as if the VIEW regenerates with every poll. I check the execution log, and the more polls made to the VIEW, the longer the response time. If you hit it once, its fine, but if a gallery hits the VIEW say 20 times, I saw some instances taking over 800seconds to respond. My theory is if this were simply a table of data, not a VIEW, that it would be much quicker. So, I wanted to try pushing the VIEW to a table every 5-10 minutes, then connecting to this table for Powerapps.

Here is the definition.

WITH cte  
     AS (SELECT *  
         FROM   dbo.variablevalue  
         WHERE  ( 2 = CASE  
                        WHEN ( variableid = 73  
                                OR variableid = 74  
                                OR variableid = 75  
                                OR variableid = 76 )  
                             AND configurationid = 2 THEN 2  
                        WHEN NOT ( variableid = 73  
                                    OR variableid = 74  
                                    OR variableid = 75  
                                    OR variableid = 76 ) THEN 2  
                      END )),  
     all_combined  
     AS (SELECT TOP (100) PERCENT documentid,  
                                  [49] AS [DwgRevision],  
                                  [47] AS [Description],  
                                  [45] AS [ProjectName],  
                                  [54] AS [PartNumber],  
                                  currentstatusid,  
                                  pending,  
                                  latestrevisionno,  
                                  filename,  
                                  [57] AS [Version],  
                                  [56] AS [Material],  
                                  [73] AS [RvTbl_Description],  
                                  [76] AS [RvTbl_DwgDate],  
                                  [75] AS [RvTbl_Approved],  
                                  [74] AS [RvTbl_Revision],  
                                  transitionid  
         FROM   (SELECT documentid,  
                        variableid,  
                        textoutput,  
                        currentstatusid,  
                        filename,  
                        transitionid,  
                        latestrevisionno,  
                        CASE  
                          WHEN currentstatusid <> 10 THEN 1  
                          ELSE 0  
                        END AS Pending  
                 FROM   (SELECT cte.documentid,  
                                cte.variableid,  
                                documents.currentstatusid,  
                                dbo.transitionhistory.transitionid,  
                                documents.filename,  
                                documents.latestrevisionno,  
                                cte.configurationid,  
                                cte.revisionno,  
                                cte.valuetext  
                                        AS TextOutput,  
                                Row_number()  
                                  OVER (  
                                    partition BY cte.documentid, cte.variableid  
                                    ORDER BY cte.revisionno DESC,  
                                  dbo.transitionhistory.transitionnr  
                                  DESC) AS  
                                Seq  
                         FROM   dbo.documents  
                                INNER JOIN cte  
                                        ON dbo.documents.documentid =  
                                           cte.documentid  
                                INNER JOIN dbo.transitionhistory  
                                        ON dbo.documents.documentid =  
                                           dbo.transitionhistory.documentid  
                         WHERE  ( dbo.documents.deleted = 0 )  
                                AND ( dbo.documents.extensionid = 3 )  
                                AND ( dbo.documents.currentstatusid <> 0 )  
                                AND cte.variableid IN ( 57, 56, 54, 49,  
                                                        47, 45, 73, 76,  
                                                        75, 74 )) t  
                 WHERE  seq = 1) doc  
                PIVOT (Max(textoutput)  
                      FOR variableid IN ([57],  
                                         [56],  
                                         [54],  
                                         [49],  
                                         [47],  
                                         [45],  
                                         [73],  
                                         [76],  
                                         [75],  
                                         [74])) AS pvt  
         WHERE  [54] <> 'NULL')  
SELECT all_combined.*,  
       dbo.documentsinprojects.projectid  
FROM   dbo.documentsinprojects  
       INNER JOIN all_combined  
               ON dbo.documentsinprojects.documentid = all_combined.documentid   

Answer 5

@Jeffrey Williams ,

Thanks for the reply. I think there is something we can start with here. First of all, the fact that the cardinality estimater changed doesnt give me much of a hint on how to change things. I dont know what it is, let alone how its change would affect my execution. So, I put my trust on experts like yourself, since I am a rank amateur on this stuff.

So, first thing. The TOP 100 PERCENT statement. I get that. I have heard that a lot. I take that out of the query and it doesnt seem to change the execution time. I would have taken it out long ago, but I use SMSS View Designer never let me save it. I made this in SMSS 2014, and I would always get the error when I removed it. The VIEW would run fine in Designer, but it never allowed me to save it with it removed. I will attempt in the latest install of SMSS 2018 and report.

Ok, this CASE statement. Lets see what we can do with this. You mention it should be rewritten. Can you give some hints on how to rewrite it? I dont know other than changing it how exactly to go about it. I know its not the right thing to just change it, but in what way will help this move along quicker?

CASE  
                         WHEN ( variableid = 73  
                                 OR variableid = 74  
                                 OR variableid = 75  
                                 OR variableid = 76 )  
                              AND configurationid = 2 THEN 2  
                         WHEN NOT ( variableid = 73  
                                     OR variableid = 74  
                                     OR variableid = 75  
                                     OR variableid = 76 ) THEN 2  
                       END  

Oh yea, with regards to 'NULL'. This string is physically 'NULL'. Its not just a blank field. The source data cannot be edited. Its output from an off the shelf application.

Thanks so much!