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

Откуда:
Сообщений: 17
Кто может подсказать, уже второй день бьюсь, нужно оптимизовать код

вот код

USE [Xplode_Content_GlobalEdge]
GO
/****** Object:  StoredProcedure [dbo].[usp_TiersTop10DropDawn]    Script Date: 08/28/2014 13:46:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


 
ALTER PROCEDURE [dbo].[usp_TiersTop10DropDawn] 
(
	@TierID int
)
AS
DECLARE @contentTitle nvarchar(100);
DECLARE @TierName nvarchar(255)
-- создаём таблицу и помешаем в неё, Tier и кол-во компаний в ней
SET @TierName = (SELECT Name
				FROM Tiers
				WHERE @TierID = TierID)
DECLARE @get_tiers CURSOR
	SET @get_tiers = CURSOR SCROLL
	FOR
	SELECT [TierID] FROM Tiers WHERE [Name] LIKE @TierName + '%'
	
DECLARE @tempTable table(ID int NOT NULL, TierName nvarchar(255), CompaniesTierCount int);


--Кол-во компаний в Tier
DECLARE @Count  nvarchar(255)

--Смотрим сколько компаний

	
--вставляем данные в таблицу

OPEN @get_tiers
	/*Select the first row*/
	FETCH NEXT FROM @get_tiers INTO @tierID
	/*Running in a loop through the lines*/
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--извлекаем имя Tier
		SET @TierName = (SELECT Name
				FROM Tiers
				WHERE @TierID = TierID)
		-- извлекаем колличество компаниий, находяшихся в Tier		
		SET @Count = (
		SELECT COUNT([CompanyID])
		FROM  Companies
		WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' and (@TierID <> '' or EditorsChoice = 'true')
		AND (@TierID = 0 OR CompanyID IN
				( 
					SELECT 	CompanyID
					FROM 	CompanyTiers
					WHERE 	TierID  IN 
					(
						SELECT TierID
						FROM Tiers
						WHERE [Name] LIKE @TierName + '%'
					) 			
				)
			))
			--вставить таблицу	
		INSERT INTO @tempTable VALUES (@TierID,@TierName,@Count)
		FETCH NEXT FROM @get_tiers INTO @tierID
	END
CLOSE @get_tiers


select * from @tempTable WHERE CompaniesTierCount <> 0  ORDER BY TierName


Нужно оставить все условия те же но как-то заставить быстрее работать, при этом я разобрался что тормозит именно блок
SELECT COUNT([CompanyID])
		FROM  Companies
		WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' and (@TierID <> '' or EditorsChoice = 'true')
		AND (@TierID = 0 OR CompanyID IN
				( 
					SELECT 	CompanyID
					FROM 	CompanyTiers
					WHERE 	TierID  IN 
					(
						SELECT TierID
						FROM Tiers
						WHERE [Name] LIKE @TierName + '%'
					) 			
				)
			))

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

Примичание: Кол-во записей во всех таблицах превышает 1 000
29 авг 14, 08:24    [16510498]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Как насчёт чистоты кода?
@TierID <> ''
Ведь @TierID типа int.

А так можно либо добавить OPTION(RECOMPILE)
SELECT COUNT([CompanyID])
		FROM  Companies
		WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' and (@TierID <> '' or EditorsChoice = 'true')
		AND (@TierID = 0 OR CompanyID IN
				( 
					SELECT 	CompanyID
					FROM 	CompanyTiers
					WHERE 	TierID  IN 
					(
						SELECT TierID
						FROM Tiers
						WHERE [Name] LIKE @TierName + '%'
					) 			
				)
			)
OPTION(RECOMPILE)


Либо переписать запрос, разделив на 2.
SELECT COUNT([CompanyID])
		FROM  Companies
		WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' and (@TierID = 0 and EditorsChoice = 'true')
UNION ALL
SELECT COUNT([CompanyID])
		FROM  Companies
		WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' and (@TierID <> 0 and  CompanyID IN
				( 
					SELECT 	CompanyID
					FROM 	CompanyTiers
					WHERE 	TierID  IN 
					(
						SELECT TierID
						FROM Tiers
						WHERE [Name] LIKE @TierName + '%'
					) 			
				)
                  )
