3

I have a table defined as:

CREATE TABLE [dbo].[IPS_INTEGERS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [network_start_integer] [bigint] NULL,
    [network_last_integer] [bigint] NULL,
    [connection_type] [varchar](50) NULL
)

It has about 360k registers and querying it repeatedly many times by second. I need the following query optimized; currently it completes in 8 ms and I'd like to improve that.

SELECT connection_type 
FROM   IPS_INTEGERS 
WHERE  network_start_integer <= @IP 
AND    network_last_integer >= @IP

The @IP value has the C# System.Int64 data type.

I have tried different indexing approaches but none giving the the expected optimization.

Currently using the following index:

CREATE NONCLUSTERED INDEX [IPS_include_connection] ON [dbo].[IPS_INTEGERS]
(
    [network_start_integer] ASC,
    [network_last_integer] ASC
) INCLUDE ([connection_type])

Here's the link to the estimated query plan.

Any suggestions?

Improve this question
17
  • 1
    It looks like you have some implicit conversions to BIGINT going on. Make sure @IP is defined as BIGINT.
    – Scott Hodgin - Retired
    Oct 24, 2017 at 11:15
  • It is a System.Int64 called from c# code, is it correct right?
    – Jurgen Harms
    Oct 24, 2017 at 11:19
  • 1
    Can you describe what isn't happening that you'd like to happen? I've read your question and the comments and can't quite figure out what success looks like to you.
    – Erik Darling
    Oct 24, 2017 at 12:11
  • 3
    @JurgenHarms alright, crazy question: what are queries waiting on for those 8ms?
    – Erik Darling
    Oct 24, 2017 at 13:25
  • 1
    what is the expected performance? Try to change the datatype for [network_start_integer] and [network_end_integer] from bigint to int and see if there is any deference
    – sebeid
    Oct 25, 2017 at 3:44

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.