Revisions to What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?

added [query-performance] to 2412 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:23

Community Bot

1

Tweeted twitter.com/StackDBAs/status/1104170171793637376

occurred Mar 9, 2019 at 0:00

Became Hot Network Question

occurred Mar 8, 2019 at 22:44

added 118 characters in body

Source Link

edited Mar 8, 2019 at 20:46

J Weezy

199
9

SELECT ICOUNT(DISTINCT DE.RowKey)
FROM ExchangeServerLogTestDE.IpGeoLocation EDE
INNER JOIN IpGeoLocationFWY.ExchangeServerLogTest IT
    ON ET.InsideProtocolKey = IDE.ProtocolKey
    AND ET.InsideIpHex*InsideIpHex **BETWEEN**BETWEEN IDE.NetworkStartIpHex AND IDE.NetworkEndIpHex

Estimated Query Execution Plan: Estimated Query Execution Plan Estimated InsideIp Query Execution Plan

SELECT ICOUNT(DISTINCT DE.RowKey)
FROM ExchangeServerLogTestDE.IpGeoLocation EDE
INNER JOIN IpGeoLocationFWY.ExchangeServerLogTest IT
    ON ET.OutsideProtocolKey = IDE.ProtocolKey
    AND ET.OutsideIpHex BETWEEN IDE.NetworkStartIpHex AND IDE.NetworkEndIpHex

Estimated Execution Plan: https://www.brentozar.com/pastetheplan/?id=H1OJg8evN Estimated OutsideIp Query Execution Plan

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.InsideProtocolKey = I.ProtocolKey
AND E.InsideIpHex* **BETWEEN** I.NetworkStartIpHex AND I.NetworkEndIpHex

Estimated Query Execution Plan: Estimated Query Execution Plan

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.OutsideProtocolKey = I.ProtocolKey
AND E.OutsideIpHex BETWEEN I.NetworkStartIpHex AND I.NetworkEndIpHex

Estimated Execution Plan: https://www.brentozar.com/pastetheplan/?id=H1OJg8evN

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.InsideProtocolKey = DE.ProtocolKey
    AND T.InsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Query Execution Plan: Estimated InsideIp Query Execution Plan

SELECT COUNT(DISTINCT DE.RowKey)
FROM DE.IpGeoLocation DE
INNER JOIN FWY.ExchangeServerLogTest T
    ON T.OutsideProtocolKey = DE.ProtocolKey
    AND T.OutsideIpHex BETWEEN DE.NetworkStartIpHex AND DE.NetworkEndIpHex

Estimated Execution Plan: Estimated OutsideIp Query Execution Plan

added 170 characters in body

Source Link

edited Mar 8, 2019 at 20:27

J Weezy

199
9

CREATE TABLE [dbo][FWY].[ExchangeServerLogTest](
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceFileName] [varchar](50) NOT NULL,
    [SourceServer] [varchar](9) NOT NULL,
    [SourceService] [varchar](6) NOT NULL,
    [EventOccuranceTs] [datetime] NOT NULL,
    [ServiceType] [varchar](50) NOT NULL,
    [UserNameType] [varchar](25) NOT NULL,
    [DomainId] [varchar](50) NULL,
    [DomainName] [varchar](255) NULL,
    [UserNameToLookup] [varchar](255) NOT NULL,
    [UserAgent] [varchar](255) NULL,
    [OutsideProtocolId] [varchar](10) NOT NULL,
    [OutsideIp] [varchar](39) NULL,
    [OutsideIpHex] [varbinary](16) NULL,
    [InsideProtocolId] [varchar](10) NOT NULL,
    [InsideIp] [varchar](39) NULL,
    [InsideIpHex] [varbinary](16) NULL,
    [DeviceId] [varchar](32) NULL,
    [DeviceType] [varchar](25) NULL,
    [DeviceModel] [varchar](75) NULL,
    [AsOfDt] [date] NULL,
    [OutsideProtocolKey] [int] NULL,
    [InsideProtocolKey] [int] NULL,
 CONSTRAINT [PK_ExchangeServerLogTest] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

