Create a SQL Table from a View RRS feed

  • Question

  • I know enough to speak some of the language, but probably not enough to be able to describe well what I'm trying to do.  So, please be patient with my illiteracy...  This is what I'm trying to do.

    I have a working SQL system, but the server resources are being strained a bit.  I need to reorganize this a bit, so the server does has to regenerate views less.

    I am using Powerapps, and making MANY calls to SQL from lookups.  

    Assumption:  Every time Powerapps makes a lookup() call to an SQL View, the view has to regenerate on the server.

    Resolution: Lower the number of times SQL has to regenerate the view.

    Method:  When an SQL table changes, can a view be generated to update the data, then dump that view into a new table, which I will then call via powerapps?  The frequence of update to the source SQL tables is FAR less than the number of times powerapps makes lookup() calls.  Thus, if it is calling a table, the SQL server will not have to update a view every time.

    Is this a valid assumption, and a valid method?  A powerapps search can hit the SQL View with 50+ separate lookup calls.  

    Other suggestions?

    Thank you.

    Monday, February 4, 2019 2:00 PM


All replies

  • You can add a trigger on your table to capture DDL changes like addition of column

    Then inside the trigger write logic to  propagate changes to your new table which can then be used by PowerApps

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 4, 2019 3:13 PM
  • I just put together a mini example of what is a view and what is a table. 

    Can you show us the CREATE VIEW code? And ... what is "regenerate the view". 

    -- create a temp table for an example
    create table temp (column1 varchar(5))
    insert into temp (column1) select 'Hello'
    -- create a view for an example
    create view view1 as
        (select column1 from temp)
    -- querying the table directly
    select column1  from temp  
    -- querying the view that is built on the table. in this simple example, even though 
    -- it is querying the view it is identical to querying the table
    select column1  from view1
    -- nothing automatically, that i know of, regenerates a view other than droping and recreating, which
    -- takes no overhead
    drop view view1
    create view view1 as
        (select column1 from temp)
    -- clean up 
    drop view view1
    drop table temp 

    Monday, February 4, 2019 3:41 PM
  • Ok, TheBrenda,

    Thanks for the reply.  I must admit that it a bit above my head.  Perhaps we need to backup a bit, and work on some explanation on how this actually works.

    A single powerapp search may invoke a couple hundred separate Lookup() functions into SQL views.

    How does a view work?  My assumption was that the server assembled the view EVERY time a lookup() is done.  Thus, the server would be building the view data a couple hundred times for one search.  Then, multiply that if we have other users.

    My assumption... is if my view was written as a table, and there were some triggers that updated this table whenever an sql row was added or modified... that would reduce the number of times the view is "regenerated" (my earlier words) by an enormous amount.  It was my impression this might improve performance a bit.  When I do a resource monitor, the dual CPU VM gets cpu resourced maxed for about 15 seconds with just one search.  This same quite of apps has a LOT more to do, so this is just one single function.

    I will show some of my view statements.  This is a very complex nested view.  I cannot control the tables given to me, that's driven by another software.

    I have 7 sub views that are all very similar.  With the way the data is organized, I do not know how to put this all in one view.

    SELECT        TOP (100) PERCENT DocumentID, VariableID, ExtensionID, Description, RevisionNo, CurrentStatusID, Date, TransitionNr, RevNr, LatestRevisionNo, Filename, Deleted
    FROM            (SELECT        dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, dbo.Documents.ExtensionID, dbo.VariableValue.ValueText AS Description, dbo.VariableValue.RevisionNo, dbo.Documents.CurrentStatusID, 
                                                        dbo.TransitionHistory.Date, dbo.TransitionHistory.TransitionNr, dbo.TransitionHistory.RevNr, dbo.Documents.LatestRevisionNo, dbo.Documents.Filename, dbo.Documents.Deleted, ROW_NUMBER() 
                                                        OVER (PARTITION BY dbo.VariableValue.DocumentID
                              ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
    FROM            dbo.Documents INNER JOIN
                             dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.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 (dbo.VariableValue.VariableID = 47)) t
    WHERE        Seq = 1
    ORDER BY DocumentID DESC, RevisionNo DESC, TransitionNr DESC

    Then, all of the "SUB" views are pulled together in this view:

    SELECT        TOP (100) PERCENT dbo.Documents.DocumentID, dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DwgRevision, dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.Description, 
                             dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.ProjectName, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.PartNumber, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.CurrentStatusID, 
                             CASE WHEN dbo.Documents.CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending, dbo.Documents.LatestRevisionNo, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.Filename, 
                             dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.RvTbl_Description, dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST.RvTbl_DwgDate, dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.RvTbl_Approved, 
    FROM            dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST RIGHT OUTER JOIN
                             dbo.Documents INNER JOIN
                             dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST.DocumentID ON 
                             dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.DocumentID = dbo.Documents.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_DWGREVISION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.DocumentID LEFT OUTER JOIN
                             dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.DocumentID
    ORDER BY dbo.Documents.DocumentID DESC

    • Edited by martirl3 Thursday, February 7, 2019 12:17 AM
    Tuesday, February 5, 2019 3:46 PM
    • Marked as answer by martirl3 Monday, August 19, 2019 2:48 PM
    Monday, August 19, 2019 2:48 PM