Custom Page Design: Update field based on another field changed

Answered

Comments

7 comments

  • Avatar
    Eric Weldon

    Hi Joe,

    If you add auditing to the table, the #AuditColumn table will tell you which field was updated (when the field is not null). You may be able to leverage that in your procedure to make the update(s). 

    0
    Comment actions Permalink
  • Avatar
    Kurt Vandergriend

    It is best to avoid field names that are reserved for special use in SQL or DSP, and "Status" is one of them.  Try renaming the field.  Also, to add Auditing, go to Admin > Data Sources > Audit > Tables.  Add the table for your page, and click on "Build Audit tables."

    0
    Comment actions Permalink
  • Avatar
    Joseph Flesche

    Eric Weldon, Kurt Vandergriend,

    Thank you for your replies. If I recall correctly, DSP auditing only runs based on transactions from the front end and not based on a database trigger. This caused significant performance issues for me in the past and I have implemented SQL Server triggers to audit table faster. Now it is true that I could have setup something incorrectly and thus caused more issues. Would you happen to have a link to best practices for setting up table auditing? Or maybe just on how DSP Auditing works so that I may understand to optimize the setup.

     

    Kurt Vandergriend,

    Yes, Status is a special field in SQL Server but is it also special in DSP? If so, where is it used? I have been using it for a while and have not noticed any issues so far with page rendering or functionality. 

    0
    Comment actions Permalink
  • Avatar
    Eric Weldon

    Hey Joe,

    I was offering an alternative to using triggers. You are correct, the auditing record only gets inserted when a change is made via the front end (regardless if the update is made using excel integration or added/edited on the page). My understanding of how the auditing works is that the framework captures 1) the primary keys of the record and 2) what was been modified and inserts the old and new values along with the field name into the #Audit (primary keys) and #AuditColumn tables. The audit indicator of 0 means that was the old/previous value and the indicator of 1 is the new/updated value. 

    In your previous post you mentioned adding auditing caused performance issues? Or did the issues appear after you added the trigger? 

     

    0
    Comment actions Permalink
  • Avatar
    Joseph Flesche

    Eric Weldon,

    The manually added SQL Server triggers work without an issue and are not noticeable from a page performance perspective.

    From my testing quite some time ago (~1 year), the DSP Auditing caused quite a bit of performance issues on the page (or really pages) that I had enabled. That is why I went down a custom trigger path in SQL Server. I'm not too sure if it was my page design in DSP, but I take advantage of the DSP header / footer page design on most (if not all) of my pages to show child data relationships. As a side note, I recently came across an issue with a page that was using an OUTER APPLY with an established sub-VIEW to gather counts of child records. When running it in SSMS, it ran in ~1 second for 10,000 records. The execution plan looked good to me. But in DSP, it would take 8-10 seconds to render the page. Removing the view used in the OUTER APPLY and placing the source SQL in place of it resolved the performance issues. I have no idea why that is the case. But it works and I am moving on keeping that in mind and that I may need to redesign a page or 2 as I go forward. (Sorry, that was long, I share just in case you pick up on something that I have done here.)

    0
    Comment actions Permalink
  • Avatar
    Eric Weldon

    Joseph Flesche - I haven't experienced performance issue when adding auditing to a table - may be worth a try again. If they persist, perhaps Support can help identity any issues. Regarding general page performance, it may be more helpful to grab the querie(s) that DSP uses to render the page (using Ctrl + Shift + K). The execution plan  would most likely be different and include the joins used for combo boxes or drop down. 

    Of course if your solution is working well - no need to re-invent the wheel. :)

     

    0
    Comment actions Permalink
  • Avatar
    Joseph Flesche

    Eric Weldon,

    I use CTRL+SHIFT+K quite frequently to troubleshoot pages. With my new pages, I will try the auditing functionality.

    I try not to use combo boxes or lists on the horizonal views because of the performance issues with page rendering. I mainly reserve them for verticals.

    Thank you for your feedback and suggestions.

    0
    Comment actions Permalink

Please sign in to leave a comment.