March 9, 2017 at 1:48 am
Hi all,
I have like this
CREATE TABLE dbo.[IpRange]
(
[IpRangeid] [BIGINT] IDENTITY(1, 1)
NOT NULL ,
[IpStartInt] [BIGINT] NULL,
[IpEndInt] [BIGINT] NULL,
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [PK__IpRange] PRIMARY KEY CLUSTERED
( [IpRangeid] ASC )
);
it has index like this :
CREATE NONCLUSTERED INDEX [ix_IpEndIntIpStartInt] ON [dbo].[IpRange]
(
[IpStartInt] ASC,
[IpEndInt] ASC
);
this table has about 14 million records.
this is my query:
DECLARE @Ip BIGINT= 94176016019;
SELECT TOP ( 1 )
IpRangeid
FROM dbo.IpRange WITH ( NOLOCK )
WHERE @Ip BETWEEN IpStartInt AND IpEndInt;
the execution plan is :
but its extremely slow ! anyone has better idea ? better index ? or ...
March 9, 2017 at 2:12 am
farax_x - Thursday, March 9, 2017 1:48 AMHi all,
I have like this
CREATE TABLE dbo.[IpRange]
(
[IpRangeid] [BIGINT] IDENTITY(1, 1)
NOT NULL ,
[IpStart] [NVARCHAR](40) NULL ,
[IpEnd] [NVARCHAR](40) NULL ,
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [PK__IpRange] PRIMARY KEY CLUSTERED
( [IpRangeid] ASC )
);
it has index like this :
CREATE NONCLUSTERED INDEX [ix_IpEndIntIpStartInt] ON [dbo].[IpRange]
(
[IpStartInt] ASC,
[IpEndInt] ASC
);
this table has about 14 million records.
this is my query:
DECLARE @Ip BIGINT= 94176016019;SELECT TOP ( 1 )
IpRangeid
FROM dbo.IpRange WITH ( NOLOCK )
WHERE @Ip BETWEEN IpStartInt AND IpEndInt;the execution plan is :
but its extremely slow ! anyone has better idea ? better index ? or ...
Your table definition doesn't match your index or query - but I guess that means your thinking along the right lines, implicit conversion.
Match your variable datatype to your columns:
DECLARE @Ip NVARCHAR(40) = '94176016019';
SELECT TOP ( 1 )
IpRangeid
FROM dbo.IpRange WITH ( NOLOCK )
WHERE @Ip BETWEEN IpStart AND IpEnd;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2017 at 2:48 am
ChrisM@Work - Thursday, March 9, 2017 2:12 AMfarax_x - Thursday, March 9, 2017 1:48 AMHi all,
I have like this
CREATE TABLE dbo.[IpRange]
(
[IpRangeid] [BIGINT] IDENTITY(1, 1)
NOT NULL ,
[IpStart] [NVARCHAR](40) NULL ,
[IpEnd] [NVARCHAR](40) NULL ,
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [PK__IpRange] PRIMARY KEY CLUSTERED
( [IpRangeid] ASC )
);
it has index like this :
CREATE NONCLUSTERED INDEX [ix_IpEndIntIpStartInt] ON [dbo].[IpRange]
(
[IpStartInt] ASC,
[IpEndInt] ASC
);
this table has about 14 million records.
this is my query:
DECLARE @Ip BIGINT= 94176016019;SELECT TOP ( 1 )
IpRangeid
FROM dbo.IpRange WITH ( NOLOCK )
WHERE @Ip BETWEEN IpStartInt AND IpEndInt;the execution plan is :
but its extremely slow ! anyone has better idea ? better index ? or ...
Your table definition doesn't match your index or query - but I guess that means your thinking along the right lines, implicit conversion.
Match your variable datatype to your columns:
DECLARE @Ip NVARCHAR(40) = '94176016019';
SELECT TOP ( 1 )
IpRangeid
FROM dbo.IpRange WITH ( NOLOCK )
WHERE @Ip BETWEEN IpStart AND IpEnd;
sorry, this right, I edit the porst
March 9, 2017 at 3:48 am
Is there a reason why the CLUSTERED key is on the IDENTITY field?
Based purely on this query, you could look at changing the PRIMARY KEY to a NON-CLUSTERED one, and make the INDEX CLUSTERED.
You would have to also considder other queries that are run against this table.
CREATE TABLE dbo.[IpRange] (
[IpRangeid] [BIGINT] IDENTITY(1, 1) NOT NULL ,
[IpStartInt] [BIGINT] NULL,
[IpEndInt] [BIGINT] NULL,
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [PK__IpRange] PRIMARY KEY NONCLUSTERED ( [IpRangeid] ASC )
);
CREATE CLUSTERED INDEX [ix_IpEndIntIpStartInt]
ON [dbo].[IpRange] ([IpStartInt] ASC, [IpEndInt] ASC);
Alternatively, you may get better performance by including the output field in the index
CREATE NONCLUSTERED INDEX [ix_IpEndIntIpStartInt]
ON [dbo].[IpRange] ([IpStartInt] ASC, [IpEndInt] ASC)
INCLUDE ([IpRangeid]);
As a third option, (I have not been able to confirm/deny this) ... Perhaps some of the more knowledgeable folks could confirm/deny the following.
I have been led to believe that this
WHERE @Ip >= IpStartInt AND @Ip <= IpEndInt
performs better than this
WHERE @Ip BETWEEN IpStartInt AND IpEndInt
March 9, 2017 at 3:52 am
farax_x - Thursday, March 9, 2017 1:48 AM
but its extremely slow ! anyone has better idea ? better index ? or ...
what do you mean by "slow".... how long does this take?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 9, 2017 at 8:34 am
You don't need an identity column on this table: it just corrupts the table design and performance (and is a moderate waste of disk and RAM as well). There really is no law that "every" table must have an IDENTITY property -- no, really, there isn't π
CREATE TABLE dbo.[IpRange]
(
[IpStartInt] [BIGINT] NULL, /*can this really be NULL??; if not, you can make this part of a PK*/
[IpEndInt] [BIGINT] NULL, /*can this really be NULL??*; if not, you can make this part of a PK/
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [CK__IpRange] CHECK(IpStartInt <= IpEndInt)
);
CREATE UNIQUE CLUSTERED INDEX CL__IpRange ON dbo.IpRange ( IpStartInt, IpEndInt ) WITH ( FILLFACTOR = 99 ) ON [PRIMARY];
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 16, 2017 at 7:02 pm
This was removed by the editor as SPAM
March 19, 2017 at 5:26 pm
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2017 at 6:51 pm
ScottPletcher - Thursday, March 9, 2017 8:34 AMYou don't need an identity column on this table: it just corrupts the table design and performance (and is a moderate waste of disk and RAM as well). There really is no law that "every" table must have an IDENTITY property -- no, really, there isn't π
CREATE TABLE dbo.[IpRange]
(
[IpStartInt] [BIGINT] NULL, /*can this really be NULL??; if not, you can make this part of a PK*/
[IpEndInt] [BIGINT] NULL, /*can this really be NULL??*; if not, you can make this part of a PK/
[LastUpdatedate] [DATETIME] NULL ,
CONSTRAINT [CK__IpRange] CHECK(IpStartInt <= IpEndInt)
);CREATE UNIQUE CLUSTERED INDEX CL__IpRange ON dbo.IpRange ( IpStartInt, IpEndInt ) WITH ( FILLFACTOR = 99 ) ON [PRIMARY];
Again, "It Depends". If the leading column isn't ever-increasing, you can take a hell of a hit on inserts while all the page splits occur.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply