I have a table defined as:

    [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 
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:

    [network_start_integer] ASC,
    [network_last_integer] ASC
) INCLUDE ([connection_type]) 

Here's the link to the estimated query plan.

Any suggestions?

  • 1
    It looks like you have some implicit conversions to BIGINT going on. Make sure @IP is defined as BIGINT. Oct 24, 2017 at 11:15
  • It is a System.Int64 called from c# code, is it correct right? 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. Oct 24, 2017 at 12:11
  • 3
    @JurgenHarms alright, crazy question: what are queries waiting on for those 8ms? 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


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.