2

I have a few complex queries that are ran very often. Caching the results is not possible, as they're updated most of the time, and seeing the updated data is the whole point.

I'm not allowed to change the database settings, and those who are won't do it unless hell freezes over first, so I have to do everything I can to optimize queries and tables.

Since I think I already did all I could for these queries and the tables they use, I was thinking if there would be any gains in speed if I were to create stored procedures for them.

Would it work to increase speed, or should I look for something else?

MarkR
  • 62,604
  • 14
  • 116
  • 151
Carlos
  • 57
  • 2
  • 9
  • What about setting up a view... – Ben Nov 19 '10 at 17:22
  • I thought about it a few times, but I remember reading somewhere how mysql didn't compile the query used for a view, so all it would accomplish is making the code look nicer, so I didn't bother with views. Or is my information wrong? – Carlos Nov 19 '10 at 17:30

3 Answers3

2

No, using a stored procedure will not increase the performance of a "hard" query.

Mostly hard queries are caused by the database needing to do a lot of work to find the answer. This won't be any different if it's in a stored procedure.

Changing the database settings might affect some things, but usually the best ways of optimising a query is to change the structure of your data, so that you need to query either fewer rows or fewer columns. Alternatively, you might be able to have it use better indexes or some other way of improving the query.

Use EXPLAIN. Use a non-production system for performance testing. Don't bother putting your queries into a procedure (if performance is your only reason for wanting to do so).

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • All I have been doing already, and I got huge improvements on performance. Then things slowed down again as the tables kept growing. – Carlos Nov 19 '10 at 17:25
  • Do you think de-normalizing a database for performance purposes is justifiable as well? That can often decrease the complexity of a query, though it might increase the amount of time it takes to insert/update data. But it seems like those costs could be amortized since INSERTs and UPDATEs are often performed in smaller chunks over longer periods of time. – Lèse majesté Nov 19 '10 at 17:36
  • Already did some denormalisation. It drastically reduced the time it took for some of my slowest queries. Or maybe I had designed the tables poorly, which is not something that would surprise me, since I had never worked with tables that would reach hundreds of thousands of rows and need joins with other tables just as large. – Carlos Nov 19 '10 at 17:47
  • 1
    +1 for 'use EXPLAIN'. We once had query that ran for *hours* at a client once a certain table reached a particular (large) number of rows. Turned out that a join in a subquery resulted in a table scan on a very small table (<20 rows). *Hundreds of thousands* of times, since it was in a subquery. We unrolled that subquery & runtime dropped from 20 hours to < 5 minutes. – DaveE Nov 19 '10 at 18:16
0

Yes. Using Stored Procedures will increase performance. Since the SP is compiled and stored in database server.

But then it also depends upon the structure of the table and query!

As data grows performance will be low if you have poor database structure and non optimized query.

Bhaskar
  • 1,680
  • 7
  • 26
  • 40
  • Can you provide evidence of this as I am not 100% sure this is correct. – Rippo Nov 19 '10 at 17:23
  • Refer the following link under the section - Stored Procedures and Execution Plans http://msdn.microsoft.com/en-us/library/aa174792%28SQL.80%29.asp – Bhaskar Nov 19 '10 at 17:33
  • "Content not found". Also, if it's on msn, won't it be about SQLServer? – Carlos Nov 19 '10 at 17:36
  • Oops sorry try the following link.. http://msdn.microsoft.com/en-us/library/aa174792%28SQL.80%29.aspx – Bhaskar Nov 19 '10 at 17:40
  • Refer the following link :http://stackoverflow.com/questions/1151255/mysql-store-procedure-performance – Bhaskar Nov 19 '10 at 17:41
  • Thanks for correcting the link, but it doesn't really help when I'm using MySQL and not SQLServer. – Carlos Nov 19 '10 at 17:48
0

Refer following link

MySQL Stored Procedure vs. complex query

It will give you small performance boost.

Community
  • 1
  • 1
Bhaskar
  • 1,680
  • 7
  • 26
  • 40
  • I wish I had found that question earlier. Anyway, it answered the question if I should use a SP, and it seems the answer is "no", because the performance gain will be very small. – Carlos Nov 19 '10 at 18:15