View using Cross Join and LIKE operator please suggest alternate syntax.

Question

View using Cross Join and LIKE operator please suggest alternate syntax.

sizal0234

SSCrazy

Points: 2272
More actions
April 5, 2021 at 2:50 am

#3865437
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.
1. FROM dbo.tbEmp er
  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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

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;

This reply was modified 3 years, 8 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101117 More actions · Answer 2

(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".