Comments by prasad patil Jul2022 (Top 8 by date)

Hi @chill60
same query taking only 9sec to get execute in sql server
but more than 3 hours taking in mysql server , why so?

Hi @chill60
below are my view schemas
1)ROM CONTRACTS VIEW
CREATE
ALGORITHM = UNDEFINED
DEFINER = `nicehrmslive0505`@`%`
SQL SECURITY DEFINER
VIEW `ROM_Contracts` AS
(SELECT
`CT`.`Code` AS `Employee_Code`,
`CT`.`Name` AS `EmployeeName`,
`P`.`Name` AS `name`,
DATE_FORMAT(`PM`.`FromDate`, '%d-%m-%y') AS `FromDate`,
`PL`.`Name` AS `Location`,
DATE_FORMAT(`PM`.`ToDate`, '%d-%m-%y') AS `ToDate`,
DATE_FORMAT(`PM`.`ActualEndDate`, '%d-%m-%y') AS `ActualEndDate`,
DATE_FORMAT(`PM`.`ExtendedTo`, '%d-%m-%y') AS `ExtendedTo`,
(CASE
WHEN (`P`.`ProjectAllocationType_160` = 1136) THEN 'Trainer'
ELSE `PM`.`Role`
END) AS `Role`,
`CC`.`Name` AS `ClientName`,
`PID`.`StartDate` AS `StartDate`,
`PID`.`EndDate` AS `EndDate`,
`GMD`.`Name` AS `ProjectTypes`,
(CASE
WHEN (`P`.`FixedBid` = 1) THEN 'FixedBid'
WHEN (`P`.`Consolidated` = 1) THEN 'Consolidated'
WHEN
((`P`.`FixedBid` = NULL)
AND (`P`.`Consolidated` = NULL))
THEN
'None'
END) AS `ProjectType`
FROM
((((((((((`projects` `P`
JOIN `project_members` `PM` ON ((`P`.`ID` = `PM`.`ProjectID`)))
JOIN `project_location_masters` `PL` ON ((`P`.`ProjectLocationMasterID` = `PL`.`ID`)))
JOIN `currency` `CU` ON ((`P`.`CurrencyID` = `CU`.`ID`)))
JOIN `employees` `C` ON ((`PM`.`EmployeeID` = `C`.`ContactID`)))
JOIN `contacts` `CT` ON ((`PM`.`EmployeeID` = `CT`.`ID`)))
JOIN `contacts` `CC` ON ((`P`.`ClientID` = `CC`.`ID`)))
LEFT JOIN `general_master_details` `GMD` ON (((`P`.`ProjectAllocationType_160` = `GMD`.`ID`)
AND (`GMD`.`GMID` = 160))))
JOIN `project_invoice_details` `PID` ON (((`PM`.`ProjectID` = `PID`.`ProjectID`)
AND (`PM`.`ProjectInvoiceDetailID` = `PID`.`ID`))))
JOIN `customers` `CS` ON ((`P`.`ClientID` = `CS`.`ContactID`)))
LEFT JOIN `general_master_details` `GMDS` ON ((`PM`.`InvoiceBasedOn_236` = `GMDS`.`ID`)))
WHERE
((`P`.`IsInternal` = 0)
AND (`P`.`ProjectAllocationType_160` IN (1136 , 1137, 1138, 1603, 6706, 6708, 6709))))

2)ROM_EMPLOYEE_MASTER VIEW

CREATE
ALGORITHM = UNDEFINED
DEFINER = `nicehrmslive0505`@`%`
SQL SECURITY DEFINER
VIEW `ROM_Employee_Master` AS
(SELECT
`c`.`ID` AS `ID`,
`c`.`Code` AS `Employee_Code`,
`c`.`Name` AS `Employee_Name`,
`e`.`JoiningDate` AS `DOJ`,
`d`.`Name` AS `Designation`,
`dp`.`Name` AS `Department`,
`cc`.`Name` AS `Reporting Manager`,
MAX(`eh`.`ModifiedDate`) AS `MDate`
FROM
(((((`contacts` `c`
LEFT JOIN `employees` `e` ON ((`c`.`ID` = `e`.`ContactID`)))
LEFT JOIN `designation` `d` ON ((`c`.`ID` = `d`.`ID`)))
LEFT JOIN `employee_history` `eh` ON ((`c`.`ID` = `eh`.`ContactID`)))
LEFT JOIN `department` `dp` ON ((`eh`.`DepartmentID` = `dp`.`ID`)))
LEFT JOIN `contacts` `cc` ON ((`eh`.`ImmediateSuperiorID` = `cc`.`ID`)))
WHERE
((`dp`.`Name` = 'Consulting')
AND (NOT ((`c`.`Name` LIKE '%Testing%'))))
GROUP BY `c`.`ID` , `c`.`Code` , `c`.`Name` , `e`.`JoiningDate` , `d`.`Name` , `dp`.`Name`)

Its mysql workbench 6.2CE tool
I will share the details of views

Hi Chill60,
I tried removing CAST function as you suggested but no improvement in the result.
I am trying with Materialized view, if you know about materialized view then could you please give some idea about it.