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