-2

I am trying to find the pros and cons of using stored procedures instead of SQL queries from an external connection, but I am unable to find any direct comparison.

  • What is the benefit of using stored procedures instead of SQL queries from an external connection?
  • Is there any execution speed differences between them for small volume and big volume outputs?
  • Is there any benefits for the database management as well?
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Yordan Yanakiev
  • 2,546
  • 3
  • 39
  • 88
  • Can you clarify what you mean by "from an external connection"? I think you mean that both the SP and Query are coming from some external application, is that right? – Jeremiah Cooper Dec 29 '21 at 18:44
  • Honestly I am really concerned for what reason my question, which is more than important for decision making about really large scale projects is giving negative votes. Definitely StackOverflow is a place where asking questions leads to really strange results nowday.. Even "Close" votes!? Interesting! I would like to hear the explanation behind this. – Yordan Yanakiev Jan 01 '22 at 18:04

2 Answers2

1

What is the benefit of using stored procedures instead of SQL queries from an external connection?

  • Stored Procedures can be complex. Very complex. They can do things that a single SQL query cannot do. (Execute Block aside.)
  • They have their own set of grants so they can do things that current user cannot do at all.
  • Firebird optimizer is not that bad but obviously complex queries require more time for optimization and the result still may be suboptimal. Using imperative language the programmer can split complex query to set of simpler ones making Data Access Paths more predictable.

Is there any execution speed differences between them for small volume and big volume outputs?

No.

Is there any benefits for the database management as well?

It depends on what you call "database management" and what benefits you have on mind. Most likely - no.

user13964273
  • 1,012
  • 1
  • 4
  • 7
0

What is the benefit of using stored procedures instead of SQL queries from an external connection?

One benefit, in terms of execution, is stored procedures store their query plan whereas dynamic sql query plans will not be stored and must be calculated each time the query is executed.

Is there any execution speed differences between them for small volume and big volume outputs?

Once the query plan is calculated, no, there is no speed difference.

Is there any benefits for the database management as well?

This is very subjective! In the past I worked at a place where ALL database access went through stored procedures so that they could lock down access to just the SPs. Other places I've worked didn't use stored procs at all because they are generally outside source control and problematic for developers who aren't SQL gurus. Also, business logic spread across multiple systems can become a real problem.

Jeremiah Cooper
  • 117
  • 6
  • Dynamic qiuery? You mean ad-hoc SQL - the plans for these are also cached so that's simply not true. – Stu Dec 29 '21 at 19:57
  • In Firebird execution plan is not stored. A Stored Procedure is compiled from BLR and optimized at first load. A DSQL is compiled and optimized at prepare. – user13964273 Dec 29 '21 at 22:39
  • @Stu you're right, if you are executing the same sql over and over within the cache retention duration it will reuse the cache. There are also tricks to increasing how long the query plan will stay in cache such as parameterizing your queries. The more often a query plan is used the longer it is persisted. – Jeremiah Cooper Dec 30 '21 at 01:42
  • Also Firebird has no query plan cache. – user13964273 Dec 30 '21 at 11:27
  • One extra consideration is joining. I am not sure if select over external connection would be optimized like local select, for example sorting by index to facilitaty sorted streams merge, but why not? – Arioch 'The Jan 01 '22 at 11:26
  • Sorry! I didn't see the firebird tag on the post. I (incorrectly) assumed you meant SQL Server – Jeremiah Cooper Jan 03 '22 at 21:47