ГлавнаяРазноеСчетчик в sql запросе

Лучшая реализация таблицы "счетчик" в SQL Server. Счетчик в sql запросе


sql - Лучшая реализация таблицы "счетчик" в SQL Server

Я работаю с большой базой данных SQL Server и основывается на идее таблицы счетчиков для значений первичного ключа. Каждая таблица имеет строку в этой таблице счетчиков с именем PK и следующим значением, которое будет использоваться в качестве первичного ключа (для этой таблицы). Наш текущий метод получения значения счетчика выглядит примерно так:

BEGIN TRAN UPDATE CounterValue + 1 SELECT Counter Value COMMIT TRAN

Это хорошо работает с момента начала транзакции, а затем обновляет строку, блокирует строку/страницу/таблицу (уровень блокировки не слишком важен для этого раздела), пока транзакция не будет выполнена.

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

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

Еще одна попытка решить проблему - использовать блокировку приложения SQL Server для сериализации доступа к таблице/строке. Это хорошо в большинстве случаев, но имеет недостатки. Один из самых больших недостатков здесь также включает триггеры. Поскольку триггеры запускаются в контексте инициирующего запроса, блокировка приложения блокируется до тех пор, пока не будут завершены какие-либо родительские транзакции.

Так что я пытаюсь понять, это способ сериализации доступа к строке/таблице, которая может быть запущена из приложения или из SP/триггера, который никогда не будет выполняться в контексте родительской транзакции. Если родительская транзакция будет откат, мне не потребуется значение счетчика для откат. Имея всегда доступный, быстрый доступ к значению счетчика гораздо важнее, чем потеря нескольких значений счетчика, если родительская транзакция будет отменена.

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

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

EDIT - мы можем предположить, что SQL Server 2005+

qaru.site

Несколько счетчиков в одном SQL-запросе MS SQL Server

Я хочу видеть их количество в одном запросе. Как я могу это сделать? Благодарю.

Используйте Sub Queries! Изучите некоторые основы SQL https://blog.sqlauthority.com/

SELECT (SELECT COUNT(*) FROM [myDb].[dbo].[Properties] WHERE Bathtub is null) AS BathTub, (SELECT COUNT(*) FROM [myDb].[dbo].[Properties] WHERE Bathroom is null) AS Bathroom, (SELECT COUNT(*) FROM [myDb].[dbo].[Properties] WHERE Toilet is null) AS Toilet

Предупреждение Накладные расходы, связанные с ситуацией, если вас беспокоит производительность

Попробуйте это (это оракул, я полагаю, не отличается в mysql):

SELECT COUNT(CASE WHEN Bathtub IS NULL THEN 1 END) Bathtub, COUNT(CASE WHEN Bathroom IS NULL THEN 1 END) Bathroom, COUNT(CASE WHEN Toilet IS NULL THEN 1 END) Toilet from [myDb].[dbo].[Properties];

Или напишите так:

SELECT total-BT cnt_bt, total-BR cnt_br, total-TL cnt_tl FROM ( SELECT COUNT(*) total, COUNT(Bathtub) BT, COUNT(Bathroom) BR, COUNT(Toilet) TL FROM [myDb].[dbo].[Properties] ) subq

Попробуйте соединение с индивидуальным описанием

SELECT COUNT(*), 'Bathtub is null counts' desc FROM [myDb].[dbo].[Properties] WHERE Bathtub is null union SELECT COUNT(*),'Bathroom is null counts' desc FROM [myDb].[dbo].[Properties] WHERE Bathroom is null union SELECT COUNT(*), 'Toilet is null counts' desc FROM [myDb].[dbo].[Properties] WHERE Toilet is null

или

SELECT SUM(CASE WHEN Bathtub IS NULL THEN 1 ElSE 0 END) as Bathtub_count, SUM(CASE WHEN Bathroom IS NULL THEN 1 ElSE 0 END) as Bathroom_count, SUM(CASE WHEN Toilet IS NULL THEN 1 ElSE 0 END) as Toilet_count from [myDb].[dbo].[Properties]

Вы можете использовать эту функцию SQL SUM() . Как показано ниже:

SELECT SUM(IF(Bathtub IS NULL, 1, 0)) AS Bathtub_count, SUM(IF(Bathroom IS NULL, 1, 0)) AS Bathroom_count, SUM(IF(Toilet IS NULL, 1, 0)) AS Toilet_count FROM [ myDb ].[ dbo ].[ Properties ]

sqlserver.bilee.com

Счетчик в запросе

Вопрос: Планы выполнения запросов: предикаты (аргументы) в операторе Index Seek

Всем привет.

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

