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
@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])