Избежать большого количество подзапросов SQL — sql sql-server

1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд
Загрузка...

Вопрос:


В WinForms приложении существует dataGridView, который заполняется около 1000 строк и 107 столбцов при входе в приложение из БД MS SQL.
Хранимая процедура, большинство данных возвращает через подзапросы следующего вида:

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

Количество выше приведенных под запросов около 50 шт. Отличаются они фильтром sort = N. Прошу, подскажите способ утилизации такого количества подзапросов, цель — увеличить скорость ответа. Если бы задачу решал в Report Builder при создании отчета, фильтровал бы данные в столбцах инструментами Report Builder без использования подзапросов. На данный момент при количестве строк 986 столбцов 107, время выполнения около минуты. Все поля попадающие под фильтр индексированы.
Запрос из процедуры за исключением нескольких полей :

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

Автор вопроса: Winteriscoming

Скорее всего тормоза из-за этого

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

Замените на

WHERE dateInsert >= ‘2017-11-13’ AND dateInsert < ‘2017-11-14’

и почитайте

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

Источник

Вам также может быть интересно:

Использование вложенных маршрутов в React Router — javascript reactjs react-router
Вопрос: Для организации маршрутов в приложении использую React Router. <Route path="/" component={...}> <IndexRoute component={...}/> <Route path="user/:userId" component={...}> ...
Как с помощью Retrofit 2.0 отправить данные в JSON на сервер и получить ответ? — java android retrofit
Вопрос: Только начал читать про Retrofit 2.0 до этого использовал HttpURLConnection. Как я работаю с HttpURLConnection, формирую Json перевожу его в byte, ставлю header в ...
Не приходят push уведомления. FCM — android firebase android-notification
Вопрос: Появилась необходимость реализовать push уведомления. Прописал в манифесте сервис: <service android:name=".MyFirebaseMessagingService"> <intent-filter> ...
Принцип браузерной игры в линукс терминале — java linux terminal
Вопрос: Наткнулся на Java библиотеку CHARVA. И хотел бы уточнить у знающих людей, возможно ли на основе данной библиотеки сделать программу по принципу браузерной игры, но ...
Мерцание заблокированного экрана при выключенной подсветке в Debian 8 Gnome 3 — linux debian экран
Вопрос: На ноутбуке с Debian 8 Jessie и Gnome 3 имеется следующая проблема. При выключенном заблокированном экране сквозь него можно наблюдать, как весь экран становится белым, ...
Создание WCF клиента на готовый SOAP web сервер — c# wcf
Вопрос: Доброго времени суток. Появилась задача опрашивать web сервер с клиента на котором планируется написать WCF клиентскую часть. Информации про сервер очень мало (не знаю платформу ...
Безопасно ли удалить файл логов general_log.txt? — mysql
Вопрос: При выполнении запроса со вставкой данных большого объёма SQLyog начал вылетать с ошибкой: not enough memory application terminated В связи с этим я решила ...
Callback функции создания таблицы mysql в nodejs — mysql node.js callback
Вопрос: Есть функция, которая при запуске создает базу даных, function showDb() { pool.query("show databases like 'bt' ",function (err, ...
Как создать Adapter с неограниченным количеством строк и с неограниченным разным количеством столбцов в каждой строке — java android
Вопрос: Как создать Adapter с неограниченным количеством строк и с неограниченным разным количеством столбцов в каждой строке Автор вопроса: Salut Amigo Источник
Не могу передать байтовый массив в контроллер — c# asp.net-mvc entity-framework
Вопрос: У меня изображения храняться в бд в формате байтового массива, через форич отлично все выводит, но когда я хочу открыть страницу для работы с ...
proguard release error — java android mvp
Вопрос: Включил в проекте proguard, apk собирается, все хорошо, но приложение не работает) Proguard-rules.pro -keepattributes InnerClasses -keepattributes EnclosingMethod -keepattributes *Annotation* -dontoptimize # Keep Butterknife -keep class butterknife.** { *; } -dontwarn butterknife.internal.** -keep ...
Не отрабатывает page:update — javascript ruby-on-rails
Вопрос: Есть мой учебный проект на ruby. Делаю редактирование объектов с помощью JS. Сейчас работает так: Редактирую первый раз - всё нормально. Не обновляя страницу, ...
Как найти определенный символ в строке и удалить значение после него (и вместе с ним) Jquery — javascript html jquery
Вопрос: Здравствуйте, есть определенный набор строк, типа "L / Красный / 12345", как можно на странице найти их, и вырезать из них все что находится ...
Почему не работает wildcard module declaration? — typescript
Вопрос: Почему не работает такой способ декларации: declare module "*!text" {} ? Цель - использовать контент файла в переменной: import layout = require("/js/views/layouts/wnd.html!text"); или так: import layout from "/js/views/layouts/wnd.html!text"; Если ...
Как прервать 3rd-party код? — c# многопоточность .net-core
Вопрос: Есть 3rd-party код из библиотеки который "зависает" в ожидании где-то в работе с сетью. CancellationToken поддержки нет, таймаутов нет. Запускаю я его через: Task.Run(() => ...

Оставьте ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *