SQL Query Optimization

Question

My SQL Server version is

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53

and the query below just runs without producing an error or updating. The table [dbo].[Data] will produce roughly 15 entries that would need to be updated (has less than 1,000 rows in the table itself while [dbo].[Domains] has just under 9000 rows to check against.

Is there a better/quicker way for me to write this query so the appropriate rows will be updated? Below is sample DDL that has the same data structure as the actual production table I would be using this query with.

 CREATE TABLE [dbo].[Domains](
 [DOMAIN] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'gmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'hotmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'aol.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'yahoo.com')


CREATE TABLE [dbo].[Data](
  [email] [nvarchar](75) NULL
  ,[accept] [nvarchar](20) NULL
 ) ON [PRIMARY]
 GO
 SET ANSI_PADDING OFF
 GO
 INSERT [dbo].[Data] ([email]) VALUES (N'[email protected]')
 INSERT [dbo].[Data] ([email]) VALUES (N'[email protected]')
 INSERT [dbo].[Data] ([email]) VALUES (N'[email protected]')

 UPDATE dt
 SET [accept] = 
        case    
            when EXISTS (SELECT TOP 1 [DOMAIN]
                         FROM [dbo].[Domains] dmn
                         WHERE [dmn].[DOMAIN] = SUBSTRING([dt].[email],     CHARINDEX('@', [dt].[email]) + 1, LEN([dt].[email])))
            THEN 'NO'
            ELSE 'Yes'
        END
FROM [dbo].[Data] dt

Answer 1

This query avoids the Compute Scalar of your query and avoids any changes in your table structure. Should perform well.

SELECT dt.email
    , accept = CASE WHEN dm.[DOMAIN] IS NOT NULL THEN 'NO' ELSE 'Yes' END
FROM dbo.[Data] dt
    LEFT OUTER JOIN dbo.[DOMAINS] dm ON dt.email LIKE ('%@' + dm.DOMAIN)

Answer 2

A reach

I think you mean LEN([dmn].[DOMAIN])

It should not matter but maybe use

[DOMAIN] [nvarchar](75) NOT NULL

and make it a clustered index

UPDATE dt
SET dt.[accept] = case    
                     when [dmn].[DOMAIN] is null
                     THEN 'Yes'
                     ELSE 'NO'
                  END
FROM [dbo].[Data] dt 
LEFT JOIN dbo].[Domains] dmn
      on [dmn].[DOMAIN] = SUBSTRING([dt].[email], CHARINDEX('@', [dt].[email])+1, LEN([dmn].[DOMAIN]))
WHERE dt.[accept] is null