Вопрос в следующем. Есть простейший запрос, соединяющий две таблички (Employee и Address) внутренним соединением и выбирающий из них некоторое количество полей. Есть отбор по одному из полей таблицы Employee. Одна табличка без индексов вообще (Employee), другая (Address) содержит индекс по внешнем ключу, по которому идет соединение (индекс по полю EmployeeID).

SELECT employee.lastName, address.City FROM person.employee INNER JOIN person.address ON employee.EmployeeID = address.EmployeeID where employee.LastName = 'Иванов'

План запроса тоже простой. СУБД делает table scan по первой табличке по условию из отбора и index seek по второй (по ключу из первой таблицы), затем СУБД "добирает" результат с помощью RID Lookup и "собирает" все в кучу с помощью пары Nested Loops.

Интересует начало плана выполнения запроса - table scan первой таблицы и index seek по второй. Совсем точно - интересует index seek.

У него в аргументах (предикатах) написано следующее:

SEEK:([my_db].[Person].[Address].[EmployeeID]=[my_db].[Person].[Employee].[EmployeeID]) ORDERED FORWARD

В графическом плане выглядит примерно так же, только еще функция scalar operator добавляется после знака равенства.

При этом и в графическом, и в текстовом плане выполнения запроса table scan и index seek находится на одном уровне.

Что физически делает СУБД во время index seek? Все-таки использует ключи из оператора table scan и уже по ним делает поиск? Или просто получает все возможные ключи из первой таблицы и делает поиск по ним, а "отсекает" лишнее уже во время nested loops? Если первое - почему операции находятся на одном уровне в плане, ведь первая должна предшествовать второй, по идее?

Ответ: По моим наблюдения странное поведение планировщика происходит в первую очередь при изменении статистики. Выражается это в том что после пересчета с full scan и очистке хэша планов выполнения запросов ситуация меняется. Но также замечал что видимо сервер анализирует объем доступной(по сегментам) памяти. Выражалось это например при падении счетчика "время жизни страницы"(это скорее всего косвенный счетчик) оптимизатор без видимых причин начинал использовать преимущественно(на определенных типах запросов) нестед лупс вместо хэш джоина. Были мысли эту ситуацию смоделировать но как то лень. Да и главное как мы на это можем повлиять? Гарантированный способ - явно приклеивать xml плана выполнения. Если есть вырожденные случаи в селективности то явно в клиенте анализировать и подкладывать другой план. По моему опыту это не нужно для всех запросов , обычно их набирается 5-10ть на всю систему.

forundex.ru

sql - Использование функции счетчика в запросе на вставку

Хорошо, вы были довольно скромны со спецификой, поэтому мне пришлось угадать некоторые из вашей логики, но если я последую вашей логике, что ваша функция получит наибольшую запись + 1, вы все равно сможете это сделать в наборе на основе Способ:

Сначала я объясню с помощью SELECT, который затем должен быть адаптирован к UPDATE, по сути, проблема, с которой вы сталкиваетесь, заключается в том, что MaxRecord + 1 одинаково для всех строк, как это было бы со следующим SELECT:

SELECT t.ID, t.MyColumn, t.MyParamter, MaxRecord = MaxT.MyColumn + 1 FROM MyTable AS t INNER JOIN ( SELECT MyParameter, MyColumn = MAX(MyColumn) FROM MyTable WHERE SomeLogic GROUP BY MyParamter ) AS MaxT ON MaxT.MyParamter = t.MyParamter WHERE SomeLogic;

Вы можете избежать этого, удалив +1 и добавив ROW_NUMBER():

MaxRecord = MaxT.MyColumn + ROW_NUMBER() OVER(PARTITION BY t.MyParameter ORDER BY t.ID)

Наконец, вы также можете удалить подзапрос, используя также аналитическую функцию, поэтому ваш окончательный выбор будет:

SELECT t.ID, t.MyParameter, t.MyColumn, MyNewColumn = MAX(t,MyColumn) OVER(PARTITION BY t.MyParameter) + ROW_NUMBER() OVER(PARTITION BY t.MyParameter ORDER BY t.ID) FROM MyTable t WHERE SomeLogic;

Затем, чтобы сделать обновление, вы можете просто обернуть все это в CTE и обновить это:

WITH CTE AS ( SELECT t.ID, t.MyParameter, t.MyColumn, MyNewColumn = MAX(t,MyColumn) OVER(PARTITION BY t.MyParameter) + ROW_NUMBER() OVER(PARTITION BY t.MyParameter ORDER BY t.ID) FROM MyTable t WHERE SomeLogic ) UPDATE CTE SET MyColumn = MyNewColumn;

qaru.site