Mysql – Stored Procedure taking longer than a normal query

MySQLmysql-5.6performancequery-performancestored-procedures

I have a stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query.

Procedure

    DELIMITER $$
DROP PROCEDURE IF EXISTS `local_db`.`getAllMatchedStockByCompany` $$
CREATE
    PROCEDURE `local_db`.`getAllMatchedStockByCompany`(
    IN Company_ID BIGINT(20)
    )
    BEGIN
        SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),' ',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'’',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),' ',''),' ',
                30),'\r\n\t\r\n\t',''),''','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = Company_ID AND IsActive = 1
         ORDER BY ScheduledTime DESC;
    END$$

DELIMITER ;

The total execution time of this is 16 seconds with 1 row.

If I use below query:

SELECT t1.*
    FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),' ',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'’',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive
           ,t2.CompanyID FROM t2
          ) UNION
          (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),' ',''),' ',
                30),'\r\n\t\r\n\t',''),''','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive
        ,t3.CompanyID FROM t3
          )
         ) t1
         WHERE CompanyID = 931 AND IsActive = 1
         ORDER BY ScheduledTime DESC;

It took only .5 to 1 seconds with one row.

Can anyone of you let me why this happening or how I can improve my SP execution time?

Best Answer

  • It is usually better to cleanse the data before inserting it into the database.
  • The query can be sped up in both cases by some rearranging; see below.
  • Is this the only difference? -- CompanyID = 931 versus CompanyID = Company_ID
  • Is CompanyID declared BIGINT?
  • Can you get EXPLAIN output, both in the SP and outside, to help verify the theory that it is easier to work with a number than a parameter passed in.
  • If you know that there will be no duplicates, use UNION ALL, which is faster.

By moving the WHERE into each SELECT but leaving the `ORDER BY outside, the query is likely to be faster:

( SELECT ... WHERE ... )
UNION [DISTINCT|ALL]
( SELECT ... WHERE ... )
ORDER BY ...

Of the main problem is 931 versus Company_ID, then the solution may involved constructing the query and using prepare and execute.

Related Question