Skip to Content

Stored Procedure and DB stability

I was going through the note: 1650957 - SAP HANA Database: Starting the Script Server. I came across this line about stored procedures...

"The disadvantage of database procedures, in general, is that they might somehow affect the stability of the database itself. In order to minimize the risk of destabilizing the SAP HANA database...."

A google search did not return anything on how a SP might affect a DB's stability. I am just curious since I have never heard this thing about SPs anywhere before :)

Can anyone throw some light on this?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 10, 2017 at 05:50 AM

    Besides certain parts of stored procedures the scriptserver also executed AFL/BFL function code. These can be externally written and may use resources outside the workload management restrictions. Having such code in a separate process should provide a better isolation against any issues in that kind of code.

    I doubt that sqlscript procedures were considered specifically problematic when this statement was put into the documentation/sap note.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 11, 2017 at 08:28 AM

    The general problem with most DB development using SP is it's harder to keep tracks of what's going on. For example you could easily get a deadlock condition from 2 SP using the same lock objects. It's the same kind of problems as in multithreaded development.

    Add comment
    10|10000 characters needed characters exceeded

    • It looks like you mix up the idea of database locks with database procedure usage. Locks (implicit and explicit) are used all the time when accessing data in HANA (and most other DBMS for that matter). Whether or not you put your statements into a stored procedure doesn't change this or any of the effects of it.

      When using explicit wide-ranging locks like a TABLE LOCK, then, of course, you can make it easier for yourself to create deadlocks. Connection to the usage of DB procedures? None.

      Locks are not dependent on the coding artefact you use, but on the transaction they belong to.