CREATE TABLE [dbo][DE].[IpGeoLocation](
    [CreateTs] [datetime] NOT NULL,
    [CreateBy] [varchar](50) NOT NULL,
    [CreateSequenceKey] [int] NULL,
    [UpdateTs] [datetime] NULL,
    [UpdateBy] [varchar](50) NULL,
    [UpdateSequenceKey] [int] NULL,
    [ActiveInd] [int] NOT NULL,
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [VendorKey] [int] NULL,
    [VendorTypeKey] [int] NULL,
    [DimensionTypeKey] [int] NULL,
    [ProtocolKey] [int] NULL,
    [ProtocolId] [varchar](10) NOT NULL,
    [EffectiveStartDate] [date] NULL,
    [EffectiveEndDate] [date] NULL,
    [NetworkStartIp] [varchar](39) NOT NULL,
    [NetworkStartIpHex] [varbinary](16) NULL,
    [NetworkEndIp] [varchar](39) NOT NULL,
    [NetworkEndIpHex] [varbinary](16) NULL,
    [Country] [varchar](255) NOT NULL,
    [Region] [varchar](255) NOT NULL,
    [City] [varchar](255) NOT NULL,
    [ConnectionSpeed] [varchar](255) NOT NULL,
    [ConnectionType] [varchar](255) NOT NULL,
    [MetroCode] [int] NOT NULL,
    [Latitude] [numeric](6, 3) NULL,
    [Longitude] [numeric](6, 3) NULL,
    [PostalCode] [varchar](255) NOT NULL,
    [PostalExtension] [varchar](255) NOT NULL,
    [CountryCode] [int] NOT NULL,
    [RegionCode] [int] NOT NULL,
    [CityCode] [int] NOT NULL,
    [ContinentCode] [int] NOT NULL,
    [TwoLetterCountry] [varchar](2) NOT NULL,
    [InternalCode] [int] NOT NULL,
    [AreaCodes] [varchar](255) NOT NULL,
    [CountryConfidenceCode] [int] NOT NULL,
    [RegionConfidenceCode] [int] NOT NULL,
    [CityConfidenceCode] [int] NOT NULL,
    [PostalConfidenceCode] [int] NOT NULL,
    [GmtOffset] [varchar](255) NOT NULL,
    [InDistance] [varchar](255) NOT NULL,
    [TimeZoneName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_IpGeoLocation] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.InsideProtocolKey = I.ProtocolKey
AND E.InsideIpHex* **BETWEEN** I.NetworkStartIpHex AND I.NetworkEndIpHex
 
SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.OutsideProtocolKey = I.ProtocolKey
AND E.OutsideIpHex BETWEEN I.NetworkStartIpHex AND I.NetworkEndIpHex

Note 2: The ProtocolId must be included, otherwise there are two results for each IP lookup Estimated Query Execution Plan: one for IPv4 and one for IPv6.Estimated Query Execution Plan

This works in finding the IP Address and the execution plan shows the followingActual Query Execution Plan: Waiting for query to complete

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.OutsideProtocolKey = I.ProtocolKey
AND E.OutsideIpHex BETWEEN I.NetworkStartIpHex AND I.NetworkEndIpHex

Estimated Execution Plan: https://www.brentozar.com/pastetheplan/?id=H1OJg8evN

Actual Query Execution Plan: DOES NOT FINISH

Note 2: The ProtocolId must be included, otherwise there are two results for each IP lookup: one for IPv4 and one for IPv6.

CREATE TABLE [dbo].[ExchangeServerLogTest](
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceFileName] [varchar](50) NOT NULL,
    [SourceServer] [varchar](9) NOT NULL,
    [SourceService] [varchar](6) NOT NULL,
    [EventOccuranceTs] [datetime] NOT NULL,
    [ServiceType] [varchar](50) NOT NULL,
    [UserNameType] [varchar](25) NOT NULL,
    [DomainId] [varchar](50) NULL,
    [DomainName] [varchar](255) NULL,
    [UserNameToLookup] [varchar](255) NOT NULL,
    [UserAgent] [varchar](255) NULL,
    [OutsideProtocolId] [varchar](10) NOT NULL,
    [OutsideIp] [varchar](39) NULL,
    [OutsideIpHex] [varbinary](16) NULL,
    [InsideProtocolId] [varchar](10) NOT NULL,
    [InsideIp] [varchar](39) NULL,
    [InsideIpHex] [varbinary](16) NULL,
    [DeviceId] [varchar](32) NULL,
    [DeviceType] [varchar](25) NULL,
    [DeviceModel] [varchar](75) NULL,
    [AsOfDt] [date] NULL,
    [OutsideProtocolKey] [int] NULL,
    [InsideProtocolKey] [int] NULL,
 CONSTRAINT [PK_ExchangeServerLogTest] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

CREATE TABLE [dbo].[IpGeoLocation](
    [CreateTs] [datetime] NOT NULL,
    [CreateBy] [varchar](50) NOT NULL,
    [CreateSequenceKey] [int] NULL,
    [UpdateTs] [datetime] NULL,
    [UpdateBy] [varchar](50) NULL,
    [UpdateSequenceKey] [int] NULL,
    [ActiveInd] [int] NOT NULL,
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [VendorKey] [int] NULL,
    [VendorTypeKey] [int] NULL,
    [DimensionTypeKey] [int] NULL,
    [ProtocolKey] [int] NULL,
    [ProtocolId] [varchar](10) NOT NULL,
    [EffectiveStartDate] [date] NULL,
    [EffectiveEndDate] [date] NULL,
    [NetworkStartIp] [varchar](39) NOT NULL,
    [NetworkStartIpHex] [varbinary](16) NULL,
    [NetworkEndIp] [varchar](39) NOT NULL,
    [NetworkEndIpHex] [varbinary](16) NULL,
    [Country] [varchar](255) NOT NULL,
    [Region] [varchar](255) NOT NULL,
    [City] [varchar](255) NOT NULL,
    [ConnectionSpeed] [varchar](255) NOT NULL,
    [ConnectionType] [varchar](255) NOT NULL,
    [MetroCode] [int] NOT NULL,
    [Latitude] [numeric](6, 3) NULL,
    [Longitude] [numeric](6, 3) NULL,
    [PostalCode] [varchar](255) NOT NULL,
    [PostalExtension] [varchar](255) NOT NULL,
    [CountryCode] [int] NOT NULL,
    [RegionCode] [int] NOT NULL,
    [CityCode] [int] NOT NULL,
    [ContinentCode] [int] NOT NULL,
    [TwoLetterCountry] [varchar](2) NOT NULL,
    [InternalCode] [int] NOT NULL,
    [AreaCodes] [varchar](255) NOT NULL,
    [CountryConfidenceCode] [int] NOT NULL,
    [RegionConfidenceCode] [int] NOT NULL,
    [CityConfidenceCode] [int] NOT NULL,
    [PostalConfidenceCode] [int] NOT NULL,
    [GmtOffset] [varchar](255) NOT NULL,
    [InDistance] [varchar](255) NOT NULL,
    [TimeZoneName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_IpGeoLocation] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.InsideProtocolKey = I.ProtocolKey
AND E.InsideIpHex* **BETWEEN** I.NetworkStartIpHex AND I.NetworkEndIpHex
 
SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.OutsideProtocolKey = I.ProtocolKey
AND E.OutsideIpHex BETWEEN I.NetworkStartIpHex AND I.NetworkEndIpHex

Note 2: The ProtocolId must be included, otherwise there are two results for each IP lookup: one for IPv4 and one for IPv6.

This works in finding the IP Address and the execution plan shows the following:

CREATE TABLE [FWY].[ExchangeServerLogTest](
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [SourceFileName] [varchar](50) NOT NULL,
    [SourceServer] [varchar](9) NOT NULL,
    [SourceService] [varchar](6) NOT NULL,
    [EventOccuranceTs] [datetime] NOT NULL,
    [ServiceType] [varchar](50) NOT NULL,
    [UserNameType] [varchar](25) NOT NULL,
    [DomainId] [varchar](50) NULL,
    [DomainName] [varchar](255) NULL,
    [UserNameToLookup] [varchar](255) NOT NULL,
    [UserAgent] [varchar](255) NULL,
    [OutsideProtocolId] [varchar](10) NOT NULL,
    [OutsideIp] [varchar](39) NULL,
    [OutsideIpHex] [varbinary](16) NULL,
    [InsideProtocolId] [varchar](10) NOT NULL,
    [InsideIp] [varchar](39) NULL,
    [InsideIpHex] [varbinary](16) NULL,
    [DeviceId] [varchar](32) NULL,
    [DeviceType] [varchar](25) NULL,
    [DeviceModel] [varchar](75) NULL,
    [AsOfDt] [date] NULL,
    [OutsideProtocolKey] [int] NULL,
    [InsideProtocolKey] [int] NULL,
 CONSTRAINT [PK_ExchangeServerLogTest] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

CREATE TABLE [DE].[IpGeoLocation](
    [CreateTs] [datetime] NOT NULL,
    [CreateBy] [varchar](50) NOT NULL,
    [CreateSequenceKey] [int] NULL,
    [UpdateTs] [datetime] NULL,
    [UpdateBy] [varchar](50) NULL,
    [UpdateSequenceKey] [int] NULL,
    [ActiveInd] [int] NOT NULL,
    [RowKey] [int] IDENTITY(1,1) NOT NULL,
    [VendorKey] [int] NULL,
    [VendorTypeKey] [int] NULL,
    [DimensionTypeKey] [int] NULL,
    [ProtocolKey] [int] NULL,
    [ProtocolId] [varchar](10) NOT NULL,
    [EffectiveStartDate] [date] NULL,
    [EffectiveEndDate] [date] NULL,
    [NetworkStartIp] [varchar](39) NOT NULL,
    [NetworkStartIpHex] [varbinary](16) NULL,
    [NetworkEndIp] [varchar](39) NOT NULL,
    [NetworkEndIpHex] [varbinary](16) NULL,
    [Country] [varchar](255) NOT NULL,
    [Region] [varchar](255) NOT NULL,
    [City] [varchar](255) NOT NULL,
    [ConnectionSpeed] [varchar](255) NOT NULL,
    [ConnectionType] [varchar](255) NOT NULL,
    [MetroCode] [int] NOT NULL,
    [Latitude] [numeric](6, 3) NULL,
    [Longitude] [numeric](6, 3) NULL,
    [PostalCode] [varchar](255) NOT NULL,
    [PostalExtension] [varchar](255) NOT NULL,
    [CountryCode] [int] NOT NULL,
    [RegionCode] [int] NOT NULL,
    [CityCode] [int] NOT NULL,
    [ContinentCode] [int] NOT NULL,
    [TwoLetterCountry] [varchar](2) NOT NULL,
    [InternalCode] [int] NOT NULL,
    [AreaCodes] [varchar](255) NOT NULL,
    [CountryConfidenceCode] [int] NOT NULL,
    [RegionConfidenceCode] [int] NOT NULL,
    [CityConfidenceCode] [int] NOT NULL,
    [PostalConfidenceCode] [int] NOT NULL,
    [GmtOffset] [varchar](255) NOT NULL,
    [InDistance] [varchar](255) NOT NULL,
    [TimeZoneName] [varchar](255) NOT NULL,
 CONSTRAINT [PK_IpGeoLocation] PRIMARY KEY CLUSTERED 
(
    [RowKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
) ON [SECONDARY]

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.InsideProtocolKey = I.ProtocolKey
AND E.InsideIpHex* **BETWEEN** I.NetworkStartIpHex AND I.NetworkEndIpHex

Estimated Query Execution Plan: Estimated Query Execution Plan

Actual Query Execution Plan: Waiting for query to complete

SELECT I.RowKey
FROM ExchangeServerLogTest E
INNER JOIN IpGeoLocation I
ON E.OutsideProtocolKey = I.ProtocolKey
AND E.OutsideIpHex BETWEEN I.NetworkStartIpHex AND I.NetworkEndIpHex

Estimated Execution Plan: https://www.brentozar.com/pastetheplan/?id=H1OJg8evN

Actual Query Execution Plan: DOES NOT FINISH

Note 2: The ProtocolId must be included, otherwise there are two results for each IP lookup: one for IPv4 and one for IPv6.

deleted 5 characters in body

Source Link

edited Mar 8, 2019 at 20:16

J Weezy

199
9

Loading

added 4427 characters in body

Source Link

edited Mar 8, 2019 at 20:12

J Weezy

199
9

Loading

added 4427 characters in body

Source Link

edited Mar 8, 2019 at 20:07

J Weezy

199
9

Loading

added 4427 characters in body

Source Link

edited Mar 8, 2019 at 20:05

J Weezy

199
9

Loading

added 27 characters in body

Source Link

edited Mar 8, 2019 at 19:49

J Weezy

199
9

Loading

added 576 characters in body

Source Link

edited Mar 8, 2019 at 19:43

J Weezy

199
9

Loading

added 4 characters in body

Source Link

edited Mar 8, 2019 at 19:26

J Weezy

199
9

Loading

added 4 characters in body

Source Link

edited Mar 8, 2019 at 19:21

J Weezy

199
9

Loading

added 279 characters in body

Source Link

edited Mar 8, 2019 at 19:14

J Weezy

199
9

Loading

Source Link

asked Mar 8, 2019 at 19:08

J Weezy

199
9

Loading

Stack Exchange Network

Return to Question