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

Откуда:
Сообщений: 61
в базе данных хранятся объекты, на данный момент около 60 000 шт.
У каждого объекта есть статус. Статус меняется во времени и рассчитывается автоматически.
Есть хранимая процедура которая возвращает историю изменения статусов объектов. Всех сразу.

условно таблицу вида --> obj_id, state_id, state_begin, state_end

если я просто вызываю хранимую процедуру, то получаю примерно 30 тыс. чтений (судя про профайлеру)

если же создаю временную таблицу и в неё втыкаю результат хранимой процедуры, то кол-во чтений возрастает переваливает за миллион


мне нужен запрос с различными фильтрами, например, все объекты которые два месяца имели статус 'новый' и все объекты которые месяц назад имели статус 'текущий' итд.

проблема в том, что запрос отрабатывает примерно за 25 секунд и выдает около 3000000 чтений. Это очень много.
Подскажите пожалуйста какие -то ни будь варианты как оптимизировать в таких случаях.
Думаю, завести реальную таблицу для этого запроса и перед его выполнением подготавливать данные...
p.s. запрос выходит примерно на 200 строк....
2 мар 15, 15:56    [17332507]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Добрый Э - Эх
Guest
deniskodua,

Запрос-то покажи...
2 мар 15, 16:02    [17332571]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
deniskodua
Подскажите пожалуйста какие -то ни будь варианты как оптимизировать в таких случаях

Оптимизировать сферического коня в вакууме очень трудно, практически невозможно

deniskodua
Думаю, завести реальную таблицу для этого запроса и перед его выполнением подготавливать данные...

А сейчас у вас эта "история изменения статусов объектов" не в реальной таблице хранится ?
2 мар 15, 16:05    [17332591]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Добрый Э - Эх
Guest
А вообще, задача на смену состояний решается через инвариант группы на разности двух разнооконных row_number-ов
2 мар 15, 16:05    [17332597]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
deniskodua
Member

Откуда:
Сообщений: 61
Glory,сейчас история рассчитывается хранимкой
2 мар 15, 16:15    [17332683]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Добрый Э - Эх
Guest
deniskodua,

посмотри примеры схожих задач
2 мар 15, 16:16    [17332687]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
deniskodua
мне нужен запрос с различными фильтрами, например, все объекты которые два месяца имели статус 'новый' и все объекты которые месяц назад имели статус 'текущий' итд.


Для начала надо указать версию вашего SQL. Начиная с 2008 есть индексы с фильтрами.

Про разницу двух ROW_NUMBER понятно?

Может вам зарание вести таблицу с этими моментами смены статусов, если требуется выборка на скорости.
2 мар 15, 16:17    [17332695]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
deniskodua
Glory,сейчас история рассчитывается хранимкой

Рассчитывается из воздуха ? Без использования реальных таблиц ?
2 мар 15, 16:25    [17332770]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
deniskodua
Member

