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

The other answer...

SELECT snapshot_isolation_state_desc, is_read_committed_snapshot_on  
FROM   sys.databases  
WHERE name = 'Kelso-Powerapps'  

Results:

Answer 2

@Erland Sommarskog ,

I have some more information to consider. I have discovered that I can query this VIEW, or a TABLE with the exact same data. Either one is about the exact same speed when I do a simple query, as what PowerApps is doing. Considering this query:

 select top 1  
     [_].[DocumentID],  
     [_].[DwgRevision],  
     [_].[Description],  
     [_].[ProjectName],  
     [_].[PartNumber],  
     [_].[CurrentStatusID],  
     [_].[Pending],  
     [_].[LatestRevisionNo],  
     [_].[Filename],  
     [_].[Version],  
     [_].[Material],  
     [_].[RvTbl_Description],  
     [_].[RvTbl_DwgDate],  
     [_].[RvTbl_Approved],  
     [_].[RvTbl_Revision],  
     [_].[TransitionID],  
     [_].[ProjectID]  
 from [dbo].[VIEW_ALL_PDM_LATEST] as [_]  
 where [_].[PartNumber] = '501029X' and [_].[PartNumber] is not null  

It takes 8 seconds to run in SSMS. However, if I search for DocumentID... the result is almost instant!! Either in the VIEW, or the TABLE. THe difference? It may not be a surprise for you, because you know what you are doing. But, DocumentID is an int, and PartNumber is a nvarchar(max). So, I can remove the VIEW aspect of this entirely, and still have the problem. From this, I have two questions.

1. What is the expectation of what would be a measurable "congestion" for this if its just a simple table? In my mind.. SQL is designed to serve a very large concurrent user base. So, I would hope that it would work without a huge degraded performance situation, even if it had say 20, 50, or even 100 concurrent hits. Is that not what SQL is designed for? Large datasets with lots of users and concurrent hits? Also note, I did not index anything in the TABLE version of this dataset either.
2. Is there a better way to query for string variables? In my case, I am searching for an exact match. Not a partial match. So, when I want '501029X', that exactly what I want. In every case. I dont konw that I can change the type in a VIEW, but is there a better type for performance for a string variable? It does make sense to me that integers are much faster... but not by that much. Comparitively speaking. SSMS reports '0' seconds for the DocumentID search result, and '8' seconds for the PartNumber result.

So, at this point, I would like to ignore the entire VIEW part of this discussion, because it seems this has been eliminated as a root cause of the performance issues.

ANything else you want me to try for more information??

Thanks so much for help thus far.

Rod

Answer 3

@Jeffrey Williams ,

I can do that for my table. But how to change the type of my existing view? Thats the issue. Also, i cant key it. As far as i know. I havent been given a method that works. I get an err about the view not being... uhh. I forget the term now. But its earlier in this thread.

The partnumber field will never be more than 16 characters. But how can i change that? Same issue. Pkease peovide method, and ill gladly try it.

Properties of all columns in my view say indexible 'no'.

Thank you!