我有一个存储过程,它将运行 20 秒并产生高 cpu 使用率,我在慢速查询日志中找到它,这个存储过程始终存在并将运行 15 到 30 秒。我相信这个存储过程导致了高 CPU 使用率。 所以我正在尝试使用 explain 来获取存储过程的查询计划。而且我不知道如何改进这个存储过程。请分享一些关于如何改进存储过程的见解。
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectUsage4`(
IN p_ids MEDIUMTEXT
, IN p_locationIDs MEDIUMTEXT
, IN p_indicatorIDs MEDIUMTEXT
, IN p_fromDate date
, IN p_toDate date
, IN p_yearly tinyint(4)
, IN p_monthly tinyint(4)
, IN p_halfYear1 tinyint(4)
, IN p_halfYear2 tinyint(4)
, IN p_fourMonths1 tinyint(4)
, IN p_fourMonths2 tinyint(4)
, IN p_fourMonths3 tinyint(4)
, IN p_q1 tinyint(4)
, IN p_q2 tinyint(4)
, IN p_q3 tinyint(4)
, IN p_q4 tinyint(4)
, IN p_biMonthly1 tinyint(4)
, IN p_biMonthly2 tinyint(4)
, IN p_biMonthly3 tinyint(4)
, IN p_biMonthly4 tinyint(4)
, IN p_biMonthly5 tinyint(4)
, IN p_biMonthly6 tinyint(4)
, IN p_approvalStatus int(11)
, IN p_language nvarchar(10)
)
BEGIN
select
case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language) > 0 then
(select content from `labels` as lbl where lbl.ObjectID = l.id and lbl.ObjectName = 'locations' and lbl.ColumnName = 'LocationName' and lbl.LanguageCode = p_language limit 1)
else
l.LocationName
end as LocationName
, l.ParentID as LocationParentID
, l.Active as LocationActive
, l.RegionID
, case when (select count(lbl.id) from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language) > 0 then
(select content from `labels` as lbl where lbl.ObjectID = i.id and lbl.ObjectName = 'indicators' and lbl.ColumnName = 'IndicatorName' and lbl.LanguageCode = p_language limit 1)
else
i.IndicatorName
end as IndicatorName
, i.ParentID as IndicatorParentID
, i.Unit
, i.DecimalPlaces
, i.Active as IndicatorActive
, i.IndicatorType
, u.*
from
`usage` as u
left join `locations` as l on u.LocationID = l.id
left join `Indicators` as i on u.IndicatorID = i.id
where
u.IsDeleted = 0
and (
(p_fromDate is null and p_toDate is null)
or
(
p_fromDate is not null and p_toDate is not null
and
DATE(CONCAT(convert(u.`Year`, char(4)), '-', convert(u.`Month`, char(2)), '-1')) between p_fromDate and p_toDate
)
or
(
p_fromDate is not null and p_toDate is not null
and
u.`Month` is null
and
u.`Year` between Year(p_fromDate) and Year(p_toDate)
)
)
and (p_yearly is null or (p_yearly is not null and p_yearly = 1 and u.`Month` is null) or (p_yearly is not null and p_yearly = 0 and u.`Month` is not null))
and (p_monthly is null or (p_monthly is not null and p_monthly = 1 and u.`Month` is not null))
and (p_ids is null or FIND_IN_SET(u.id, p_ids))
and (p_locationIDs is null or FIND_IN_SET(u.LocationID, p_locationIDs))
and (p_indicatorIDs is null or FIND_IN_SET(u.IndicatorID, p_indicatorIDs))
and
(
(p_halfYear1 is null or u.HalfYear1 = p_halfYear1)
or (p_halfYear2 is null or u.HalfYear2 = p_halfYear2)
)
and
(
(p_fourMonths1 is null or u.FourMonths1 = p_fourMonths1)
or (p_fourMonths2 is null or u.FourMonths2 = p_fourMonths2)
or (p_fourMonths3 is null or u.FourMonths3 = p_fourMonths3)
)
and
(
(p_q1 is null or u.Q1 = p_q1)
or (p_q2 is null or u.Q2 = p_q2)
or (p_q3 is null or u.Q3 = p_q3)
or (p_q4 is null or u.Q4 = p_q4)
)
and
(
(p_biMonthly1 is null or u.BiMonthly1 = p_biMonthly1)
or (p_biMonthly2 is null or u.BiMonthly2 = p_biMonthly2)
or (p_biMonthly3 is null or u.BiMonthly3 = p_biMonthly3)
or (p_biMonthly4 is null or u.BiMonthly4 = p_biMonthly4)
or (p_biMonthly5 is null or u.BiMonthly5 = p_biMonthly5)
or (p_biMonthly6 is null or u.BiMonthly6 = p_biMonthly6)
)
and (
p_approvalStatus is null
or
(
select ara.ApprovalStatus
from `tasks_details` as t
inner join `approval_request_tasks` as art on t.TaskID = art.TaskID
inner join `approval_request_approvers` as ara on art.ApprovalRequestID = ara.ApprovalRequestID
where
t.IsDeleted = 0
and
t.ObjectID = u.id
and t.ObjectType = 'Usage'
order by
ara.ModifiedDate desc limit 1
) = p_approvalStatus
)
order by
i.IndicatorName, l.LocationName
;
END
存储过程的说明计划如下所示:
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra | |
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
| 1 | PRIMARY | u | ALL | (NULL) | (NULL) | (NULL) | (NULL) | 75095 | 10.00 | USING WHERE; USING TEMPORARY; USING filesort |
| 1 | PRIMARY | l | eq_ref | PRIMARY,Index1 | PRIMARY | 4 | pg.u.LocationID | 1 | 100.00 | |
| 1 | PRIMARY | i | eq_ref | PRIMARY | PRIMARY | 4 | pg.u.IndicatorID | 1 | 100.00 | |
| 6 | DEPENDENT SUBQUERY | ara | INDEX | Index1 | Index1 | 28 | (NULL) | 1384 | 100.00 | USING INDEX; USING filesort |
| 6 | DEPENDENT SUBQUERY | art | ref | Index1 | Index1 | 4 | pg.ara.ApprovalRequestID | 1 | 100.00 | USING INDEX |
| 6 | DEPENDENT SUBQUERY | t | ref | Index1 | Index1 | 161 | pg.art.TaskID,pg.u.id,const,const | 1 | 100.00 | USING INDEX |
| 5 | DEPENDENT SUBQUERY | lbl | ref | Index1 | Index1 | 644 | const,pg.i.id,const,const | 1 | 100.00 | |
| 4 | DEPENDENT SUBQUERY | lbl | ref | Index1 | Index1 | 644 | const,pg.i.id,const,const | 1 | 100.00 | USING INDEX |
| 3 | DEPENDENT SUBQUERY | lbl | ref | Index1 | Index1 | 644 | const,pg.l.id,const,const | 1 | 100.00 | |
| 2 | DEPENDENT SUBQUERY | lbl | ref | Index1 | Index1 | 644 | const,pg.l.id,const,const | 1 | 100.00 | USING INDEX |
+----+--------------------+-------+--------+----------------+---------+---------+-----------------------------------+-------+--------+----------------------------------------------+
usage
的表结构
CREATE TABLE `usage` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`LocationID` int(11) NOT NULL,
`IndicatorID` int(11) NOT NULL,
`Year` int(11) DEFAULT NULL,
`Month` int(11) DEFAULT NULL,
`HalfYear1` tinyint(4) DEFAULT NULL,
`HalfYear2` tinyint(4) DEFAULT NULL,
`FourMonths1` tinyint(4) DEFAULT NULL,
`FourMonths2` tinyint(4) DEFAULT NULL,
`FourMonths3` tinyint(4) DEFAULT NULL,
`Q1` tinyint(4) DEFAULT NULL,
`Q2` tinyint(4) DEFAULT NULL,
`Q3` tinyint(4) DEFAULT NULL,
`Q4` tinyint(4) DEFAULT NULL,
`BiMonthly1` tinyint(4) DEFAULT NULL,
`BiMonthly2` tinyint(4) DEFAULT NULL,
`BiMonthly3` tinyint(4) DEFAULT NULL,
`BiMonthly4` tinyint(4) DEFAULT NULL,
`BiMonthly5` tinyint(4) DEFAULT NULL,
`BiMonthly6` tinyint(4) DEFAULT NULL,
`DateOfUsage` date DEFAULT NULL,
`Price` decimal(24,10) DEFAULT NULL,
`PriceUnit` int(11) DEFAULT NULL,
`ExchangeRate` decimal(24,10) DEFAULT NULL,
`Value` decimal(24,10) DEFAULT NULL,
`ValueUnit` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Remarks` varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
`CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`CreatedDate` datetime DEFAULT NULL,
`ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`ModifiedDate` datetime DEFAULT NULL,
`IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
`IsHeatRecovery` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `Index1` (`LocationID`,`IndicatorID`,`Year`,`Month`,`HalfYear1`,`HalfYear2`,`FourMonths1`,`FourMonths2`,`FourMonths3`,`Q1`,`Q2`,`Q3`,`Q4`,`IsDeleted`,`CreatedDate`,`ModifiedDate`),
KEY `Index2` (`LocationID`,`IndicatorID`,`Year`,`BiMonthly1`,`BiMonthly2`,`BiMonthly3`,`BiMonthly4`,`BiMonthly5`,`BiMonthly6`,`CreatedDate`,`ModifiedDate`,`IsDeleted`),
KEY `Index3` (`LocationID`,`IndicatorID`,`DateOfUsage`,`IsDeleted`)
) ENGINE=InnoDB AUTO_INCREMENT=79273 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
locations
的表结构
CREATE TABLE `locations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ParentID` int(11) DEFAULT NULL,
`LocationName` varchar(100) CHARACTER SET utf8 NOT NULL,
`Active` tinyint(1) NOT NULL DEFAULT '1',
`CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`CreatedDate` datetime DEFAULT NULL,
`ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`ModifiedDate` datetime DEFAULT NULL,
`IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
`RegionID` int(11) DEFAULT NULL,
`IsRegionComingFromParent` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index1` (`id`,`ParentID`,`Active`,`IsDeleted`,`RegionID`)
) ENGINE=InnoDB AUTO_INCREMENT=445 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
indicators
的表结构
CREATE TABLE `indicators` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ParentID` int(11) DEFAULT NULL,
`IndicatorName` varchar(255) CHARACTER SET utf8 NOT NULL,
`IndicatorType` int(11) DEFAULT NULL,
`Unit` varchar(100) CHARACTER SET utf8 NOT NULL,
`DecimalPlaces` int(11) DEFAULT NULL,
`Active` tinyint(4) NOT NULL DEFAULT '1',
`CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`CreatedDate` datetime DEFAULT NULL,
`ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`ModifiedDate` datetime DEFAULT NULL,
`IsDeleted` tinyint(4) NOT NULL DEFAULT '0',
`SyncID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`LastSyncDate` datetime DEFAULT NULL,
`FormulaSummary` int(11) DEFAULT NULL,
`IndicatorCategory` int(11) DEFAULT NULL,
`BreakSync` tinyint(4) DEFAULT NULL,
`IsInteger` tinyint(1) DEFAULT '0',
`ActiveForReporting` tinyint(1) DEFAULT '1',
`BaselineYear` int(11) DEFAULT NULL,
`Ceiling` decimal(24,10) DEFAULT NULL,
`Floor` decimal(24,10) DEFAULT NULL,
`BreakSyncForUnit` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index1` (`ParentID`,`IndicatorType`,`IsDeleted`,`Active`)
) ENGINE=InnoDB AUTO_INCREMENT=10396 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
当我调用下面的存储过程时,Rows_examined是1133668,它运行到 7 秒,我认为这导致了 CPU 的繁重计算。
CALL `SelectUsage4`(NULL, '65,92,207,93,94,95,66,67,372,57,96,68,70,69,71,378,379,380,97,98,370,250,99,196,100,197,208,63,183,72,51,74,75,101,73,64,395,251,102,103,104,252,106,209,105,210,429,257,107,258,91,46,267,108,211,259,253,261,254,260,255,109,110,79,80,81,437,111,112,427,428,409,113,413,412,425,28,41,249,114,212,333,335,366,334,368,367,318,391,406,43,115,213,263,116,214,215,117,216,118,217,119,120,121,122,124,218,123,125,126,127,128,129,130,131,219,56,220,221,198,132,133,48,134,222,223,224,135,136,137,225,50,138,271,331,417,414,363,226,139,227,315,140,141,229,199,228,142,143,144,230,146,231,147,78,148,149,316,150,151,264,45,268,232,233,152,269,153,154,200,155,443,234,201,156,157,76,265,49,342,235,236,158,159,160,161,237,238,162,77,163,394,390,439,442,389,388,415,416,418,419,420,387,424,410,421,369,426,239,164,240,272,314,202,241,266,273,165,166,167,203,242,47,270,168,444,169,204,86,328,170,274,243,171,87,374,375,376,377,373,244,275,172,205,371,385,386,173,256,42,174,175,176,245,177,178,277,287,279,288,286,291,317,280,289,284,281,282,295,290,283,292,293,294,285,278,179,246,206,180,276,247,181,88,52,89,182,248,184,185,186,187,188,189,190,191,192,193,194,195,308,364,365,300,304,301,302,303,345,306,323,349,400,350,401,320,440,402,392,324,403,321,441,351,362,361,360,393,322,346,399,325,347,348,299,423,307,381,353,397,352,398,382,358,354,357,355,356,359,145,434,435,438,436,297,430,431,432,433,311,83,85,298,58,40,60,319,59,329,296,61,422,305,327,384,396,383,326,330,62,310,309,312,407', '10065', '2017-12-01', '2017-12-31', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 202, 'en-US');
编辑:
CREATE TABLE `labels` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Content` varchar(4096) DEFAULT NULL,
`LanguageCode` varchar(10) DEFAULT NULL,
`ObjectID` int(11) DEFAULT NULL,
`ObjectName` varchar(100) DEFAULT NULL,
`ColumnName` varchar(100) DEFAULT NULL,
`CreatedBy` varchar(100) DEFAULT NULL,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`ModifiedBy` varchar(100) DEFAULT NULL,
`ModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
`SyncID` varchar(50) DEFAULT NULL,
`LastSyncDate` datetime DEFAULT NULL,
`BreakSync` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Index1` (`LanguageCode`,`ObjectID`,`ObjectName`,`ColumnName`,`IsDeleted`),
KEY `index2` (`IsDeleted`),
KEY `index3` (`LanguageCode`),
KEY `index4` (`ObjectID`),
KEY `index5` (`ObjectName`),
KEY `index6` (`ColumnName`)
) ENGINE=InnoDB AUTO_INCREMENT=470129 DEFAULT CHARSET=utf8;
更改
WHEN ( SELECT COUNT(x) ... ) > 0
自
WHEN EXISTS( SELECT 1 ... )
此外,改变
CASE WHEN EXISTS (...)
THEN (SELECT ... LIMIT 1)
ELSE LocationName
END
简单地
IFNULL( (SELECT ... LIMIT 1), LocationName)
我看不到CREATE TABLE labels
;它需要
INDEX(ObjectID, ObjectName, ColumnName, LanguageCode) -- in any order
我不确定convert(u.Month, char(2))
能否正常工作 1 位数的月份。
u.Q1
中可以有哪些值? 我可能会对ORs
ANDs
的长期混乱进行优化.
这:
and (
p_approvalStatus is null
or
( big select ) = p_approvalStatus
)
看起来像查询的很大一部分(请参阅EXPLAIN
(。 建议通过拉出 NULL 测试来加快一些调用速度:
BEGIN
IF (p_approvalStatus IS NULL)
THEN
SELECT ...
FROM ...
WHERE ...
and ( (p_biMonthly1 ... )
-- (skipping the p_approvalStatus stuff)
ELSE
SELECT ...
FROM ...
WHERE ...
and ( (p_biMonthly1 ... )
AND ( big select ) = p_approvalStatus -- (no check for NULL)
END IF
END
可能也有一种方法可以进一步重写ELSE
部分。
如果我用PHP编写这个,我会用PHP"构造"查询,从而消除所有对"null"的检查,也许所有的"ors"然后发出更短,更简单的查询。
请提供SHOW CREATE TABLE labels
,我想了解'644'
。
你的"OR"条件很疯狂。 即使有括号,你也有它们。 第 1-4 季度的示例
(p_q1 is null or u.Q1 = p_q1)
or (p_q2 is null or u.Q2 = p_q2)
or (p_q3 is null or u.Q3 = p_q3)
or (p_q4 is null or u.Q4 = p_q4)
如果您为 Q4 提供 null 并为 Q1、Q2、Q3 包含 3 个特定值,您仍将遍历所有记录。 所以确保你明白你的意思... 例如,如果您执行上述操作,是否意味着您只需要第 1-3 季度的数据? 如果是这样,您可能的意思是将您的病情定为
哪里 ( -- 要么每个季度都为空 ( p_q1为空 和 p_q2为空 和 p_q3为空 和 p_q4 为空 ) 或 -- 或者,季度必须与提供的特定季度匹配。 -- 在这里,您只能提供 Q1 和 Q3,并且只拉动这两个季度 ( u.Q1 = p_q1 或 u.Q2 = p_q2 或 u.Q3 = p_q3 或 u.Q4 = p_q4 ) )
我将相同的上下文应用于半年 1-2、第四个月 1-3、季度 1-4 和双月刊 1-6 的标准
您关于年/月、从/到日期的其他顶级区域标准也很可怕,可能需要重新考虑,或编辑您的帖子并重新阐明您试图获得的内容与解析所有日期的年份和/或日期/时间字段的月份部分。
至于语言表中的位置和指示器,我已将其更改为预查询,以分别获取 ID 和给定的语言或指示器。 因此,为每个语言获取一次并左加入它们。 然后应用 COALESCE((,您可以获得特定于语言的语言(如果存在(,或者默认获得指标或位置表中的语言。
这是包含上述上下文的最终重写
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectUsage4`(
IN p_ids MEDIUMTEXT
, IN p_locationIDs MEDIUMTEXT
, IN p_indicatorIDs MEDIUMTEXT
, IN p_fromDate date
, IN p_toDate date
, IN p_yearly tinyint(4)
, IN p_monthly tinyint(4)
, IN p_halfYear1 tinyint(4)
, IN p_halfYear2 tinyint(4)
, IN p_fourMonths1 tinyint(4)
, IN p_fourMonths2 tinyint(4)
, IN p_fourMonths3 tinyint(4)
, IN p_q1 tinyint(4)
, IN p_q2 tinyint(4)
, IN p_q3 tinyint(4)
, IN p_q4 tinyint(4)
, IN p_biMonthly1 tinyint(4)
, IN p_biMonthly2 tinyint(4)
, IN p_biMonthly3 tinyint(4)
, IN p_biMonthly4 tinyint(4)
, IN p_biMonthly5 tinyint(4)
, IN p_biMonthly6 tinyint(4)
, IN p_approvalStatus int(11)
, IN p_language nvarchar(10)
)
BEGIN
SELECT
coalesce( LangLoc.LocationName, l.LocationName ) as LocationName,
l.ParentID as LocationParentID,
l.Active as LocationActive,
l.RegionID,
coalesce( LangInd.IndicatorName, i.IndicatorName ) as IndicatorName,
i.ParentID as IndicatorParentID,
i.Unit,
i.DecimalPlaces,
i.Active as IndicatorActive,
i.IndicatorType,
u.*
from
usage as u
left join Indicators as i
on u.IndicatorID = i.id
left join
( select
objectID,
MIN( content ) IndicatorName
from
labels as lbl
where
LanguageCode = p_language
and ObjectName = 'indicators'
and ColumnName = 'IndicatorName'
group by
objectID ) LangInd
ON u.IndicatorID = LangInd.ObjectID
left join locations as l
on u.LocationID = l.id
left join
( select
objectID,
MIN( content ) LocationName
from
labels as lbl
where
LanguageCode = p_language
and ObjectName = 'locations'
and ColumnName = 'LocationName'
group by
objectID ) Lang
ON l.id = Lang.ObjectID
where
u.IsDeleted = 0
and (
( p_fromDate is null
and p_toDate is null
)
or
(
p_fromDate is not null
and p_toDate is not null
and DATE(CONCAT(convert(u.`Year`, char(4)), '-', convert(u.`Month`, char(2)), '-1')) between p_fromDate and p_toDate
)
or
(
p_fromDate is not null
and p_toDate is not null
and u.`Month` is null
and u.`Year` between Year(p_fromDate) and Year(p_toDate)
)
)
and ( p_yearly is null
or ( p_yearly is not null
and p_yearly = 1
and u.`Month` is null)
or ( p_yearly is not null
and p_yearly = 0
and u.`Month` is not null )
)
and
( p_monthly is null
or ( p_monthly = 1
and u.`Month` is not null
)
)
and
( p_ids is null
or FIND_IN_SET(u.id, p_ids)
)
and
( p_locationIDs is null
or FIND_IN_SET(u.LocationID, p_locationIDs )
)
and
( p_indicatorIDs is null
or FIND_IN_SET(u.IndicatorID, p_indicatorIDs)
)
and
-- qualifying half-year criteria
(
( p_halfYear1 is null
AND p_halfYear2 is null
)
OR
( u.HalfYear1 = p_halfYear1
OR u.HalfYear2 = p_halfYear2
)
)
and
-- qualifying months criteria
(
( p_fourMonths1 is null
AND p_fourMonths2 is null
AND p_fourMonths3 is null
)
OR
( u.FourMonths1 = p_fourMonths1
or u.FourMonths2 = p_fourMonths2
or u.FourMonths3 = p_fourMonths3
)
)
and
-- qualifying quarters criteria
(
-- either EVERY Quarter is null
( p_q1 is null
AND p_q2 is null
AND p_q3 is null
AND p_q4 is null
)
OR
-- or, the quarter must match the specific one(s) that WERE provided.
-- here you could provide only Q1 and Q3 and only pull those 2 quarters
( u.Q1 = p_q1
or u.Q2 = p_q2
or u.Q3 = p_q3
or u.Q4 = p_q4
)
)
and
-- qualifying Bi-Monthly criteria
(
( p_biMonthly1 is null
AND p_biMonthly2 is null
AND p_biMonthly3 is null
AND p_biMonthly4 is null
AND p_biMonthly5 is null
AND p_biMonthly6 is null
)
OR
( u.BiMonthly1 = p_biMonthly1
or u.BiMonthly2 = p_biMonthly2
or u.BiMonthly3 = p_biMonthly3
or u.BiMonthly4 = p_biMonthly4
or u.BiMonthly5 = p_biMonthly5
or u.BiMonthly6 = p_biMonthly6
)
)
and
(
p_approvalStatus is null
or (
select ara.ApprovalStatus
from
tasks_details as t
inner join approval_request_tasks as art
on t.TaskID = art.TaskID
inner join approval_request_approvers as ara
on art.ApprovalRequestID = ara.ApprovalRequestID
where
t.IsDeleted = 0
and t.ObjectID = u.id
and t.ObjectType = 'Usage'
order by
ara.ModifiedDate desc limit 1
) = p_approvalStatus
)
order by
i.IndicatorName, l.LocationName
;
END