By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @KeysToSelect TABLE(Key1 INT) INSERT INTO @KeysToSelect VALUES(139743),(139878),(139953)
DECLARE @BigView TABLE(Key1 INT, Key2 INT, Area DECIMAL(18,2), BuildingType NVARCHAR(25))
INSERT @BigView VALUES
(100, NULL, 0,''),
(101, NULL, 0,''),
(200, NULL, 0,''),
(201, NULL, 0,''),
(139743, NULL, 8475.00,'Industrial'),
(139743, NULL, 593.00, 'Office'),
(139744, NULL, 0,''),
(139745, NULL, 0,''),
(139746, NULL, 0,''),
(139747, NULL, 0,''),
(139878, NULL, 1268.00,'Office'),
(139878, NULL, 15534.00,'Warehouse'),
(139879, NULL, 0,''),
(139880, NULL, 0,''),
(139881, NULL, 0,''),
(139953, 6, 20000.00,'Warehouse'),
(139953, 14,20000.00,'Office'),
(149956, NULL, 0,''),
(149957, NULL, 0,''),
(149958, NULL, 0,'')
;WITH OveralOrderInData AS
(
SELECT
--!!!! Cant't SELECT * FROM @BigView Here because it hits a 30 second timeout limit. This is what the solve is for.
--It would be easy just to return the data with a rank, however, in this case going into a skinny slice of the data to get overall count
--and joining that against a limited subset of the larger viuew returns in under s second
--Grabbing the 2 keys and ranking takes less than a second, lots of left joins and views calling views
ROW_NUMBER() OVER (ORDER BY vw.Key1 ASC, vw.Key2 ASC) AS OrderInData,
CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE SUM(CASE WHEN Keys.Key1 IS NULL THEN NULL ELSE 1 END) OVER (ORDER BY vw.Key2 ASC,vw.Key1 ASC ROWS UNBOUNDED PRECEDING) END AS MatchedOrderInSearchKeys,
CASE WHEN Keys.Key1 IS NULL THEN 0 ELSE 1 END AS IsMatched,
vw.Key1,
| RealOrderInData | OrderInData | MatchedOrderInSearchKeys | Key1 | Key2 | Area | BuildingType | DistributeOrder |
|---|---|---|---|---|---|---|---|
| 5 | 5 | 1 | 139743 | null | 8475.00 | Industrial | 1 |
| 6 | 5 | 1 | 139743 | null | 593.00 | Office | 1 |
| 11 | 11 | 3 | 139878 | null | 1268.00 | Office | 1 |
| 12 | 11 | 3 | 139878 | null | 15534.00 | Warehouse | 1 |
| 16 | 16 | 5 | 139953 | 6 | 20000.00 | Warehouse | 1 |
| 17 | 17 | 6 | 139953 | 14 | 20000.00 | Office | 1 |
Warning: Null value is eliminated by an aggregate or other SET operation.