Click here to Skip to main content
15,444,353 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I am trying to execute the below query, but its taking 3 hours to get executed.
Can anyone please help me out to optimize the query.
in the below query,ROM_Employee_Master and ROM_Contracts are the views that I have created.

SQL
SELECT DISTINCT X1.DateD DATE_X1, X1.Employee_Name, CASE WHEN X2.DateD IS NULL THEN 0 ELSE 1 END UTIL_FLAG
FROM
(SELECT DISTINCT B.DateD, A.Employee_Name
FROM ROM_Employee_Master A, DIMDATE B, ROM_Contracts C
WHERE CAST(B.DateD AS DATE) BETWEEN CAST(A.DOJ AS DATE) AND C.EndDate

) X1

LEFT JOIN

(SELECT DISTINCT B.DateD, A.EmployeeName
FROM ROM_Contracts A, DIMDATE B
WHERE CAST(B.DateD AS DATE) BETWEEN CAST(A.StartDate AS DATE) AND A.EndDate

) X2

ON X1.Employee_Name = X2.EmployeeName
AND X1.DateD = X2.DateD


What I have tried:

I am trying with Materialized view
Posted
Updated 18-Jul-22 0:51am
v2

1 solution

First thing I notice is that you are using a lot of casts e.g.
CAST(B.DateD AS DATE) BETWEEN CAST(A.StartDate AS DATE)
. Change your database to store dates as dates, get rid of the casts and you should notice a profound improvement

You will need to share details of your table/view schemas for a fuller analysis

Edit: On a second look I want to draw your attention to the sub-query
SQL
SELECT DISTINCT B.DateD, A.Employee_Name
	FROM ROM_Employee_Master A, DIMDATE B, ROM_Contracts C
	WHERE CAST(B.DateD AS DATE) BETWEEN CAST(A.DOJ AS DATE) AND C.EndDate
That is pre-ANSI 92 syntax which is limited and confusing. It is incredibly easy to product cross-joins and that is what you have done here.

If you alter that to the more robust and flexible (and clearer!) ANSI 92 method of using Explicit Joins you can see the problem
SQL
SELECT DISTINCT B.DateD, A.Employee_Name
FROM ROM_Employee_Master A
INNER JOIN DIMDATE B ON CAST(B.DateD AS DATE) BETWEEN CAST(A.DOJ AS DATE) AND C.EndDate
INNER JOIN ROM_Contracts C
there is nothing to limit the rows coming from ROM_Contracts, so you are going to get a row in your results for every single row on ROM_Contracts repeated for every single row returned from ROM_Employee_Master joined to DIMDATE.

That will explain why it takes hours to run.

Once you have tidied up your joins you will probably be able to see that you don't need the sub-queries at all. Again, that will aid performance
 
Share this answer
 
v3
Comments
prasad patil Jul2022 18-Jul-22 8:39am     CRLF
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.
CHill60 18-Jul-22 11:09am     CRLF
Is this with MS-SQL or MySQL? And what version? Also note my comment in my solution : "You will need to share details of your table/view schemas for a fuller analysis"
CHill60 18-Jul-22 11:14am    
Note that I have updated my solution with another observation
prasad patil Jul2022 18-Jul-22 14:01pm     CRLF
Its mysql workbench 6.2CE tool I will share the details of views
prasad patil Jul2022 19-Jul-22 2:49am     CRLF
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`)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900