0

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?

0
  • 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.

  • @Rick James thanks for your answer. 1- Is this the only difference? -- CompanyID = 931 versus CompanyID = Company_ID-- Yes. 2- Is CompanyID declared BIGINT? Yes. 3- Actually I am using laravel, so I am not able to user "fnStripTags" function to remove html code from description, So this is the reason, I am using SP over normal query. I first create normal query than convert them in SP to get easily called in Laravel eloquent. Thanks – rkrathor Dec 27 '19 at 10:45
  • Sometimes 3rd party packages make more trouble than benefit. – Rick James Dec 27 '19 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.