Re: VERY VERY SLOW PROCEDURE - SOMETIMES

[Peter Brawley]

Quote

DIRECT ACCESS - 0.04 SEC SELECT * FROM MAIN WHERE LAB NO = '<SOME LAB NUMBER>';
STORED PROCEDURE - 25+ sec - CALL Test_SELECT(<some lab number>) where test select contains the above select statement.

To optimise MySQL queries you need to differentiate the consequences of (i) table design including keys, (ii) query design, (iii) query engine settings, and of course (iv) query caching.

MySQL sprocs are slow and don't cache much, so the performance difference you cite is likely due to both those effects.

To control for caching while you study the effects of table design, query design and query engine params, you need need to turn caching off with something like ...

CREATE PROCEDURE setquerycaching( pcache bool )
BEGIN
  IF NOT pcache THEN
    IF @prev_query_cache_type IS NOT NULL THEN
      SET @prev_query_cache_type = @@LOCAL.query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NULL THEN
      SET @prev_query_cache_size = @@GLOBAL.query_cache_size;
    END IF;
    SET LOCAL query_cache_type = 0;
    SET GLOBAL query_cache_size = 0;
    RESET QUERY CACHE;
  ELSE
    IF @prev_query_cache_type IS NOT NULL THEN
      SET LOCAL query_cache_type= @prev_query_cache_type;
    END IF;
    IF @prev_query_cache_size IS NOT NULL THEN
      SET GLOBAL query_cache_size= @prev_query_cache_size;
    END IF;
  END IF;
  SELECT
    pcache AS 'Query Caching',
    @@LOCAL.query_cache_type AS QCacheType, 
    @prev_query_cache_type AS 'PrevType',
    @@GLOBAL.query_cache_size AS QCacheSize,
    @prev_query_cache_size AS 'PrevSize';
END;

The table design you show doesn't appear to reach 3NF. That might be a performance issue.

You've not shown the queries in the 20 other sprocs, so I can't make specific suggestions about those queries. One way of thinking about the performance problem this job presents is that your main sproc does the equivalent of 20 correlated subqueries. That's going to be slow.

Me, I'd forget about sprocs for now and try to optimise the tables & queries.

On optimising InnoDB queries see ...

Optimise queries and their index use:
http://dev.mysql.com/doc/refman/5.5/en/explain.html
http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html

Use the MySQL slow query log:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html

Optimise MySQL memory use:
http://mysql.rjweb.org/doc.php/memory

Optimise InnoDB configuration:
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html



Edited 1 time(s). Last edit at 11/05/2012 12:33PM by Peter Brawley.