Avoid a large number of SQL subqueries

Question:

There is a dataGridView in WinForms application, which is filled with about 1000 rows and 107 columns when entering the application from MS SQL . Stored procedure, most of the data is returned through subqueries of the following form:

ISNULL(
    (SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1),
    (SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1)
    ) as t2

The quantity of the above mentioned requests is about 50 pieces. They differ in the sort = N filter. Please, tell me a way to dispose of such a number of subrequests, the goal is to increase the response speed. If I solved the problem in Report Builder when creating a report, I would filter the data in columns using Report Builder tools without using subqueries. At the moment, with a row count of 986 columns of 107, the execution time is about a minute. All fields falling under the filter are indexed.
Request from procedure except for a few fields:

select distinct 
    dataRegistr.id,
    ISNULL(tripStatus.id,0) as id_Status,
    dataRegistr.shiftCode as shiftCode2,
    dataRegistr.dateDocument,
    0 as tripList,
    ISNULL(dataRegistr.transportKg, 0.00) as transportKg,
    ISNULL(dataRegistr.transportPal,0) as transportPal,
    ISNULL(dataRegistr.ref,0) as ref,
    ISNULL(dataRegistr.addPoint,0) as addPoint,
    ISNULL(dataRegistr.tarifPerHour, 0.00) as tarifPerHour,
    ISNULL(dataRegistr.tarifPerKm, 0.00) as tarifPerKm,
    ISNULL(dataRegistr.hourToPay, 0.00) as hourToPay,
    ISNULL(dataRegistr.kmToPer, 0.00) as kmToPer,
    ISNULL(dataRegistr.allSum, 0.00) as allSum,
    ISNULL(newRegistr.addressName,0) as p1,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1),0) as p2,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 2),0) as p3,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 3),0) as p4,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 4),0) as p5,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 5),0) as p6,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 6),0) as p7,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 7),0)as p8,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 8),0) as p9,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 9),0) as p10,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 10),0) as p11,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 11),0) as p12,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 12),0) as p13,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 13),0) as p14,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 14),0) as p15,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 15),0) as p16,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 16),0) as p17,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 17),0) as p18,
    ISNULL((SELECT top 1 ISNULL(CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end,0) from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 18),0) as p19,
    ISNULL((SELECT top 1 CASE WHEN addressCode IN (select region.regionCode from region) THEN addressName ELSE addressCode end from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 19),0) as p20,
    ISNULL(newRegistr.departedTime, newRegistr.arrivedTime) as t1,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1)) as t2,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 2),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 2)) as t3,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 3),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 3)) as t4,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 4),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 4)) as t5,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 5),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 5)) as t6,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 6),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 6)) as t7,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 7),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 7)) as t8,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 8),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 8)) as t9,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 9),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 9)) as t10,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 10),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 10)) as t11,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 11),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 11)) as t12,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 12),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 12)) as t13,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 13),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 13)) as t14,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 14),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 14)) as t15,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 15),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 15)) as t16,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 16),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 16)) as t17,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 17),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 17)) as t18,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 18),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 18)) as t19,
    ISNULL((SELECT top 1 departedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 19),(SELECT top 1 arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 19)) as t20,
    ISNULL(newRegistr.arrivedTime, newRegistr.departedTime) as tv1,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 1)) as tv2,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 2),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 2)) as tv3,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 3),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 3)) as tv4,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 4),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 4)) as tv5,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 5),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 5)) as tv6,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 6),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 6)) as tv7,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 7),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 7)) as tv8,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 8),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 8)) as tv9,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 9),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 9)) as tv10,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 10),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 10)) as tv11,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 11),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 11)) as tv12,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 12),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 12)) as tv13,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 13),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 13)) as tv14,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 14),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 14)) as tv15,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 15),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 15)) as tv16,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 16),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 16)) as tv17,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 17),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 17)) as tv18,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 18),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 18)) as tv19,
    ISNULL((SELECT top 1  arrivedTime from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 19),(SELECT top 1 departedTime  from newRegistr WHERE shiftCode = dataRegistr.shiftCode and sort = 19)) as tv20,
    dataRegistr.tripNumber,
    dataRegistr.region,
    dataRegistr.allDistance,
    dataRegistr.allHour,
    dataRegistr.allDistanceWithOrders,
    dataRegistr.successTKm,
    dataRegistr.managerName,
    dataRegistr.dateInsert,
    dataRegistr.registrName,
    dataRegistr.idTariffType,
    dataRegistr.isReturns
from dataRegistr
JOIN newRegistr ON dataRegistr.shiftCode = newRegistr.shiftCode and newRegistr.sort = 0
LEFT JOIN tripStatus ON dataRegistr.isTax = tripStatus.id
WHERE  CAST(dateInsert as date) = '2017-11-13' 
ORDER by dateInsert DESC

Answer:

Most likely the brakes are due to this

WHERE CAST (dateInsert as date) = '2017-11-13'

Change to

WHERE dateInsert> = '2017-11-13' AND dateInsert <'2017-11-14'

and read

http://www.interface.ru/home.asp?artId=10279

Scroll to Top