Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Здравствуйте!

Есть SQL-запрос (без выделенного), который формируется за 6 минут при возвращении 20 000 строк.
+SQL-запрос
WITH
Выгрузка1 AS (
    SELECT
        ...
)
SELECT
	*
FROM
	Выгрузка4
ORDER BY
	[Город],
	[Клиент],
	[Регион ТТ],
	[Код ТТ],
	[Код привязанного ассортимента],
	[Тип общего SKU],
	[Общее SKU],
	[SKU]
Но если добавить ORDER BY, то выполняется дольше полтора часа, даже не дожидался.

Как можно обойти эту проблему? Есть вариант толкать в TEMPORARY TABLE и задавать индексы. Скажите, какие есть другие варианты?
30 июн 18, 07:40    [21532607]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30764
ferzmikk
Есть SQL-запрос (без выделенного), который формируется за 6 минут при возвращении 20 000 строк.
А в многоточии что, там нет TOP nnn ?
30 июн 18, 10:16    [21532704]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
ferzmikk
Есть SQL-запрос (без выделенного), который формируется за 6 минут при возвращении 20 000 строк.
А в многоточии что, там нет TOP nnn ?
TOP нет
30 июн 18, 10:26    [21532717]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
ferzmikk
Есть SQL-запрос (без выделенного), который формируется за 6 минут при возвращении 20 000 строк.
А в многоточии что, там нет TOP nnn ?

+SQL-запрос
...,
Выгрузка4 AS (
	SELECT
		Таб1.[Город],
		Таб1.[Клиент],
		Таб1.[Регион ТТ],		
		Таб1.[Код ТТ],		
		Таб1.[Торговая точка],		
		Таб1.[Адрес ТТ],
		Таб1.[Код привязанного ассортимента],
		Таб1.[Тип общего SKU],
		Таб1.[Общее SKU],
		Таб1.[SKU],
		Таб1.[Отгрузки шт],
		Таб1.[Новинка],
		Таб1.[Кол-во ТТ],
		Таб2.[Кол-во SKU в общем SKU],
		Таб2.[Кол-во отгрузок в общем SKU],
		Таб2.[Кол-во новинок в общем SKU],		
		(CASE
			WHEN Таб2.[Кол-во SKU в общем SKU] = 1 THEN
				(CASE
					WHEN Таб1.[Отгрузки шт] > 0 AND Таб1.[Новинка] = 'Да' THEN '' 
					WHEN Таб1.[Отгрузки шт] > 0 AND Таб1.[Новинка] = '' THEN '' 
					WHEN Таб1.[Отгрузки шт] = 0 AND Таб1.[Новинка] = 'Да' THEN 'Да' 
					WHEN Таб1.[Отгрузки шт] = 0 AND Таб1.[Новинка] = '' THEN ''
				ELSE
					''
				END) 
			WHEN Таб2.[Кол-во SKU в общем SKU] = 2 THEN
				(CASE
					WHEN Таб2.[Кол-во отгрузок в общем SKU]] = 1 AND Таб2.[Кол-во новинок в общем SKU] = 2 AND Таб1.[Отгрузки шт] = 0 THEN 'Да' 
					WHEN Таб2.[Кол-во отгрузок в общем SKU]] = 0 AND Таб2.[Кол-во новинок в общем SKU] = 2 THEN 'Да' 
					WHEN Таб2.[Кол-во отгрузок в общем SKU]] = 0 AND Таб2.[Кол-во новинок в общем SKU] = 1 AND Таб1.[Новинка] = 'Да' THEN 'Да' 
				ELSE
					''
				END)
		END) AS [Осталось отгрузить ТТ]
	FROM
		Выгрузка3 Таб1
	OUTER APPLY
		(SELECT
			[Код ТТ],
			[Код привязанного ассортимента],
			[Тип общего SKU]],
			[Общее SKU],
			COUNT([SKU]) AS [Кол-во SKU в общем SKU],
			SUM(CASE WHEN [Отгрузки шт] > 0 THEN 1 ELSE 0 END) AS [Кол-во отгрузок в общем SKU],
			SUM(CASE WHEN [Новинка] = 'Да' THEN 1 ELSE 0 END) AS [Кол-во новинок в общем SKU]			
		FROM
			Выгрузка3 Таб
		WHERE
			Таб1.[Код ТТ] = Таб.[Код ТТ] 
			AND
			Таб1.[Код привязанного ассортимента] = Таб.[Код привязанного ассортимента]
			AND
			Таб1.[Тип общего SKU] = Таб.[Тип общего SKU]
			AND
			Таб1.[Общее SKU] = Таб.[Общее SKU]
		GROUP BY
			[Код ТТ],
			[Код привязанного ассортимента],
			[Тип общего SKU],
			[Общее SKU]) Таб2
)

SELECT
	*
FROM
	Выгрузка4
ORDER BY
	[Город],
	[Клиент],
	[Регион ТТ],
	[Код ТТ],
	[Код привязанного ассортимента],
	[Тип общего SKU],
	[Общее SKU],
	[SKU]
30 июн 18, 11:12    [21532765]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30764
ferzmikk
Но если добавить ORDER BY, то выполняется дольше полтора часа, даже не дожидался.

Как можно обойти эту проблему? Есть вариант толкать в TEMPORARY TABLE и задавать индексы. Скажите, какие есть другие варианты?
Видимо, как то криво строится план, сортирует в таблицах до накладывания ограничений.
Можно сравнить планы...

Попробуйте сделать первый запрос INTO #temp, а потом выбрать из неё с сортировкой, думаю, и без всяких индексов 20 тыщ должно отсортировать быстро.
30 июн 18, 11:21    [21532775]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
Видимо, как то криво строится план, сортирует в таблицах до накладывания ограничений.
Подскажите, как можно скорректировать планы запросов?
Можно сравнить планы...
Нужно обращать внимание на процент стоимости?
30 июн 18, 12:52    [21532889]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30764
ferzmikk
Можно сравнить планы...
Нужно обращать внимание на процент стоимости?
Нужно посмотреть, где делается сортировка.
ferzmikk
alexeyvg
Видимо, как то криво строится план, сортирует в таблицах до накладывания ограничений.
Подскажите, как можно скорректировать планы запросов?
Можно попробовать обновить статистику.
30 июн 18, 14:53    [21532990]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
Попробуйте сделать первый запрос INTO #temp, а потом выбрать из неё с сортировкой, думаю, и без всяких индексов 20 тыщ должно отсортировать быстро.
+SQL-запрос
WITH
Выгрузка1 AS (
    SELECT
        ...
)

Select * INTO #Выгрузка FROM Выгрузка4
CREATE /*CLUSTERED*/ INDEX i1 ON #Выгрузка([Город],[Клиент],[Регион ТТ],[Код ТТ],[Код привязанного ассортимента], [Тип общего SKU], [Общее SKU], [SKU]); 

SELECT
	*
FROM
	Выгрузка4
ORDER BY
	[Город],
	[Клиент],
	[Регион ТТ],
	[Код ТТ],
	[Код привязанного ассортимента],
	[Тип общего SKU],
	[Общее SKU],
	[SKU]
Выполняется намного быстрее. Но не хотелось бы использовать времянку, так как увеличивает TempDB.
2 июл 18, 10:43    [21536108]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ferzmikk,

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

а на кой она вообще тогда :)

зы

Select * INTO #Выгрузка FROM Выгрузка4
CREATE /*CLUSTERED*/ INDEX i1 ON #Выгрузка(

текущая реальность сильно не рекомендует так делать... CREATE TABLE вместе с индексом и INSERT.... ms никогда не победит tempdb до конца :)
2 июл 18, 10:47    [21536122]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
TaPaK
текущая реальность сильно не рекомендует так делать...
Почему?
CREATE TABLE вместе с индексом и INSERT....
Я правильно понимаю, что после того, как табличную переменную Выгрузка4 - вставляем в созданную таблицу с индексами (не временная, назовем Таблица1) и там внутри сортируем? Или после вставки SELECT * FROM Таблица1 GROUP BY ...?
2 июл 18, 10:59    [21536170]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ferzmikk,

потому что гладиолусы там

автор
you use these methods as best practices in future Transact-SQL development:

Remove the DROP TABLE commands for any temporary tables that won't be reused within the same execution of the stored procedure. SQL Server will remove the temporary tables when the session that created the temporary tables has ended.

Avoid using the TRUNCATE TABLE command against temporary tables.

Avoid altering temporary tables after they have been created.

If indexes are created against the temporary table, consider moving these to inline index creation statements within the CREATE TABLE command.

со старыми проблемами с блокировками системных объектов tempdb
https://support.microsoft.com/en-us/help/4131193/performance-issues-occur-in-form-of-pagelatch-ex-and-pagelatch-sh-wait
2 июл 18, 11:33    [21536261]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30764
ferzmikk
Я правильно понимаю, что после того, как табличную переменную Выгрузка4 - вставляем в созданную таблицу с индексами (не временная, назовем Таблица1) и там внутри сортируем? Или после вставки SELECT * FROM Таблица1 GROUP BY ...?
Вы попробуйте индекс вообще не делать. Не факт, что сортировка при создании индекса будет быстрее, чем при выборке. Всё таки там нужно сохранять отсортированные данные.

TaPaK
со старыми проблемами с блокировками системных объектов tempdb
Думаю, у ТС не такие нагрузки и не столько пользователей, что бы такие тонкости учитывать. Если он смотрит, как ускорить запрос с часов до минут. Какая нибудь ночная выгрузка, когда пользователи спят дома :-)
2 июл 18, 17:11    [21537253]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
alexeyvg,

автор
Думаю, у ТС не такие нагрузки и не столько пользователей, что бы такие тонкости учитывать. Если он смотрит, как ускорить запрос с часов до минут. Какая нибудь ночная выгрузка, когда пользователи спят дома :-)
нисомненно, проблемы вроде от 4к одновременных объектов. Речь про то, что мягкие намекают на стандарт разработки с исключением этих телодвижений
2 июл 18, 17:14    [21537267]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
ferzmikk
Я правильно понимаю, что после того, как табличную переменную Выгрузка4 - вставляем в созданную таблицу с индексами (не временная, назовем Таблица1) и там внутри сортируем? Или после вставки SELECT * FROM Таблица1 GROUP BY ...?
Вы попробуйте индекс вообще не делать. Не факт, что сортировка при создании индекса будет быстрее, чем при выборке. Всё таки там нужно сохранять отсортированные данные.
Получилось. Без индексов.

Интересует такой вопрос, если два пользователя или более одновременно вызвали процедуру, то как тут распределится Таблица1? Или надо прописать запрос так, что используется ли кем то Таблица1 или нет? В MS SQL Server есть какие нибудь технологии, которые учитывает или отлавливают такие подобные случаи?
3 июл 18, 09:00    [21538387]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30764
ferzmikk
Интересует такой вопрос, если два пользователя или более одновременно вызвали процедуру, то как тут распределится Таблица1?
Эээ, конечно, нужно использовать временную таблицу, не надо постоянную.
Вот как вы писали: Select * INTO #Выгрузка FROM Выгрузка4

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

С постоянной таблицей будет, во первых, медленней, во вторых, нужно будет разделять данные по коннектом, для чего придётся создавать кластерный индекс по SPID, в общем, всё будет хуже.
3 июл 18, 09:54    [21538476]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
alexeyvg
Потом, после завершения выполнения процедуры, таблица #Выгрузка сама удалится, ничего для этого делать не надо, ну и запускать можно в каком угодно количестве экземпляров, из разных коннектов.
Как сама может удалиться без DROP #Выгрузка? Или не удаляется, после завершения запроса, потому что там индексы есть?
3 июл 18, 10:14    [21538528]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
ferzmikk,

вы хоть sql начните изучать, а не бомбите форму идиотизмом
3 июл 18, 10:16    [21538535]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос. Order by  [new]
Eleanor
Member

Откуда:
Сообщений: 2632
ferzmikk
Как сама может удалиться без DROP #Выгрузка?

Из документации:
Временные таблицы автоматически удаляются при выходе за пределы области определения, если не удалять их явно с помощью инструкции DROP TABLE.
  • Локальная временная таблица, созданная хранимой процедурой, удаляется автоматически при завершении хранимой процедуры.
  • Все прочие локальные временные таблицы удаляются автоматически в конце текущего сеанса.
  • 3 июл 18, 12:11    [21538897]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить