Sql-server – Improving performance of a view

sql server

I am executing the following query and it takes about 2 seconds to retrieve the data. (cities has about 150, 000) records so not that many.
I have indexes on the city table for name, provindid, and on the provinces, for provinceid, name, and on countries table name index

Link to the plan

https://www.brentozar.com/pastetheplan/?id=SJuYKiilg (old)

Second Plan
https://www.brentozar.com/pastetheplan/?id=HyHghijlx

select * from citieswithplaces where fullname like '%london%'

I am trying to bring that time down if possible, here is my view

CREATE VIEW [dbo].[CitiesWithPlaces]
WITH SCHEMABINDING
AS
SELECT      c.CityID, 
        c.Name AS CityName, 
        c.Position.Lat,
        c.Position.Long,
        p.Name as ProvinceName, 
        p.ProvinceID, COUNT(l.PlaceID) AS TotalListings,
        p.Abbv as RegionCode,
        cn.Code as CountryCode,
        cn.Name as CountryName,
        (c.Name + ' ' + COALESCE(p.AsciiName, '') + ' ' + cn.Name) as FullName,
        (LOWER('/'+ cn.Code + '/'+ p.Abbv + '/' + c.Name)) as URL
FROM         dbo.Cities AS c INNER JOIN
                  dbo.Places AS l ON l.CityID = c.CityID
                  INNER JOIN dbo.Provinces AS p ON p.ProvinceID = c.ProvinceID
                  INNER JOIN dbo.Countries AS cn ON p.CountryID = cn.CountryID
WHERE StatusID = 1 --Active 
GROUP BY c.CityID, p.AsciiName, c.Name, p.Name,  p.ProvinceID, cn.Code, p.Abbv, cn.Name, c.Position.Lat, c.Position.Long

I tried creating a index on the fullname of the view but it asked for way too many changes to the view that I got lost.

Here is the actual plan
enter image description here

UPDATE: create of the tables

/****** Object:  Table [dbo].[Countries]    Script Date: 11/5/2016 2:02:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Countries](
[CountryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Code] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 
(
[CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/****** Object:  Table [dbo].[Provinces]    Script Date: 11/5/2016 2:03:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Provinces](
[ProvinceID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](56) NOT NULL,
[Abbv] [nvarchar](56) NULL,
[CountryID] [int] NULL,
[AsciiName] [varchar](500) NULL,
 CONSTRAINT [PK_Provinces] PRIMARY KEY CLUSTERED 
(
[ProvinceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Provinces]  WITH CHECK ADD  CONSTRAINT [FK_Provinces_Countries] FOREIGN KEY([CountryID])
REFERENCES [dbo].[Countries] ([CountryID])
GO

ALTER TABLE [dbo].[Provinces] CHECK CONSTRAINT [FK_Provinces_Countries]
GO




/****** Object:  Table [dbo].[Cities]    Script Date: 11/5/2016 2:02:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Cities](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ProvinceID] [int] NOT NULL,
[Position] [geography] NOT NULL,
[MapZoomLevel] [int] NOT NULL CONSTRAINT [DF_Cities_MapZoomLevel]  DEFAULT ((11)),
[AlternateNames] [nvarchar](max) NULL,
[GeoNameID] [int] NULL,
[cs_AlternateNames]  AS (checksum([AlternateNames])),
 CONSTRAINT [PK_Cities_1] PRIMARY KEY CLUSTERED 
(
[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Cities]  WITH CHECK ADD  CONSTRAINT [FK_Cities_Provinces] FOREIGN KEY([ProvinceID])
REFERENCES [dbo].[Provinces] ([ProvinceID])
GO

ALTER TABLE [dbo].[Cities] CHECK CONSTRAINT [FK_Cities_Provinces]
GO



/****** Object:  Table [dbo].[Places]    Script Date: 11/5/2016 2:01:23 PM             ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Places](
[PlaceID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar](128) NULL,
[CreatedDateUTC] [datetime] NOT NULL,
[Address] [nvarchar](500) NOT NULL,
[PostalCode] [nvarchar](50) NOT NULL,
[StatusID] [int] NOT NULL,
[CityID] [int] NOT NULL,
[Description] [nvarchar](max) NULL,
[ImportReferenceNumber] [varchar](500) NULL,
[Name] [nvarchar](500) NOT NULL,
[Website] [nvarchar](500) NULL,
[UniqueID] [uniqueidentifier] NOT NULL,
[LastUpdatedDateUTC] [datetime] NOT NULL,
[PhoneNumber] [nvarchar](50) NULL,
[Position] [geography] NOT NULL,
[Facebook] [varchar](500) NULL,
[Twitter] [varchar](500) NULL,
[Instagram] [varchar](500) NULL,
[Options1ID] [int] NULL,
[Options2ID] [int] NULL,
[Options3ID] [int] NULL,
[Options4ID] [int] NULL,
[Options5ID] [int] NULL,
[TimeZoneID] [nvarchar](500) NULL,
[ImageURL] [nvarchar](500) NULL,
 CONSTRAINT [PK_Places] PRIMARY KEY CLUSTERED 
(
[PlaceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_AspNetUsers] FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_AspNetUsers]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_Cities] FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([CityID])    
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_Cities]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceOptions1] FOREIGN KEY([Options1ID])
REFERENCES [dbo].[PlaceOptions1] ([OptionID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceOptions1]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceOptions2] FOREIGN KEY([Options2ID])
REFERENCES [dbo].[PlaceOptions2] ([OptionID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceOptions2]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceOptions3] FOREIGN KEY([Options3ID])
REFERENCES [dbo].[PlaceOptions3] ([OptionID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceOptions3]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceOptions4] FOREIGN KEY([Options4ID])
REFERENCES [dbo].[PlaceOptions4] ([OptionID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceOptions4]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceOptions5] FOREIGN KEY([Options5ID])
REFERENCES [dbo].[PlaceOptions5] ([OptionID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceOptions5]
GO

ALTER TABLE [dbo].[Places]  WITH CHECK ADD  CONSTRAINT [FK_Places_PlaceStatuses]             FOREIGN KEY([StatusID])
REFERENCES [dbo].[PlaceStatuses] ([StatusID])
GO

ALTER TABLE [dbo].[Places] CHECK CONSTRAINT [FK_Places_PlaceStatuses]
GO

Best Answer

this is expensive

where fullname like '%london%'

could you use?

where CityName    = 'london'  
   or CountryName = 'london'
   or AsciiName   = 'london'

better yet

where CityName    = 'london'