April 5, 2021 at 2:50 am
Hi,
The below view is using cross join and as far as I know, Cross join is not good for performance. Can you please help me rewrite these views? Any suggestions are much appreciated on the below issues 1 & 2.
CROSS JOIN dbo.tbEmpType ect
LEFT OUTER JOIN t
ON t.Ece_ID = er.Employer_ID
2. NOT LIKE '%[^0-9.]%' THEN - I feel % placeholder is also should be avoided as best practice.
--CREATE VIEW [dbo].[vwTEST] --
AS
WITH t AS
(
SELECT
er.Ece_ID,
er.EmployerConfiguration_EmployerConfigurationType_ID,
er.Emp_Version,
er.EmployerConfiguration_Value,
er.RecCreated,
er.Creator_ID
FROM dbo.tbECM er
JOIN (SELECT EmployerConfiguration_EmployerConfigurationType_ID, Ece_ID,
MAX(Emp_Version) AS Max_Emp_Version
FROM dbo.tbECM Group BY EmployerConfiguration_EmployerConfigurationType_ID, Ece_ID) ecm
ON er.EmployerConfiguration_EmployerConfigurationType_ID = ecm.EmployerConfiguration_EmployerConfigurationType_ID
AND er.Ece_ID = ecm.Ece_ID
AND er.Emp_Version = ecm.Max_Emp_Version
)
SELECT er.Employer_ID AS Ece_ID,
ect.EmployerConfigurationType_ID AS EmployerConfiguration_EmployerConfigurationType_ID,
t.Emp_Version,
ISNULL(t.EmployerConfiguration_Value, ect.Default_Value) AS EmployerConfiguration_Value,
t.RecCreated,
t.Creator_ID,
CASE WHEN ISNULL(t.EmployerConfiguration_Value, ect.Default_Value) NOT LIKE '%[^0-9.]%' THEN
CAST(ISNULL(t.EmployerConfiguration_Value, ect.Default_Value) AS Money) ELSE NULL END
AS EmployerConfiguration_Value_computed
FROM dbo.tbEmp er
CROSS JOIN dbo.tbEmpType ect
LEFT OUTER JOIN t
ON t.Ece_ID = er.Employer_ID
AND t.EmployerConfiguration_EmployerConfigurationType_ID = ect.EmployerConfigurationType_ID
GO
April 5, 2021 at 7:41 am
With >2,000 points, you should know how to submit formatted SQL by now.
CREATE VIEW dbo.vwTEST --
AS
WITH t
AS (
SELECT er.Ece_ID
,er.EmployerConfiguration_EmployerConfigurationType_ID
,er.Emp_Version
,er.EmployerConfiguration_Value
,er.RecCreated
,er.Creator_ID
FROM dbo.tbECM er
JOIN
(
SELECT EmployerConfiguration_EmployerConfigurationType_ID
,Ece_ID
,Max_Emp_Version = MAX(Emp_Version)
FROM dbo.tbECM
GROUP BY EmployerConfiguration_EmployerConfigurationType_ID
,Ece_ID
) ecm
ON er.EmployerConfiguration_EmployerConfigurationType_ID = ecm.EmployerConfiguration_EmployerConfigurationType_ID
AND er.Ece_ID = ecm.Ece_ID
AND er.Emp_Version = ecm.Max_Emp_Version)
SELECT Ece_ID = er.Employer_ID
,EmployerConfiguration_EmployerConfigurationType_ID = ect.EmployerConfigurationType_ID
,t.Emp_Version
,EmployerConfiguration_Value = ISNULL(t.EmployerConfiguration_Value, ect.Default_Value)
,t.RecCreated
,t.Creator_ID
,EmployerConfiguration_Value_computed = CASE
WHEN ISNULL(t.EmployerConfiguration_Value, ect.Default_Value) NOT LIKE '%[^0-9.]%' THEN
CAST(ISNULL(t.EmployerConfiguration_Value, ect.Default_Value) AS MONEY)
ELSE
NULL
END
FROM dbo.tbEmp er
CROSS JOIN dbo.tbEmpType ect
LEFT OUTER JOIN t
ON t.Ece_ID = er.Employer_ID
AND t.EmployerConfiguration_EmployerConfigurationType_ID = ect.EmployerConfigurationType_ID;
April 5, 2021 at 2:25 pm
(1) CROSS JOIN is not necessarily bad for performance. If the CJ'd table is single row, it's definitely fine.
(2) The %s are OK here, since there's no easier way to do what needs done. Although to avoid the overhead of doing it repeatedly, it might be worth while to create a trigger that tests the column only once for the " NOT LIKE '%[^0-9.]%' " condition and sets a flag for the result; later queries could then just use the existing flag.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy