Производительность SQL Server с большим запросом

Есть много вещей, которые можно сделать, чтобы сделать это быстрее. Если я предполагаю, что вам нужно выполнить эти UNION, вы можете ускорить запрос:

1. Caching the results, for example,
   • Can you create an indexed view from the whole statement ? Or there are lots of different WHERE conditions, so there'd be lots of indexed views ? But know that this will slow down modifications (INSERT, etc.) for those tables
   • Можете ли вы кэшировать его по-другому? Может в среднем слое?
   • Можно ли пересчитать заранее?
2. Make a covering index. Leading columns are columns form WHERE and then all other columns from the query as included columns
   • Note that a covering index can be also filtered but filtered index isn't used if the WHERE in the query will have variables / parameters and they can potentially have the value that is not covered by the filtered index (i.e., the row isn't covered)
3. ORDER BY will cause sort
   • If you can cache it, then it's fine - no sort will be needed (it's cached sorted)
   • В противном случае сортировка привязана к процессору (и к вводу-выводу, если не в памяти). Чтобы ускорить его, вы используете быструю сортировку? Разница в производительности между самой медленной и быстрой сортировкой может быть даже в 3 раза. Например, SQL_EBCDIC280_CP1_CS_AS, SQL_Latin1_General_CP1251_CS_AS, SQL_Latin1_General_CP1_CI_AS — одни из самых быстрых сопоставлений. Однако трудно давать рекомендации, если я не знаю нужных вам характеристик сопоставления.
4. Network
   • 'network packet size' for the connection that does the SELECT should be the maximum value possible - 32,767 bytes if the result set (number of rows) will be big. This can be set on the client side, e.g., if you use .NET and SqlConnection in the connection string. This will minimize CPU overhead when sending data from the SQL Server and will improve performance on both side - client and server. This can boost performance even by tens of percents if the network was the bottleneck
   • Используйте конечную точку с общей памятью, если клиент находится на SQL Server; в противном случае TCP/IP для лучшей производительности
5. General things
   • As you said, using isolation level read uncommmitted will improve the performance

...

Вероятно, вы не можете вносить изменения, кроме переписывания запроса и т. д., но на всякий случай добавьте больше памяти, если ее сейчас недостаточно, или используйте SQL Server 2014 в функциях памяти :-), ... наверняка поможет.

Есть слишком много вещей, которые можно было бы настроить, но трудно указать ключевые, если вопрос не очень конкретен.

Надеюсь это немного поможет

ну, вы не предоставили никакой статистики или примера времени выполнения любого выполнения, поэтому невозможно угадать, что медленно и действительно ли оно медленно. сколько данных в результирующем наборе? это может быть просто извлечение 100 000 строк, так как в результате это просто занимает время. если набор результатов из 10000 строк занимает 5 минут, да, определенно можно что-то посмотреть. поэтому, если у вас есть пример запроса, количество строк в результате и сколько времени потребовалось для выполнения пары с разными условиями, опубликуйте это. это поможет нам сравнить результаты.

Кстати, не используйте CTE, просто используйте обычный внутренний и внешний выбор запроса. убедитесь, что временная БД настроена правильно. LDF и MDF не настроены по умолчанию для увеличения на 10%. путем определенных проб и ошибок вы узнаете, насколько увеличивается журнал и временная БД для достоверности запросов диапазона, и на основе этого вы должны установить начальный размер и размер приращения MDF и LDF временной БД. для покрытого индекса фильтра включаемый столбец должен быть столбцом col1, col2 и co3, а не столбцом Date, если только Date также не находится в списке выбора.

как часто обновляются данные в исходных 35 таблицах? если максимум один раз в день или если все они получают обновления почти в одно и то же время, то индексированные представления могут быть возможным решением. но если исходные таблицы получают обновления более одного раза в день или они обновляются в любое время и не находятся в одной строке, то не думайте об индексированном представлении.

если дисковое пространство не является проблемой, в крайнем случае попробуйте проверить производительность, используя триггер для каждой таблицы 35. создайте новую таблицу для хранения окончательных результатов, как вы ожидаете от этого запроса на выборку. создайте триггер вставки/обновления/удаления в каждой таблице 35, где вы проверяете условия внутри триггера, и если да, то копируйте только ту же вставку/обновление/удаление в новую таблицу. да, вам понадобится столбец в новой таблице, который определяет, какие данные поступают из какой таблицы. поскольку столбец Date is Null-Able, вы не получаете всех преимуществ индекса в этом столбце, поскольку «в основном вы ищете WHERE Date is NULL». в новой таблице вы всегда делаете только запрос, где дата равна NULL, тогда даже не утруждайте себя созданием этого столбца, просто создайте столбцы BIT и другие столбцы col1, col2, col3 и т. д.... если вы приведете реальный пример своего запроса и объясните фактический столы, остальные детали можно уточнить позже.

Подсказки запроса или уровень изоляции помогут вам только в случае возникновения какой-либо блокировки. Если вы не возражаете против грязного чтения и есть блокировки во время выполнения, это может быть хорошей идеей.

Ключевой вопрос заключается в том, сколько данных соответствует предложению «Где», которое вам нужно использовать (ГДЕ BitColumn = 1 AND DateColumn IS NULL). Если отфильтрованное подмножество мало по сравнению с общим количеством строк, используйте индекс для обоих столбцов, BitColum и DateColumn, включая столбцы в предложении select, чтобы избежать операций «Поиск страниц» в вашем плане запроса.

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(BitColumn, DateColumn)
INCLUDE (col1, col2, col3)

Конечно, пространство, необходимое для индекса с закрытой фильтрацией, зависит от типа данных задействованных полей и количества строк, удовлетворяющих условиям WHERE BitColumn = 1 AND DateColumn IS NULL.

После этого я рекомендую использовать View вместо CTE:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE Some_Condition
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE Some_Condition
.
.
.
)

При этом ваш план запроса должен выглядеть как 35 небольших сканирований индекса, но если большая часть данных удовлетворяет пункту where вашего индекса, производительность будет аналогична сканированию 35 исходных таблиц, и решение не стоит того. .

Но вы говорите: «Каждая таблица имеет 5-10-битные столбцы и соответствующий столбец даты». Если вам нужно фильтровать, используя разные столбцы BitColum и разные столбцы DateColum, используйте столбец вычислений в своей таблице:

ALTER TABLE Table1 ADD ComputedFilterFlag AS
CAST(
    CASE WHEN BitColum1 = 1 AND DateColumn1 IS NULL THEN 1 ELSE 0 END +
    CASE WHEN BitColum2 = 1 AND DateColumn2 IS NULL THEN 2 ELSE 0 END +
    CASE WHEN BitColum3 = 1 AND DateColumn3 IS NULL THEN 4 ELSE 0 END
AS tinyint)

Я рекомендую вам использовать значение 2 ^ (X-1) для условия X (BitColumnX = 1 и DateColumnX НЕ NULL). Это позволит вам фильтровать, используя любую комбинацию этих критериев. Используя значение 3, вы можете найти все строки, которые выполняют условие Bit1, Date1 и Bit2, Date2. Любая комбинация условий имеет соответствующее значение ComputedFilterFlag, поскольку ComputedFilterFlag действует как растровое изображение условий. Если у вас менее 8 различных фильтров, вы должны использовать tinyint, чтобы сэкономить место в индексе и уменьшить количество необходимых операций ввода-вывода.

Затем используйте индекс над столбцом ComputedFilterFlag:

CREATE NONCLUSTERED INDEX IX_[Choose an IndexName]
ON TableName(ComputedFilterFlag)
INCLUDE (col1, col2, col3)

И создайте представление:

CREATE VIEW [Choose a ViewName]
AS
(
Select col1, col2, col3 FROM Table1 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
UNION ALL 
Select col1, col2, col3 FROM Table2 WHERE ComputedFilterFlag IN [Choose the Target Filter Value set]--(1, 3, 5, 7)
.
.
.
)

Таким образом, ваш индекс покрывает все условия, и ваш план запроса должен выглядеть как 35 небольших поисков по индексу.

Но это сложное решение, возможно, рефакторинг в схеме вашей таблицы может дать более простые и быстрые результаты.

Вы никогда не получите результатов в реальном времени из запроса union all по многим таблицам, но я могу рассказать вам, как я немного ускорился в подобной ситуации. Надеюсь, это поможет вам.

На самом деле вы можете запустить их все сразу, немного кодируя и проявляя изобретательность.

Вы создаете глобальную временную таблицу вместо обычного табличного выражения и не помещаете никаких ключей в глобальную временную таблицу, это только замедлит работу. Затем вы запускаете все отдельные запросы, которые вставляются в глобальную временную таблицу. Я делал это около сотни раз вручную, и это быстрее, чем запрос на объединение, потому что вы получаете запрос, работающий на каждом ядре процессора. Сложная часть — это механизм, позволяющий определить, когда отдельные запросы завершились самостоятельно для этой части, поэтому я делаю это вручную.