Select random records for each users using SQL server in efficient way

I have used the below query to select 5 random records for each shopperId and it is working fine but it is taking time to execute for large number of records (more than 100k records). So is there any way to improve the performance over large amount of data?

  SELECT *  FROM (SELECT  
     ROW_NUMBER() OVER (PARTITION BY ShopperID ORDER BY NEWID() DESC )
     AS [Rank] ,
  f.ProductDisplayID,  
  f.ShopperID,  
  f.CreateTS,  
  f.FavoriteID
 FROM  
  #Favorite f )OBJ 

   WHERE OBJ.[Rank]<=5
   order by ShopperID


Dummy data:


CREATE TABLE #Favorite(
    [FavoriteID] [bigint] IDENTITY(1,1) primary key clustered NOT NULL,
    [ProductDisplayID] [bigint] NOT NULL,
    [ShopperID] [char](32) NOT NULL,
    [CreateTS] [datetime] NOT NULL
    )
GO
SET IDENTITY_INSERT #Favorite ON 
GO
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (1, 5209, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-03-16T14:17:28.367' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (2, 2318, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:36.030' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (3, 5370, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:45.043' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (4, 106, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:46.220' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (5, 4014, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:47.120' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (6, 4122, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:48.060' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (7, 4127, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:49.960' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (8, 5372, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:50.877' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (9, 5307, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:51.630' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (10, 5345, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:52.537' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (11, 5358, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:53.477' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (12, 3506, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:55.600' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (13, 5308, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:56.467' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (14, 3994, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:57.400' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (15, 5305, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:58.290' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (16, 2491, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:42:59.030' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (17, 1271, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:43:00.840' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (18, 1275, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:43:01.887' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (19, 701, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:43:02.683' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (20, 5310, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:43:03.507' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (21, 41, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-07T17:43:04.533' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (22, 5314, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:51.937' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (23, 253, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:52.840' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (24, 312, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:53.693' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (25, 313, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:55.247' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (26, 3462, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:56.067' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (27, 3471, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:57.360' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (28, 5265, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:58.127' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (29, 5312, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:44:59.180' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (30, 2119, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:00.097' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (31, 2913, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:00.950' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (32, 4562, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:02.370' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (33, 2289, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:03.110' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (34, 2116, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:04.730' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (35, 3308, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:07.000' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (36, 2103, N'5F1BF5D1679E4724BC02BA2FB91AAB4E', CAST(N'2022-04-07T17:45:08.173' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (37, 2606, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:07:59.823' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (38, 5147, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:08:00.767' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (39, 2584, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:08:01.827' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (40, 975, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:08:02.680' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (41, 1814, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:08:03.427' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (42, 1066, N'64E9A938E3384F3DBC8AE747E99A754A', CAST(N'2022-04-07T18:08:15.033' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (43, 1789, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:11.983' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (44, 29, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:12.793' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (45, 4866, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:13.693' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (46, 3317, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:14.580' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (47, 607, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:15.327' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (48, 4933, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:17.163' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (49, 4350, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:18.133' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (50, 3189, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:19.043' AS DateTime))
INSERT #Favorite ([FavoriteID], [ProductDisplayID], [ShopperID], [CreateTS]) VALUES (51, 477, N'F46AF3C6BDBE4427897A88B043A0DC43', CAST(N'2022-04-26T22:21:20.010' AS DateTime))

Leave a Comment