Searching between two BIGINT

Question

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?

It looks like you have some implicit conversions to BIGINT going on. Make sure @IP is defined as BIGINT. — Scott Hodgin - Retired, Oct 24 '17 at 11:15
It is a System.Int64 called from c# code, is it correct right? — Jurgen Harms, Oct 24 '17 at 11:19
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 '17 at 12:11
Nothing wrong with the query, just wanted to reduce timing due that it's being called hundred of times by second. I have a tool mesuring query times and it says this one is the slowest one (about 8ms) vs others I have that are less than 1 ms — Jurgen Harms, Oct 24 '17 at 13:21
@JurgenHarms alright, crazy question: what are queries waiting on for those 8ms? — Erik Darling, Oct 24 '17 at 13:25
Try the index with network_last_integer as a leading column:
CREATE NONCLUSTERED INDEX [IPS_inv_include_connection] ON [dbo].[IPS_INTEGERS] ( [network_last_integer] ASC, [network_start_integer] ASC ) INCLUDE ([connection_type])

it can make difference similarly to intervals problem — sepupic, Oct 24 '17 at 14:24
Did you just create it and server used it instead of your index or it was not chosen by server and you forced it using a hint? — sepupic, Oct 24 '17 at 15:30
How fast is the disk I/O? Are you running from solid state? How much of the table in question is loaded into memory? — datagod, Oct 24 '17 at 16:05
You may be able to get a little better performance by throwing this into an In-Memory OLTP Table if you're running SQL 2014 or later, but honestly, aside from the implicit conversion against the parameter it doesn't look like a poorly performing plan at all. — John Eisbrener, Oct 24 '17 at 18:14
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 '17 at 03:44
Convert_implication can be removed DECLARE @ip BIGINT = 1234 — Tapakah Ua, Oct 25 '17 at 06:24
any conclusion ? i think querying 360k rows repeatedly every second is itself wrong.plz update. — KumarHarsh, Dec 21 '17 at 05:58

Searching between two BIGINT

0 Answers0