Откуда:
Сообщений: 61
вот мой запрос, тут жесть получилась потому сразу не прислал его
+
	
		  
		
		  DECLARE @organizationsIdsString NVARCHAR(MAX) = null-- доступные пользователю огранизации 
		  

		 
		  DECLARE	@swLat DECIMAL(12,8) = -100 -- юго-западная широта 
          DECLARE	@neLat DECIMAL(12,8)  = 100 -- северо-восточная широта         
          DECLARE	@swLng DECIMAL(12,8) = -100 -- юго-западная долгота
          DECLARE	@neLng DECIMAL(12,8) = 100 -- северо-восточная долгота 
          
          
          DECLARE @withDead BIT = 1;-- флаг, грузить ли объекты  со статусом вышедший из строя
          
          
		  DECLARE	@Filter_CitiesIdsString VARCHAR(MAX) = '1,2,3,4,5,6';
		  DECLARE	@Filter_OrganizationsIdsString VARCHAR(MAX) = '1,2,3,4,5,6'; -- фильтр по огранизациям
		  DECLARE   @Filter_StatesData NVARCHAR(MAX) = '1;20130101;20130228,2;20130101;20130103';--идентификаторы статусов с периодами действий
	  
		  
		  DECLARE @orgs_ids TABLE(id BIGINT)
		  IF(@organizationsIdsString IS NOT NULL)
		  BEGIN
			  INSERT INTO @orgs_ids(id)
			   SELECT Id 
				FROM dbo.rep_SplitMultipleIdents(@organizationsIdsString, DEFAULT);	
		  END
          
          
                 
		 
          CREATE TABLE #needed_ctu_objects
          (
            ctu_obj_id BIGINT,
            organization_id BIGINT,
            city_id BIGINT
          ) 
                             
          INSERT INTO #needed_ctu_objects(ctu_obj_id, organization_id, city_id)
          SELECT  
              ctu.Id,
               organizations.Id,
                cities.Id
               	FROM dbo.CtuObjects ctu	 
               	 LEFT JOIN dbo.Organizations organizations ON (ctu.OrganizationID = organizations.Id AND organizations.IsDeleted = 0)
               	  LEFT JOIN dbo.Cities cities ON(organizations.CityID = cities.Id)
	            WHERE ctu.IsDeleted = 0 
	                  AND (@organizationsIdsString IS NULL OR ctu.OrganizationID IN (SELECT id FROM @orgs_ids))
			          AND ctu.Latitude BETWEEN  @swLat AND  @neLat
			          AND ctu.Longitude BETWEEN @swLng  AND @neLng

                    
         

         
         IF(@Filter_CitiesIdsString IS NOT NULL)
         BEGIN		
			 DELETE FROM #needed_ctu_objects WHERE city_id NOT IN (SELECT id FROM dbo.rep_SplitMultipleIdents(@Filter_CitiesIdsString, DEFAULT))
         END;
         
         
         
         
         IF(@Filter_OrganizationsIdsString IS NOT NULL)
         BEGIN		
			 DELETE FROM #needed_ctu_objects WHERE organization_id NOT IN (SELECT id FROM dbo.rep_SplitMultipleIdents(@Filter_OrganizationsIdsString, DEFAULT))
         END;

		
		DECLARE @ids NVARCHAR(MAX) = -- получаем строку идентификаторов через занятую
		CASE
		 WHEN @organizationsIdsString IS NULL AND (SELECT COUNT(DISTINCT organization_id)FROM #needed_ctu_objects) > 99
		  THEN NULL 
		 ELSE (SELECT DISTINCT CAST(organization_id AS NVARCHAR(MAX)) + ',' FROM #needed_ctu_objects FOR XML PATH(''))
		END;
		CREATE TABLE #state_details (ctu_obj_id BIGINT, organization_id BIGINT, status_code NVARCHAR(MAX), begin_date DATE, end_date DATE)-- хранит историю изменений нужных объектов
		INSERT INTO #state_details (ctu_obj_id, status_code, begin_date, end_date, organization_id) 
		EXEC dbo.GetChildMemberStateDetails @ids         
         
       
	         
		
		IF(@Filter_StatesData IS NOT NULL)
		BEGIN

			DECLARE @statuses_ids_with_periods  nvarchar(1000)  = REPLACE(@Filter_StatesData, ' ', '');

			
			DECLARE @status_id_periods_stuck TABLE(chunk NVARCHAR(1000) COLLATE Cyrillic_General_CI_AS)
			DECLARE @comma_separator CHAR(1) = ','
			;WITH str_indexes(a, b) AS
			(
				SELECT 1, CHARINDEX(@comma_separator, @statuses_ids_with_periods)
				UNION ALL SELECT b + 1, CHARINDEX(@comma_separator, @statuses_ids_with_periods, b + 1) FROM str_indexes WHERE b > 0
			),
			str_parts(s) AS
			(
				SELECT RTRIM(LTRIM(SUBSTRING(@statuses_ids_with_periods, a, CASE WHEN b > 0 THEN b-a ELSE 1000 END))) FROM str_indexes
			),
			str_parts_casted(chunk) AS
			(
				SELECT CASE WHEN s = '' OR s = '0' THEN NULL ELSE CAST(s AS NVARCHAR(1000)) END FROM str_parts
			)
			INSERT INTO @status_id_periods_stuck(chunk)
			SELECT chunk FROM str_parts_casted WHERE chunk IS NOT NULL
			
			
			
			DECLARE @status_id_periods_separately TABLE (status_id BIGINT, begin_date DATE, end_date DATE)
			
			DECLARE @child_status_id BIGINT;
			DECLARE @child_status_begin_date DATE;
			DECLARE @child_status_end_date DATE;
			DECLARE @chuck NVARCHAR(MAX);
			
			DECLARE child_statuses_with_period_cursor CURSOR FOR SELECT chunk  FROM @status_id_periods_stuck
			OPEN child_statuses_with_period_cursor
			FETCH NEXT FROM child_statuses_with_period_cursor INTO @chuck
			WHILE(@@FETCH_STATUS = 0)
			BEGIN
				SELECT @chuck = REPLACE(@chuck, ' ', '');
				SELECT @child_status_id = CAST(SUBSTRING(@chuck, 0, CHARINDEX(';', @chuck)) AS BIGINT)
				SELECT @child_status_begin_date =  CAST(SUBSTRING(@chuck, CHARINDEX(';', @chuck) + 1, 8) AS DATE)
				SELECT @child_status_end_date = CAST(SUBSTRING(@chuck, CHARINDEX(';', @chuck) + 8 + 1 + 1, 8) AS DATE)
				
				INSERT INTO @status_id_periods_separately(status_id, begin_date, end_date) VALUES(@child_status_id, @child_status_begin_date, @child_status_end_date)
			
				FETCH NEXT FROM child_statuses_with_period_cursor INTO @chuck
			END
			CLOSE child_statuses_with_period_cursor
			DEALLOCATE child_statuses_with_period_cursor						

			
			DELETE FROM #state_details WHERE ctu_obj_id NOT IN (SELECT ctu_obj_id FROM #needed_ctu_objects)
			
			
			
			DELETE FROM #needed_ctu_objects WHERE ctu_obj_id NOT IN
			(
			 SELECT 
			  details.ctu_obj_id
			  FROM
			   #state_details AS details
				INNER JOIN dbo.States AS member_state ON (details.status_code = member_state.Code)
				 INNER JOIN @status_id_periods_separately AS status_periods ON (member_state.Id = status_periods.status_id AND status_periods.begin_date <= details.end_date AND status_periods.end_date >= details.begin_date) 
			 )		 
		END	










		IF(@Filter_StatesData IS NULL AND @withDead = 0)-- если с формы фильтрации ничего не пришло и стоит галка (не грузить вышедший из строя)
		 BEGIN
		  
		 --то грохнем все объекты со статусом 'вышедший из строя'
		  DELETE FROM #needed_ctu_objects WHERE ctu_obj_id IN
		  (SELECT 
			ctu_obj_id
				FROM #state_details 
					WHERE CAST(GETDATE() AS DATE) BETWEEN begin_date AND end_date AND status_code = 'Dead'
		  )
		END;  
		  

		          

			SELECT  
              childs.Id AS child_id,
               orgs.Id AS organization_id,
                orgs.Name AS organization_name,
                 ctus.Name AS ctu_name,
                  ctus.GeoAddress AS ctu_geo_address,
                   ctus.Latitude AS ctu_latitude,
                    ctus.Longitude AS ctu_longitude,
                     states.Id AS current_status_id,
                      states.Code AS current_status_code,
                       states.Name AS current_status_name
	          FROM  #needed_ctu_objects needed
	          LEFT JOIN dbo.CtuObjects ctus ON needed.ctu_obj_id = ctus.Id
	           LEFT JOIN dbo.Organizations orgs ON ctus.OrganizationID = orgs.Id
	            LEFT JOIN dbo.Cities cities ON orgs.CityID = cities.Id
	             LEFT JOIN -- текущий статус
	             (
	               SELECT ctu_obj_id, status_code, begin_date, end_date
	                FROM #state_details
	                 WHERE CAST(GETDATE() AS DATE) BETWEEN begin_date AND end_date       
	             )  current_statuses ON current_statuses.ctu_obj_id = ctus.Id
	             LEFT JOIN dbo.States states ON current_statuses.status_code = states.Code

	      
	      


       

 DROP TABLE #state_details;
DROP TABLE #needed_ctu_objects
          
			          



потом эти объекты отображаются на google карте

Сообщение было отредактировано: 2 мар 15, 18:32
2 мар 15, 17:21    [17333253]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
deniskodua
Member

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

с использованием реальных таблиц, по факту их две, сама таблица хранящая ctu объекты из неё берется дата регистрации объекта, и
таблица установленных на объект девайсов, у каждого девайса есть период действия, статус зависит от того какие девайсы установлены на данный момент. Пример 01.012010 - дата создания объекта, а 10.01.2010 на него установили 1ый девайс который действует до 17.01.2010,
имеем с 01.01.2010 по 09.01.2010 статус 'новый' с 10.01.2010 по 17.01.2010 статус - пробный, с 18.01.2010 по 31.12.2100 - вышедший из строя
2 мар 15, 17:28    [17333298]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
deniskodua
Member

Откуда:
Сообщений: 61
a_voronin,
про разницу пока не очень понятно
2 мар 15, 17:34    [17333349]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
deniskodua
с использованием реальных таблиц,

И что вам мешает предоставить
- описание структуры этих таблиц
- пример данных в этих таблицах
- конечный результат для этих данных ?
2 мар 15, 17:35    [17333367]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
Добрый Э - Эх
Guest
deniskodua
a_voronin,
про разницу пока не очень понятно

Тут посмотри. Вроде, вполне себе понятный пример...

Но в целом, присоединяюсь к Glory: репрезентативный юзабильный набор тестовых данных и желаемый результат на них - резко повышают желание других участников форума помогать. в том числе и кодом...
2 мар 15, 18:09    [17333618]     Ответить | Цитировать Сообщить модератору
 Re: сложный запрос  [new]
SQLhunter
Member

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

Может задам глупый вопрос, но требуемые индексы Вы в таблицах сделали? Если структуру сюда не даете, то перечислите, пожалуйста индексы с полями или скрин с планом выполнения запроса можете приложить?
2 мар 15, 18:40    [17333774]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить