Query optimization in mysql workbench

Question

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 18-Jul-22 0:31am

prasad patil Jul2022

Updated 18-Jul-22 0:51am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2022-07-18T00:51:00

Solution 1

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

Posted 18-Jul-22 0:51am

CHill60

Updated 18-Jul-22 5:14am

v3

Comments

prasad patil Jul2022 18-Jul-22 8:39am

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

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

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

prasad patil Jul2022 19-Jul-22 2:49am

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`)

CHill60 19-Jul-22 5:49am

Your views are returning strings instead of dates - leave the formatting of data to the UI layer.
Also your where clause

AND (NOT ((`c`.`Name` LIKE '%Testing%))))

renders the query non-sargable (see Sargable - Wikipedia[^]). Tidy up your data to remove any Testing rows or impose a rule that "Testing" must be the first word in the Name so you can change there where clause to

AND (NOT ((`c`.`Name` LIKE 'Testing%))))

. Check that you have indexes on appropriate fields on each table e.g. best practices for mysql performance optimization[^]

Maciej Los 19-Jul-22 13:36pm

5ed!

prasad patil Jul2022 21-Jul-22 2:23am

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

CHill60 21-Jul-22 5:43am

Well you said earlier that you were using a materialized view. As those don't exist in MySQL (afaik) that would explain the difference - assuming that the table and index schemas are identical

prasad patil Jul2022 21-Jul-22 7:04am

but what will be the reason behind the query execution taking this much time as i have explained in the previous comment?

CHill60 21-Jul-22 8:02am

If you haven't made the changes I have suggested it would explain why the query takes so long in either MySQL or MSSQL. Using a Materialised view in MSSQL means it only looks at incremental changes so will be faster after having been run the first time.
If you haven't added the same indexes to the tables in both platforms, then again, that would be a reason for the difference.
If you really want to understand what is going on then I suggest using Query Analysers
MySQL : Chapter 9. The Query Analyzer Page[^]
MSSQL : Analyze an Actual Execution Plan - SQL Server | Microsoft Docs[^]
The way the databases are hosted could also be a factor in the performance differences you are seeing