Index query for large data in SQL Server

Question

I am working with some traffic data in VB.net using SQL Server. My table name is [Sim VehRecord], columns are:

Record ID
Simulation Sec(s)
Vehicle No
Link No
Lane No
Position (m)
Speed (km/hr)

Basing on the columns 2, 3 and 6, I would like to fill column 7 for each row.

Sample of traffic data shown here:

Record ID     Sim Sec(s)  Vehicle No Link No Lane No Position(m)  Speed (km/hr)  
-------------------------------------------------------------------------------
        1        0.80        2        74        1        13.42    
        2        0.80        3        74        2        12.88    
        3        0.80        4        2         1        2.90    
        4        1.00        1        73        1        17.97  
        5        1.00        2        74        1        17.73  
        6        1.00        3        74        2        17.22  
        7        1.00        4        2         1        7.22  
        8        1.20        1        73        1        22.42  
        9        1.20        2        74        1        22.04  
        10       1.20        3        74        2        21.57  
        11       1.20        4        2         1        11.54

I have used following code:

CREATE CLUSTERED INDEX IX_SimVehRecord_VehNo
ON [Sim VehRecord] ([Sim Time (sec)] ASC, [Position (m)] ASC)

UPDATE [Sim VehRecord]
SET [Speed (km/hr)] = (SELECT CONVERT(DECIMAL(7, 2), 3.6 * COALESCE (([Sim VehRecord].[Position (m)] - (SELECT TOP 1 sv.[Position (m)])) / NULLIF(([Sim VehRecord].[Sim Time (sec)] - sv.[Sim Time (sec)]), 0), 0)) 
                       FROM [Sim VehRecord] sv 
                       WHERE sv.[Veh No] = [Sim VehRecord].[Veh No]
                         AND sv.[Position (m)] < [Sim VehRecord].[Position (m)]
                         AND sv.[Sim Time (sec)] < [Sim VehRecord].[Sim Time (sec)])

But I always get an error:

Subquery returned more than 1 value.

Any assistance will be highly appreciated.

Regards.

Error is quite clear... You subquery which used in the update statement returning more than one row and you update set clause expecting 1 row to be returned. You can run select statement separately to see the result — Mukesh Arora
– Mukesh Arora, Commented Dec 12, 2019 at 4:20

Frederic · Accepted Answer · 2019-12-12 19:45:31Z

1

your query realy return more than one result in 8, 9, 10, 11.

you need the position of the vehicle and the previous position to calc the speed, right?

so i belive you need something like

with yourTable as (select * 
from (
values (1, 0.80, 2, 74,  1, 13.42),
(2, 0.80, 3, 74,  2, 12.88),
(3, 0.80, 4, 2,   1, 2.90),
(4, 1.00, 1, 73,  1, 17.97),
(5, 1.00, 2, 74,  1, 17.73),
(6, 1.00, 3, 74,  2, 17.22),
(7, 1.00, 4, 2,   1, 7.22),
(8, 1.20, 1, 73,  1, 22.42),
(9, 1.20, 2, 74,  1, 22.04),
(1, 1.20, 3, 74,  2, 21.57),
(1, 1.20, 4, 2,   1, 11.54)
) V (ID, Sec, Vehicle, Link, Lane, Position)),

pos_and_previous_pos as (
select 
Vehicle, Lane,ID, Sec, Position, 
LAG(Position) over (partition by Vehicle, lane order by sec asc) as previous_pos,
LAG(Sec) over (partition by Vehicle, lane order by sec asc) as previous_sec
from yourTable
)

select id, vehicle, sec, position, 3.6 * isnull((Position - previous_pos) / (Sec - previous_sec), 0) kmh
from pos_and_previous_pos
order by vehicle, lane, sec

i did partitioned by lane too, i am not sure if it matters in your use case.

see a working demo here:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=fdc5982119a56cd6730635f076c286bc

I guess your update would be something like

with SimVehRecord as (
select 
[Record ID], [Simulation Sec(s)], [Position (m)], 
LAG([Position (m)]) over (partition by [Vehicle No]  order by [Simulation Sec(s)] asc) as previous_pos,
LAG([Simulation Sec(s)]) over (partition by [Vehicle No] order by [Simulation Sec(s)] asc) as previous_sec
from [Sim VehRecord]
)
select *, (3.6 * isnull(([Position (m)] - previous_pos) / ([Simulation Sec(s)] - previous_sec), 0)) as kmh from SimVehRecord;

working fiddle update sample:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ee0c496d930117d632f96fd26440a435

be sure to have a index [Vehicle No], [Simulation Sec(s)], [Position (m)]

let me know if it helped you..

edited Dec 12, 2019 at 19:45

answered Dec 12, 2019 at 4:23

Frederic

1,0286 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Tariq Over a year ago

Thanks Frederic.. I was taking long time for understanding the logic. Note that, i give more emphasis on processing time. Because, i have almost 5 million data. Previously i applied recursive logic which used to take almost 8 min time to process. With your guidelines i came up the following solution:

Tariq Over a year ago

``` UPDATE [Sim VehRecord] SET [Speed (km/hr)] = (SELECT FORMAT(3.6* COALESCE (([Sim VehRecord].[Position (m)] - LAG (sv.[Position (m)],1,0) OVER (PARTITION BY sv.[Veh No] ORDER By sv.[Sim Time (sec)] ASC))/NULLIF(([Sim VehRecord].[Sim Time (sec)] - LAG (sv.[Sim Time (sec)],1,0) OVER (PARTITION BY sv.[Veh No] ORDER By sv.[Sim Time (sec)] ASC)),0),0),'N2','en-us') From [Sim VehRecord] sv Where sv.[Veh No] = [Sim VehRecord].[Veh No] And sv.[Position (m)] < [Sim VehRecord].[Position (m)] And sv.[Sim Time (sec)] < [Sim VehRecord].[Sim Time (sec)]) ``` But error " returned <1 value"

Frederic Over a year ago

@Tariq see this fiddle dbfiddle.uk/… a working update in a table with the name and columns similar yours (sorry updated the fiddle without lane in partition)

Frederic Over a year ago

my update was wrong did miss the from .... inner join to_cte... sorry I was sleepy when wrote the answer

Frederic Over a year ago

@Tariq, Do you already has indexes with columns [vehicle no], [sim time(sec)], [position (m)], right?

|

Collectives™ on Stack Overflow

Index query for large data in SQL Server

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related