2

I'm currently designing my mysql db for an app i'm developing.

I like it when Stored Procedures are short and readable, and since my db involved some join statements, I thought maybe I should create Views with all the joins and query these Views from my Stored Procedures.

At first it sounds great, but when I though about performance, I realised that whenever a Stored Procedure is called it will run at least 2 queries:

  1. The View query
  2. The Stored Procedure query on that View

while using the join statements inside the Stored Procedure I will have only on query doing the joining and the selection from the join.

Am I right?

If so - what will be a good practice to maintain great development performance in terms of elegant code writing ?

VMai
  • 10,156
  • 9
  • 25
  • 34
Asaf Nevo
  • 11,338
  • 23
  • 79
  • 154
  • Consider that there are [many good reasons *not* to use stored procedures](http://stackoverflow.com/a/6369030/256196). – Bohemian Jul 31 '14 at 08:13
  • I've read your answer. To be honest - most of it is irrelevant to my case - mostly since i'm good at sql. The performance issues - I will test it – Asaf Nevo Jul 31 '14 at 08:33

2 Answers2

0

"great development performance" and "elegant code" are two worlds apart. You should not be pairing them so closely.

You are correct in saying that the creation of views beforehand will add a layer of inefficiency. If you are worried more about performance, then I would just put your joined queries directly in your stored procedure. Querying from a view will force the view to repopulate, which is one additional query you don't need.

That being said, one additional query probably won't make or break your database performance.

If you are really concerned about you code tidiness, I would suggest considering what data you are actually using from your join statements to see if you can shorten them or break them down. For example, are you using multiple columns or rows of data? Or are you just querying for a single value? If the latter, consider creating a function that returns this value. This will also help separate your code out of your stored procedure.

ElliotSchmelliot
  • 7,322
  • 4
  • 41
  • 64
0

A DBMS does not typically do separate queries in the situtation you are describing. It textually expands an appearance of a view name where used in a query and evaluates the entire resulting new expression. It does not evaluate a separate query for the view. Use a stored procedure to group and/or parameterize operations other than what a view gives you (namely parameterization by bases/views only and one select only).

That being said, you may not be able to sufficiently optimize a query that contains a view name compared to a query you could get by expanding the view definition and rearranging. But that is true whether or not the query in question is in a stored procedure.

See MySQL documentation. (Note a user comment that says that certain view clauses only prevent inlining/merging when they are in the outermost select expression.) Or this dba.stackexchange.com google 'mysql view optimization' hit.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • So if i understand you correctly - creating a generic view and group/"select where" from it using a store procedure - is not less efficient then doing a select query with group/"select where" embedded inside the query? – Asaf Nevo Aug 04 '14 at 07:27
  • I'm not sure what you are saying. Eg whether you mean a view called inside a stored procedure (as in your question) or a stored procedure called inside a query/view. A stored procedure call will have [its own costs and benefits](https://dev.mysql.com/doc/refman/5.6/en/stored-routines.html). But using a view as a table name inside another query (in a stored procedure or not) does not necessarily involve a separate query or temporary table. I added links to my answer. – philipxy Aug 04 '14 at 08:16
  • i'm saying that creating a view of all my data including joins of tables, and then querying this data using SP, is defiantly more elegant, but it fills like the DBMS process will be: create a view of all the data in the database with join -> query this data. using it in the same stored procedure fills like: selecting all the data in the database with join - but the join and selecting will happen only on relevant data.. – Asaf Nevo Aug 04 '14 at 08:20
  • I'm sorry, I can't understand you. Please edit your question: Give examples. Use more words and more sentences. Don't mention your feelings; it is not helpful and promotes unclear writing. Also, I don't understand why you mention "a generic view" and "a view of all the data in the database" in your comments but "views" in your question. Also, I can't tell whether you understand that the DBMS can sometimes inline/merge a view's definition where its name appears inside a query. – philipxy Aug 04 '14 at 09:51
  • sorry, i will try to rewrite it. as i see it there are 2 options: first one is to create a view with joins from some tables. also i use SP in order to query this view. that obviously much more elegant to the DBA which writes the SP since these SP will be much cleaner. but it looks like 2 queries. the second option is to skip the view part, and just write a SP with the joining sentences inside. this cause the SP code to be less elegant, but might be more efficient. I prefer the first option, but i'm not sure if it's the right one to choose for performance. – Asaf Nevo Aug 04 '14 at 11:02