T-SQL how to select previous row using indexes

Question

T-SQL how to select previous row using indexes

WazHu 1

Hi,

I've been trying to get the prev/next rows based on a current row using the primary key. That is I only want two rows, the previous to the current and the next. I have a huge DB table with 2 million rows and it takes like 30 seconds for the below query to run. It looks like it needs to run a query through the entire database to get the lag value etc. I have an index with all the columns in it for the order so how do I get SQL Server basically to find the current row using my primary key, use those values in the index values and find the previous and next rows (without having to go through an entire database/query). In theory SQL Server should know this right, it has the columns already sorted in order so it should be able to find that position in the index and give me the next/prev. Here is my query: (AddressID is the primary key) but I want the next/previous street, street number etc (that is the next physical address)

declare @previousID int; 
declare @nextID int; 

 select @previousID =  isnull(x.PreviousAddressID, 0) 
   , @nextID = isnull(x.NextAddressID, 0) 
 from 
 ( 
 select a.AddressID 
   , lag(a.AddressID) over (order by ElectorateID 
  , StreetID 
  , iStreetNumber 
  , StreetNumber 
  , iFlatNumber 
  , FlatNumber 
   ) PreviousAddressID 
   , lead(a.AddressID) over (order by ElectorateID 
   , StreetID 
   , iStreetNumber 
   , StreetNumber 
   , iFlatNumber 
   , FlatNumber 
    ) NextAddressID 
 from dbo.[Address] a 
 where ( 
   a.[ElectorateID] = @ElectorateID 
   and a.[Old] = @Old 
   ) 
 ) x 
 where x.AddressID = @AddressID

Viorel 122.5K

Did you get the same results in this case?

select 
    @previousID = isnull( lag expression..., 0),
    @nextID = isnull( lead expression..., 0) 
from dbo.[Address]
where AddressID = @AddressID 
and [ElectorateID] = @ElectorateID 
and [Old] = @Old

LiHong-MSFT 10,056 Reputation points

2021-12-21T08:31:25.967+00:00

Hi，@WazHu
How about create a virtual table,and have a try on insert or update.

WazHu 1

Yeah unfortunately, say this, always returns null.

select lag(AddressID) over (order by ElectorateID
                              , StreetID
                              , iStreetNumber
                              , StreetNumber
                              , iFlatNumber
                              , FlatNumber
                           )
from dbo.[Address] with (nolock)
where AddressID          = @AddressID
      and [ElectorateID] = @ElectorateID
      and [Old]          = @Old;

1 answer

Your answer

Viorel 122.5K Reputation points

2021-12-21T06:49:26.007+00:00

Did you get the same results in this case?

select @previousID = isnull( lag expression..., 0), @nextID = isnull( lead expression..., 0) from dbo.[Address] where AddressID = @AddressID and [ElectorateID] = @ElectorateID and [Old] = @Old
LiHong-MSFT 10,056 Reputation points

2021-12-21T08:31:25.967+00:00

Hi，@WazHu
How about create a virtual table,and have a try on insert or update.
WazHu 1 Reputation point

2022-01-04T02:05:24.477+00:00

Yeah unfortunately, say this, always returns null.

select lag(AddressID) over (order by ElectorateID , StreetID , iStreetNumber , StreetNumber , iFlatNumber , FlatNumber ) from dbo.[Address] with (nolock) where AddressID = @AddressID and [ElectorateID] = @ElectorateID and [Old] = @Old;

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Adding an index on (Old, ElectorateID, StreetID, iStreetNumber, StreetNumber, iFlatNumber, FlatNumber) INCLUDE (AddressId) could help.

WazHu 1 Reputation point

2022-01-04T02:41:55.783+00:00

Unfortunately this only helped a little bit. Brought the query down from 55 seconds to 27 seconds, although that could have just been caching.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-04T22:22:53.127+00:00

I guess then that we need to ask you to provide the actual execution plan. The forum software will not agree to attach .sqlplan files, but just change the extention to .xml or .txt. Or upload the plan on http://www.pastetheplan.com.

Share via

T-SQL how to select previous row using indexes

1 answer

Your answer