January 24, 2014 at 10:36 am
I have the following IP Function that converts an IP address.
USE [ip2location]
GO
/****** Object: UserDefinedFunction [dbo].[Dot2LongIP] Script Date: 1/24/2014 12:16:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Dot2LongIP](@IP VarChar(15))
RETURNS BigInt
AS
BEGIN
DECLARE @ipa BigInt,
@ipB Int,
@ipC Int,
@ipD Int,
@ipI BigInt
SELECT @ipa = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipA) - 1 )
SELECT @ipB = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipB) - 1 )
SELECT @ipC = LEFT(@ip, PATINDEX('%.%', @ip) - 1 )
SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipC) - 1 )
SELECT @ipD = @ip
RETURN ( @ipa * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD
END
--RETURN @ipI
--END
I then run the function from an SP.
ALTER Procedure [dbo].[GetIPdetails]
-- Add the parameters for the stored procedure here
@IP VarChar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM ip2location_db3
WHERE (SELECT top 1 dbo.Dot2LongIP(@IP) FROM ip2location_db3) BETWEEN ip_from and ip_to
END
Table structure for the data table.
USE [ip2location]
GO
/****** Object: Table [dbo].[ip2location_db3] Script Date: 1/24/2014 12:19:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ip2location_db3](
[ip_from] [float] NOT NULL,
[ip_to] [float] NOT NULL,
[country_code] [nvarchar](2) NOT NULL,
[country_name] [nvarchar](64) NOT NULL,
[region_name] [nvarchar](128) NOT NULL,
[city_name] [nvarchar](128) NOT NULL
) ON [PRIMARY]
GO
Indexes.
USE [ip2location]
GO
/****** Object: Index [ip_to] Script Date: 1/24/2014 12:19:42 PM ******/
CREATE NONCLUSTERED INDEX [ip_to] ON [dbo].[ip2location_db3]
(
[ip_to] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [ip2location]
GO
/****** Object: Index [IX_ip_from] Script Date: 1/24/2014 12:20:06 PM ******/
CREATE NONCLUSTERED INDEX [IX_ip_from] ON [dbo].[ip2location_db3]
(
[ip_from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Sample table data.
ip_fromip_tocountry_codecountry_nameregion_namecity_name
5033164850331903USUNITED STATESMASSACHUSETTSBEVERLY
5033190450332159USUNITED STATESNEW YORKMONROE
5033216050332671USUNITED STATESCONNECTICUTFAIRFIELD
5033267250332927USUNITED STATESNEW JERSEYLEBANON
5033292850333695USUNITED STATESCONNECTICUTFAIRFIELD
5033369650333951USUNITED STATESMISSOURICHILLICOTHE
5033395250334719USUNITED STATESCONNECTICUTFAIRFIELD
5033472050334975USUNITED STATESMISSOURICHILLICOTHE
5033497650335487USUNITED STATESCONNECTICUTFAIRFIELD
5033548850335743USUNITED STATESMISSOURICHILLICOTHE
Table has approx 2.5 Million records and the SP takes 3.2 seconds to run at best.
Anything i can do to improve performance?
Thanks.
January 24, 2014 at 10:46 am
PARSENAME (usually used for server.database.schema.table) will work just as well for an IP4 address, and would save you some of your patindexing.
Can we see the query you're using to get your final information and if possible can you grab the actual execution plan as outlined below in my links (help with index/tuning)?
Edit: Nevermind, I see the proc now, my bad. Must have scrolled too fast. Optimization plan would still help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2014 at 10:58 am
Plan as requested.
Thanks.
January 24, 2014 at 11:03 am
Taking the function call out of your query would help.
ALTER Procedure [dbo].[GetIPdetails]
@IP VarChar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @LongIP bigInt
SET @LongIP = dbo.Dot2LongIP(@IP)
SELECT * FROM ip2location_db3
WHERE @LongIP BETWEEN ip_from and ip_to
END
Or changing the Scalar function into an iTVF to gain performance. (Reference: How to Make Scalar UDFs Run Faster (SQL Spackle)[/url])
ALTER FUNCTION [dbo].[Dot2LongIP](@IP VarChar(15))
RETURNS TABLE
AS
RETURN
SELECT ( CAST( PARSENAME( @IP,4) AS bigint) * 256*256*256 ) +
( CAST( PARSENAME( @IP,3) AS bigint) * 256*256 ) +
( CAST( PARSENAME( @IP,2) AS bigint) * 256 ) +
CAST( PARSENAME( @IP,1) AS bigint) LongIP
GO
And you could call it in a different way to avoid row by row processing.
SELECT *
FROM #ip2location_db3 ip
CROSS APPLY [dbo].[Dot2LongIP](@IP) x
WHERE x.LongIP BETWEEN ip.ip_from AND ip.ip_to
January 24, 2014 at 11:14 am
WOW, what an improvement !
From 3.2 seconds to 0.3 seconds 🙂
Many Thanks!
January 24, 2014 at 11:18 am
isuckatsql (1/24/2014)
WOW, what an improvement !From 3.2 seconds to 0.3 seconds 🙂
Many Thanks!
Which path did you take?
January 24, 2014 at 11:35 am
I copied what you provided in the above post.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply