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.
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
could you use?
better yet