1

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
2

this is expensive

where fullname like '%london%'

could you use?

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

better yet

where CityName    = 'london'
1

When you use a % sign to lead off a LIKE query, like you're doing:

where fullname like '%london%'

That's fairly CPU-intensive. Try this alternative - take out the leading % sign:

where fullname like 'london%'

You'll see the difference before & after. To illustrate the full details, run SET STATISTICS TIME ON in SSMS first, and you'll get output on the Messages tab of SSMS showing how much CPU time you use in both examples. You'll find that it's much higher with the leading % sign.

You can learn more about that in my post, Sargability: Why %string% Is Slow.

  • Without having to alter my where clause (as I am looking for anything with "london" in it and not what starts with "london" is there anything else I can do to my query to improve it or have i hit my limit? – Zoinky Nov 5 '16 at 19:06
  • No. You aren't able to use any of the indexes with a '% - %' style search string, so it has to scan the entire table each time. You might want to try some sort of persisted computed columns for your cities that are more likely to come up, and then query that computed column with an = operator, perhaps? – Randolph West Nov 5 '16 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.