QueryOptimization

  • 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 ...

  • farax_x - Thursday, March 9, 2017 1:48 AM

    Hi 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;

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • ChrisM@Work - Thursday, March 9, 2017 2:12 AM

    farax_x - Thursday, March 9, 2017 1:48 AM

    Hi 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

  • 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

  • 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

  • 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.

  • This was removed by the editor as SPAM

  • I don't know what's going on but the original post that Chris quoted had NVARCHAR(40) for the start and end "INTs".  Now, the original post has BIGINT and there don't appear to be any signs of editing on the original post.  How did THAT happen?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher - Thursday, March 9, 2017 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];

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply