Stored Procedure taking longer than a normal query 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),'&amp;nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&amp;rsquo;',''))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),'&amp;nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&amp;#39;','`')),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),'&amp;nbsp;',''),' ',
            30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'&amp;rsquo;',''))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),'&amp;nbsp;',''),' ',
                30),'\r\n\t\r\n\t',''),'&amp;#39;','`')),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?
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.