Are there significant performance benefits from changing involved queries to stored procedures?

Question

Obvious security benefits aside, are there significant performance boosts yielded from modifying involved queries to stored procedures in a SAP HANA database?

If so, are there metrics I can use to gauge perceived benefits?

score 1 · Answer 1 · answered May 28 '15 at 17:10

1

SQL Server 2005 onward, all SQL statements, irrespective of whether it’s a SQL coming from inline code or stored procedure or from anywhere else, they are compiled and cached. So, stored procedures won't give you performance boosts. They do give you better abstraction, security and ease of maintenance.

Read more about it here.

As for SAP HANA, I tried comparing it with Microsoft SQL Server(This article sheds some light on it.) and I cannot definitively say that it does compile and cache inline queries but it most probably should if you're using a recent version.

answered May 28 '15 at 17:10

Srini

1,626
2
15
25

I just ran some tests myself - and I'm seeing performance boosts up to five times over with stored procedures. Do you know where I can go to gauge which SAP HANA versions cache inline queries? – Louis93 May 28 '15 at 19:00
@Louis93, That's interesting, read [Matt's](http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode) answer on the same issue before you check for SAP HANA's query optimizations. Having said that, it very well might be that this **particular** DBMS isn't on par with the rest of the field but I **highly doubt it**. I'll try to find out more. – Srini May 28 '15 at 19:33

score 1 · Accepted Answer · answered Jun 08 '15 at 17:44

Read the other solution before reading this one.

Essentially, I ran some tests and swapped out my project's dynamic queries to stored procedures and saw massive performance bumps.

I was reading the SAP HANA Best Practices reference and came across this passage under Performance:

Executing dynamic SQL is slow because compile time checks and query optimization must be done for every invocation of the procedure. Another related problem is security because constructing SQL statements without proper checks of the variables used may harm security.

Are there significant performance benefits from changing involved queries to stored procedures?

2 Answers2