Thread: Function Speed vs UI Function Speed
Is there a significant difference in execution speed between similar functions that do the same DML but differ in the extra validations and messaging that are required for providing feedback to UI users? For example, a batch load script that inserts multiple rows into a table may call an insert function within a cursor to populate each row into the target table. Meanwhile, a non-technical business user may want to create only one entity via a UI. However, the UI must have validations and helpful messages to to user so the insert works....or won't, but at least user would know what they need to do differently to get to a successful outcome. Typically, the teams that I've worked on typically create the bare functions that will get the job done but later we have to come back and make new functions specifically for UIs. BTW....I'm a firm believer in having the database protect itself and not totally depend upon the application layer to protect data. Anyone with database access can bypass a UI and still corrupt data unintentionally (incomplete WHERE clause, for example). Now I wonder if it would be prudent to create the UI ready functions only and use those same functions for batch loads or any other process that needs to be done in bulk. I'm not sure about the performance impact of that. Any insights or ideas? Thanks for your help! Sue -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261
On Mon, Aug 31, 2020 at 10:32 AM Susan Hurst <susan.hurst@brookhurstdata.com> wrote:
For example, a batch load script that inserts multiple rows into a table
may call an insert function within a cursor to populate each row into
the target table. Meanwhile, a non-technical business user may want to
create only one entity via a UI. However, the UI must have validations
and helpful messages to to user so the insert works....or won't, but at
least user would know what they need to do differently to get to a
successful outcome.
I think you have both. You don't want the round trip of having the database throw an exception in this example. So yes, you have the validations within the UI for a higher level of success before submission. However, you still want the proper constraints on the database itself as it is the canonical source for your data. Further, you never know when someone else is going to connect to the database in something that isn't your application.
JD