I have one 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. But 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?
Thanks