MySQL Stored Procedure VS. Complex Query

MySQL Stored Procedure vs. complex query

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

Look to indexes and proper table design to get better performance.

View or stored procedure for complex queries?

I like questions that define "good" - you've specifically asked about performance and maintainability, which allows answers to talk about that trade-off.

From a performance point of view, I don't think there's likely to be any difference between the 3 options, as long as the queries and data fit within your expected scenarios. I'd test with 100 times more data, and potentially widening the "where" clause to see what happens, but the indexing structure etc. is more likely to affect the performance than whether you execute the same SQL from a stored proc, through a view, or from a client application.

The best way to answer that question is to test it - there are, of course, many specific details that could invalidate the general "I'd expect x, y, or z" answers we overflowers can give. If performance is a critical concern, use a database filling tool (Redgate make on, I've used DBMonster in the past) and try all 3 options.

From a maintenance point of, view, I'd provide an option 4, which - in my view - is by far the best.

Option 4: build a data access library which encapsulates access to your data. Have the library expose methods and parameters to refine the selection of records. Consider using the specification pattern (http://en.wikipedia.org/wiki/Specification_pattern). Use whatever queries are best inside the library, and don't bother the developers with the implementation details.

If that doesn't work - heterogeneous application code, too much of a change for a simple requirement - I'd evaluate the options as follows:

1. Embedded SQL: depending on the number of times this SQL is re-used, this may be okay. If there's only one part of the code that runs the SQL, it's logically similar to the data access library. If, however, the same snippet needs to get re-used in lots of places, it's a likely source for bugs - a small change in the SQL would need to be repeated in several places.

2. Stored procedure: I generally dislike stored procedures for maintenance reasons - they tend to become brittle by over-loading, and create a procedural way of thinking. For instance, if you have other requirements for using this SQL calculation in a separate stored procedure, very quickly you end up with a procedural programming model, with stored procs calling each other.

3. Views: this is probably the best choice. It puts the specific data logic in a single place, but promotes the use of set-based logic because the access route is through a SELECT statement, rather than by executing a procedural statements. Views are easy to incorporate into other queries.

MySQL Stored Procedure for Complex Query takes 5 minutes

You should avoid cursor and work with sets:

INSERT into Inspections_due 
SELECT 
      inspection_id as iid 
FROM 
      Inspections I1 
where 
      type_due = '3' and inspection_completed is null
      and exists(
           Select inspection_id 
           from Inspections I2
           where I2.serial_number = I1.serial_number 
                 and I2.date_stamp >  I1.date_stamp
                 and inspection_completed is not null))

Also replace exists subquery by a inner join:

SELECT distinct
      inspection_id as iid 
FROM 
      Inspections I1 
inner join  
      Inspections I2
         on
                 I2.serial_number = I1.serial_number 
                 and I2.date_stamp >  I1.date_stamp
                 and inspection_completed is not null

Will stored procedure increases the performance of application and Ideal scenarios to use them?

A stored procedure does directly not make individual queries any faster, but it does allow some other benefits performance-wise:

• If your queries are complex enough (multiple ones). SP's can perform better by reducing the back and forth communication between client and the server by storing intermediate results in temp tables etc.
• Having very complex queries can sometimes be optimized by breaking queries into smaller ones (again, using temp tables). SP's offer nice way of encapsulating this into the db.

Stored procedures for complex queries

Refer following link

MySQL Stored Procedure vs. complex query

It will give you small performance boost.

MySQL Stored Procedures vs JPA Queries

In terms of performance, I don't have any specific numbers.
Please elaborate on specific concerns you might have.

In general, with JPA you have far less control when it comes to performance tuning than you would if implementing a custom solution. However, JPA provides a solid, proven infrastructure with a boat load of functionality that you don't have to write yourself! JPA will definitely help you to more quickly get your application off the ground.

In terms of learning curve. If I assume you are starting fresh... there is a great deal to learn with either approach. Both require a working knowledge of SQL and entity relationship models. The JPA approach requires you learn JPA! Go figure! A MySQL approach requires knowledge of JDBC.

Your question, 'do stored procedure run faster than JPA queries' is not really the right question to ask. JPA 2.1 supports stored procedures. The better question would be, does a query in JPA run faster than a JDBC invoked MySQL query or does a stored procedure in JPA run faster than a JDBC invoked MYSQL stored procedure. All in all, a direct JDBC approach may be a bit faster than JPA, but only due to the small overhead of translating JPQL (JPA's SQL-like language) to SQL.

Is it different a SQL query inside of stored procedure than a SQL executed from PHP?

Yes, preparing and executing a statement from a stored procedure is pretty much the same as sending the query from PHP. The only difference is whether the work of concatenating all the pieces is done in the PHP code running on the client, or the MySQL server.

If the contents of the query being constructed is dependent on other table data, it may be better to do it in a stored procedure, so you don't need multiple round trips between the database and PHP to get all the information.

But if it's only dependent on parameters already available in PHP, it's probably best to construct the query in PHP, if only because (IMHO) the PHP syntax is likely to be easier to understand. The performance difference should be minor -- string concatenation is simple for both PHP and MySQL. But I generally think that anything that isn't dependent on SQL data should not be done in the database server.

---

Related Topics