Stored Procedures Performance in MySql

Question

I am using a SP in my database which I have reused from a previous question Date Intervals to query a start and end date and return all days between the 2 dates which I then do a JOIN to my orders table.

For some reason the query with the SP takes over 40sec and under 3sec without with the SP.

I have done some research here and this answer suggests that the character set may have something to do it. SP Performance

The collation connection and database collation state utf8_general_ci and latin1_swedish_ci respective, and the table collation for the table is latin1_swedish_ci.

Here is my sql

call make_intervals('2014-01-29 00:00:00','2015-03-03 00:00:00',1,'DAY');

SELECT
interval_start,
SUM(TenderedAmt) total
FROM time_intervals ti
LEFT JOIN tblorderheaders o
ON o.OrderDate = ti.interval_start AND ( ClientId=35 or ClientId IS NULL ) GROUP BY DATE(ti.interval_start) ORDER BY ti.interval_start ASC;

If I execute the following query it takes within 0.2sec without the JOIN to the table produced by the procedure.

SELECT SUM(TenderedAmt) total FROM tblorderheaders WHERE ClientId=35 AND (OrderDate>='2014-01-29' AND OrderDate<='2015-02-27') GROUP BY DATE(OrderDate)

Can anyone advise to why this query takes so long?

Thanks in advance.

Hello Mate, maybe not indexed and having a well populated table. — Avidos, Mar 02 '15 at 05:05
Thanks. I do have my orders table indexed by the primary key combination, OrderId and ClientId and also ClientId. Would it be worth indexing the OrderDate? — Daniel James Canil, Mar 02 '15 at 05:08
`ClientId=35`, is that 35 normally a passed variable in the stored procedure? ie `ClientId=@ClientId` — Simon1979, Mar 02 '15 at 05:20
Just so I am clear, it you run the `SQL` you posted normally it takes 3 seconds but if you put the EXACT same `SQL` (no variables etc) in a stored procedure it takes 40 seconds? And just to clarify you are using `MySQL`? `SQL-Server` is tagged as well. — Simon1979, Mar 02 '15 at 05:28
The stored procedure table has only interval_start and interval_end. If that is what you are referring to? — Daniel James Canil, Mar 02 '15 at 05:28
here it is with out `SELECT SUM(TenderedAmt) total FROM tblorderheaders WHERE ClientId=35 AND (OrderDate>='2014-01-29' AND OrderDate<='2015-02-27') GROUP BY DATE(OrderDate)` — Daniel James Canil, Mar 02 '15 at 05:30

score 0 · Answer 1 · answered Mar 02 '15 at 09:39

0

Finally found the issue! The issue was the o.OrderDate was set to VARCHAR type. By changing it to a timestamp dramatically increased the query speed.

answered Mar 02 '15 at 09:39

Daniel James Canil

306
2
10

Stored Procedures Performance in MySql

1 Answers1