29 авг 14, 10:08    [16510870]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Verbal,

тут нечего оптимизировать.
Надо написать всё с нуля.
Выкинуть все мысли о курсоре при этом.

Вот объясните, зачем там курсор??

Тем более, SCROLL. Это выдаёт полную некомпетентность и в области курсоров.
29 авг 14, 10:13    [16510890]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Verbal
Member

Откуда:
Сообщений: 17
iap,

Подскажи хотя бы в какую сторону мыслить? как это можно сделать более красиво? Какую функциональность использовать, либо пример подобного есть?
29 авг 14, 11:56    [16511687]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Verbal
iap,

Подскажи хотя бы в какую сторону мыслить? как это можно сделать более красиво? Какую функциональность использовать, либо пример подобного есть?
А что это чудо делает-то?
И версия сервера какая?
29 авг 14, 11:59    [16511723]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Glory
Member

Откуда:
Сообщений: 104751
Verbal
Подскажи хотя бы в какую сторону мыслить?

В сторону работы целиком с таблицей. А не с отдельными записями

Verbal
Какую функциональность использовать, либо пример подобного есть?

Стандартную функциональность - SELECT, JOIN, WHERE, EXISTS
29 авг 14, 12:00    [16511736]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Подозреваю, тело вашей процедуры должно выглядеть как-то так
ALTER PROCEDURE [dbo].[usp_TiersTop10DropDawn] 
(
	@TierID int
)
AS
SELECT T.TierID, T.Name, COUNT(*) AS CompanyCount
				FROM Tiers AS T INNER JOIN CompanyTiers AS CT ON T.TierID = CT.TierID
				INNER JOIN Companies AS C ON C.CompanyID = CT.CompanyID
				WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' AND 
					(@TierID <> 0 or EditorsChoice = 'true')
GROUP BY T.TierID, T.Name
29 авг 14, 12:12    [16511836]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Или так:
ALTER PROCEDURE [dbo].[usp_TiersTop10DropDawn]
(
@TierID int
)
AS
SELECT T.TierID, T.Name, COUNT(*) AS CompanyCount
				FROM Tiers AS T INNER JOIN CompanyTiers AS CT ON T.TierID = CT.TierID
				INNER JOIN Companies AS C ON C.CompanyID = CT.CompanyID
				WHERE [DateEstablished] <> '' and [DirectoryListingStatus] = 'Live' AND 
					((@TierID = 0 AND EditorsChoice = 'true') OR  T.TierID = @TierID) 
GROUP BY T.TierID, T.Name
29 авг 14, 12:16    [16511859]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Verbal
Member

Откуда:
Сообщений: 17
MSQ 2008 R2
Делает следующее
Есть три таблице

Компании (CompanyID,...)

Регионы (TierID,ParentTierID,Name...)

РегионКомпания (CompanyTierID, CompanyID, TierID,...)
(связь между регионами и компаниями) это ещё было сделано в 2009 году и менять кореным образом нельзя.

Так вот надо узнать количество компаний в определённом регионе. Так же надо не забыть что регионы могут быть вложенные
т.е.
Например мы выбрали Россию
Записи выглядят так
TierID ParentTierID Name
1236 1254 Russia/Ural/Chelybinsk
1237 1254 Russia/Ural/Chekybinsk
1254 1256 Russia/

Т.е. компании могут как быть в регионе, так и в России, по этому там выбираются имена а потом все записи у которых эти имена совпадают.
29 авг 14, 12:29    [16511970]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Glory
Member

Откуда:
Сообщений: 104751
Verbal
Так же надо не забыть что регионы могут быть вложенные

http://technet.microsoft.com/ru-ru/library/ms186243(v=sql.105).aspx
29 авг 14, 12:31    [16511984]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Verbal
Member

Откуда:
Сообщений: 17
Ну а как рекурсию тут сделать? постояно id сравнивать или как? не проше просто имя извлечь и по нему искать, чем рекурсии делать? ведь имя уникальное,
т.е. если буду искать Russia\ оно найдёт и урал и все города. А так база создавалась таким образом, что они в си определяют уровень региона, что и затрудняет задачу, на sql уровне не понять где все дети региона.
29 авг 14, 12:35    [16512004]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Glory
Member

Откуда:
Сообщений: 104751
Verbal
постояно id сравнивать или как?

Для начала просто напишите рекурсию

Verbal
не проше просто имя извлечь и по нему искать, чем рекурсии делать?

Что мешает начать рекурсию не с корневого элемента, а с конкретного элемента ?
Verbal
на sql уровне не понять где все дети региона.

Что тут непонятного ? TierID - ParentTierID
29 авг 14, 12:39    [16512024]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Verbal
Member

Откуда:
Сообщений: 17
Вот смотри три записи

TierId ParentTierID Name
1256 1245 Russia/
1257 1256 Russia/Ural
1258 1257 Russia/Ural/Chelyabinsk

Т.е. получается Челябинск знает что находится в Урале, но не знает что в Россия,
29 авг 14, 12:47    [16512087]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Glory
Member

Откуда:
Сообщений: 104751
Verbal
Т.е. получается Челябинск знает что находится в Урале, но не знает что в Россия,

Челябинску не надо знать, что он находится в России. Об этом знает родитель Челябенска - Урал.
Это и называется рекурсия.
29 авг 14, 12:49    [16512108]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Verbal
Например мы выбрали Россию
Записи выглядят так
TierID ParentTierID Name
1236 1254 Russia/Ural/Chelybinsk
1237 1254 Russia/Ural/Chekybinsk
1254 1256 Russia/
1256 - это что за объект-папа для России?!
Вы показываете неправильные данные. ИМХО
29 авг 14, 12:55    [16512161]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
iap
Verbal
Например мы выбрали Россию
Записи выглядят так
TierID ParentTierID Name
1236 1254 Russia/Ural/Chelybinsk
1237 1254 Russia/Ural/Chekybinsk
1254 1256 Russia/
1256 - это что за объект-папа для России?!
Вы показываете неправильные данные. ИМХО
Становится непонятным, как определить корневые элементы дерева.
29 авг 14, 12:56    [16512172]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
Verbal
Member

Откуда:
Сообщений: 17
вот кореной элемент

TierID ParentID Name
1000 NULL Europe
29 авг 14, 13:06    [16512256]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
iap
Member

Откуда: Москва
Сообщений: 47083
Verbal
вот кореной элемент

TierID ParentID Name
1000 NULL Europe
А почему Европы нет в Name с TierID =1254?
В то время как с России начинаются все названия входящих в неё регионов?
29 авг 14, 13:08    [16512278]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
случайно заглянул
Guest
Verbal
вот кореной элемент

TierID ParentID Name
1000 NULL Europe


А почему TierID у Европы = 1000, хотя ParentID у элемента Россия = 1256 (или все-таки 1245)? У вас в разных сообщениях разные родители для России (то 1256, то 1245).
29 авг 14, 13:32    [16512447]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
случайно заглянул
Guest
случайно заглянул
У вас в разных сообщениях разные родители для России (то 1256, то 1245).

Вместо 1245 читать 1254.
29 авг 14, 13:42    [16512513]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация кода  [new]
someonesomehow
Guest
может так ?
if OBJECT_ID('companies','u') is not null drop table companies
create table companies(id int,name varchar(255))

if OBJECT_ID('tiers','u') is not null drop table tiers
create table tiers(TierId int,ParentTierID int,name varchar(255))

if OBJECT_ID('companyTiers','u') is not null drop table companyTiers
create table companyTiers(TierID int,CompanyId int)

insert into tiers(TierId,ParentTierID,name)
select 1256,1245,'Russia'
union all
select 1257,1256,'Russia/Ural'
union all
select 1258,1257,'Russia/Ural/Chelyabinsk'

insert into companies(id,name)
select 1,'Рога и копыта Inc.'

insert into companyTiers(CompanyId,TierID)
select 1,1257


declare @tierID int
set @tierID=1257

;
with tiersList as 
(
  select 
  t.TierId
  from tiers t where t.TierId=@tierID
  union all
  select 
    t.TierId
  from tiersList
    join tiers t on t.ParentTierID=tiersList.TierId
)
select COUNT(*) as [CompanyCount]
from tiersList
join companyTiers ct on ct.TierID=tiersList.TierId
29 авг 14, 14:11    [16512816]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить