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

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

Есть хранимая процедура, предназначенная для загрузки товаров в базу интернет магазина. В нее может передаваться от десятков до сотен тысяч товаров. При небольшом количестве товаров работает быстро, однако база выросла до 400 000 товаров [Product], для каждого из которых несколько товарных предложений (поставщиков) [Offer] и для каждого предложения несколько типов цен (Закуп, опт, розница) [OfferPrice].

Загрузка может происходить одновременно в разных потоках (клиент ищет, делается запрос по API). Либо может загружаться одновременно несколько прайсов от поставщиков.

В результате имею две проблемы:
1. Блокировки. Возникают ошибки:
- Транзакция (идентификатор процесса 66) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно.

- Счетчик транзакций после выполнения EXECUTE показывает несовпадение числа инструкций BEGIN и COMMIT. Предыдущее число = 0, текущее число = 1.
Нефиксируемая транзакция обнаружена в конце пакета. Был выполнен откат транзакции.

Пытался разобраться с помощью профайлера, но не получилось.
В коде делал блокировку, чтобы несколько потоков одновременно не выполняли эту хранимую процедуру. Не помогло:
+

lock (locker)
                {
                    using (var db = new SQLDataAccess())
                    {
                        db.cn.ConnectionString = Connection.GetConnectionString();
                        db.cnOpen();

                        db.cmd.CommandText = "[Catalog].[sp_ImportOutsideProductTVP]";
                        db.cmd.CommandType = CommandType.StoredProcedure;
                        db.cmd.Parameters.Clear();
                        db.cmd.Parameters.Add(new SqlParameter("@VendorId", vendorId));
                        db.cmd.Parameters.Add(new SqlParameter("@mainCategoryId", categoryId));
                        db.cmd.Parameters.Add(new SqlParameter("@searchedArtNo", artNo ?? (object)DBNull.Value));
                        db.cmd.Parameters.Add(new SqlParameter("@dissableLastOffers", dissableLastOffers));
                        db.cmd.Parameters.Add(new SqlParameter("@addCrosses", addCrosses));
                        SqlParameter productTable = new SqlParameter();
                        productTable.ParameterName = "@ProductImportTable";
                        productTable.TypeName = "dbo.OutsideProductImportTable";
                        productTable.SqlDbType = SqlDbType.Structured;
                        productTable.Value = new OutsideProductDataRecord(outsideProducts);
                        db.cmd.Parameters.Add(productTable);
                        SqlParameter priceTable = new SqlParameter();
                        priceTable.ParameterName = "@PriceImportTable";
                        priceTable.TypeName = "dbo.OutsideProductPricesImportTable";
                        priceTable.SqlDbType = SqlDbType.Structured;
                        priceTable.Value = new OutsideProductPriceDataRecord(outsideProductPrices);
                        db.cmd.Parameters.Add(priceTable);
                        db.cmd.CommandTimeout = 60 * 10;
                        db.cmd.ExecuteNonQuery();
                        db.cn.Close();
                        sw.Stop();
                        ApsCore.WriteLogTime("[sp_ImportOutsideProductTVP] " + sw.Elapsed);
                       
                    }
                }

2. Низкая производительность.
Иногда сотни товаров грузятся доли секунды. Но обычно 2-5 секунд. Периодически тупняки секунд по 10-30.
Если приходит прайс размером 200к позиций - вешается минут на 5
Таблиц очень много. Индексы вроде все расставил.

Код процедуры:
+

/****** Object:  StoredProcedure [Catalog].[sp_ImportOutsideProductTVP]    Script Date: 19.10.2016 15:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Catalog].[sp_ImportOutsideProductTVP]
	@ProductImportTable	dbo.OutsideProductImportTable READONLY,
	@PriceImportTable	dbo.OutsideProductPricesImportTable READONLY,
	@VendorId INT,
    @mainCategoryId INT,
	@searchedArtNo NVARCHAR(100),
	@dissableLastOffers BIT,
	@addCrosses BIT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ProductId INT;
	DECLARE @errorText NVARCHAR(200);
	BEGIN TRY
		DECLARE @t				INT;
		DECLARE @outsideProduct TABLE (
			[ProductId]		 INT			NOT NULL,		
            [ArtNo]          NVARCHAR (100) NOT NULL,
            [ArtNoClear]     NVARCHAR (100) NOT NULL,                    
            [Article]        NVARCHAR (100) NOT NULL,
            [BrandNameClear] NVARCHAR (100) NOT NULL,
            [BrandName]      NVARCHAR (100) NOT NULL,
            [Name]           NVARCHAR (255) NOT NULL,
			[Description]    NVARCHAR (255) NULL,
            [Url]            NVARCHAR (100) NOT NULL,
            [Amount]         FLOAT (53)     NOT NULL,
			[RowNumber]      INT            NOT NULL,
            [PeriodFrom]     INT            NOT NULL,
            [PeriodTo]       INT            NOT NULL,
			[Multiplicity]   INT            NOT NULL,
            [BrandId]        INT            NOT NULL,
            [Unit]           NVARCHAR (50)  NOT NULL,
            [Information]    NVARCHAR (1000) NOT NULL, 
			UNIQUE([ArtNo],[ProductId],[RowNumber])
        );  
		DECLARE @outsideProductPrices TABLE (
			[RowNumber]     INT            NOT NULL,
            [PriceTypeId]   INT            NOT NULL,
            [Price]			FLOAT          NOT NULL,
			UNIQUE([PriceTypeId],[RowNumber])
        );
		DECLARE @relatedProducts TABLE (
			[Id]             INT            IDENTITY (1, 1) NOT NULL,
            [ArtNo]          NVARCHAR (100) NOT NULL,
            [ArtNoClear]     NVARCHAR (100) NOT NULL,
            [BrandNameClear] NVARCHAR (100) NOT NULL,
            [LinkType]       INT            NOT NULL
			UNIQUE([ArtNo],[Id])
        );
		DECLARE @productIds TABLE (productId INT, UNIQUE(productId));  
		SET @ProductId = (SELECT TOP 1 ProductId FROM [Catalog].[Product] p WHERE p.ArtNo = @searchedArtNo);

		INSERT INTO @outsideProduct SELECT * FROM @ProductImportTable;
		INSERT INTO @outsideProductPrices SELECT * FROM @PriceImportTable;

		IF (@searchedArtNo IS NOT NULL)
			DELETE FROM @outsideProduct WHERE [ArtNo] IN (SELECT [NotAnalogUid] FROM [Catalog].[NotAnalog] WHERE [Uid]=@searchedArtNo);

		BEGIN TRANSACTION;
		SET @t = @@trancount;

				/*Проставляем ProductId для товаров, которые уже есть в базе*/
		UPDATE @outsideProduct SET [@outsideProduct].ProductId = p.ProductId FROM [Catalog].[Product] p WHERE [@outsideProduct].ArtNo = p.ArtNo
		
				/* Помечаем старые Offer */
		IF (@dissableLastOffers = 1)
			UPDATE [Catalog].[Offer] SET [IsOldOffer]=1 WHERE [VendorId] = @VendorId
		ELSE 
			UPDATE [Catalog].[Offer] SET [IsOldOffer]=1 WHERE [VendorId] = @VendorId AND ProductId IN (SELECT DISTINCT ProductId FROM @outsideProduct)

		SET @errorText = 'Добавление товаров';
				/*Добавление товаров*/
		;with
		rawsource as (SELECT ArtNo, ArtNoClear, BrandNameClear, Article, Name, [Description], Unit, BrandId, Url, row_number() over(partition by ArtNo order by ArtNo) as n FROM @outsideProduct)
		, source as (SELECT ArtNo, ArtNoClear, BrandNameClear, Article, Name, [Description], Unit, BrandId, Url FROM rawsource where n = 1)
			MERGE Catalog.Product AS target
			USING  source
			ON (target.ArtNo = source.ArtNo)
			WHEN MATCHED THEN
				UPDATE SET target.[DateModified] = SYSDATETIME(), target.[DateOffersUpdate] = SYSDATETIME()
			WHEN NOT MATCHED THEN
				INSERT ([ArtNo],[Article],[Name],[BriefDescription], [Enabled], [DateAdded], [DateModified], [BrandID],[UrlPath],[CategoryEnabled], [Unit], [HasMultiOffer],[ArtNoClear],[Ratio],[AddManually], [Discount]) 
				VALUES (source.ArtNo,source.Article,source.[Name], source.[Description], 1,SYSDATETIME(),SYSDATETIME(),source.BrandId,source.Url,1,source.Unit,1,source.ArtNoClear,0,1, 0);

		UPDATE @outsideProduct SET [@outsideProduct].[ProductId] = p.ProductId
			FROM [Catalog].[Product] p
			WHERE [@outsideProduct].[ArtNo] = p.ArtNo AND [@outsideProduct].ProductId=-1; 
				
		INSERT INTO @productIds SELECT DISTINCT [ProductId] FROM @outsideProduct;

				/*Добавление товаров в категорию по умолчанию*/
		SET @errorText = 'Добавление товаров в категорию по умолчанию';
		MERGE [Catalog].[ProductCategories] AS target
		USING (SELECT DISTINCT ProductID FROM @outsideProduct)	AS source
		--ON (target.ProductId = source.ProductId)
		ON (target.ProductId = source.ProductId AND target.CategoryID=@mainCategoryId)
			--AND (SELECT COUNT(ProductID) FROM [Catalog].[ProductCategories] pc WHERE pc.ProductId = source.ProductID) = 0)
		WHEN NOT MATCHED THEN
			INSERT (CategoryID, ProductID, SortOrder, Main)
			VALUES (@mainCategoryId, source.ProductId, 0, 0);

				/*Добавление товарных предложений*/
		SET @errorText = 'Добавление товарных предложений';
		;with
		source as (SELECT ProductId, ArtNo + '-' + CAST(@VendorId AS VARCHAR) + '-' + CAST(RowNumber AS VARCHAR) as ArtNo, ArtNoClear, BrandNameClear, Article, Name, RowNumber, Amount, BrandName, PeriodFrom, PeriodTo, Multiplicity, Information, Unit FROM @outsideProduct)

				MERGE [Catalog].[Offer] AS target
				USING source
				ON (target.ArtNo = source.ArtNo)
				WHEN MATCHED THEN
					UPDATE  SET 
		target.[Article] = source.Article, target.[Amount] = source.Amount,
		target.[Main] = 0, target.[PeriodFrom] = source.PeriodFrom, target.[PeriodTo] = source.PeriodTo, target.[ProductName] = source.Name,
		target.[BrandName] = source.BrandName, target.[Information] = source.[Information]
				WHEN NOT MATCHED THEN
				INSERT ([ProductID],[VendorID],[ArtNo],[Article],[Amount],[Main],[PeriodFrom],[PeriodTo],[Multiplicity],[ProductName],[BrandName],[Information],[Unit],[DateUpdate],[RowNumber],[IsOldOffer])
				VALUES (source.ProductId, @VendorId, source.ArtNo ,source.Article,source.[Amount],0, source.PeriodFrom,source.PeriodTo,source.[Multiplicity], 
				source.Name,source.BrandName,source.[Information], source.[Unit], SYSDATETIME(), source.RowNumber, 0);
			
				/*Загрузка цен*/
		SET @errorText = 'Загрузка цен';
		;with
		source as (SELECT o.OfferID, op.PriceTypeId, op.Price FROM @outsideProductPrices op JOIN [Catalog].[Offer] o ON o.RowNumber = op.RowNumber AND o.VendorID = @VendorId )

		MERGE [Catalog].[OfferPrice] AS target
		USING source
		ON target.OfferId = source.OfferId
		WHEN NOT MATCHED THEN
			INSERT (OfferID, PriceTypeId, Price)
			VALUES (source.OfferID, source.PriceTypeId, source.Price);

				/*Добавление кроссов */
		SET @errorText = 'Добавление кроссов';
		IF @addCrosses = 1 
		BEGIN
			INSERT INTO @relatedProducts ([ArtNo], [ArtNoClear], [BrandNameClear], [LinkType]) 
				SELECT [ArtNo], [ArtNoClear], [BrandNameClear], 1 FROM @outsideProduct 
				WHERE [ArtNo] NOT IN (SELECT [NotAnalogUid] FROM [Catalog].[NotAnalog] WHERE [Uid]=@searchedArtNo);

			WITH rawsource as (SELECT DISTINCT [ArtNo], [LinkType] FROM @relatedProducts)
			, source as (SELECT p.[ProductId], r.[LinkType] as LinkType FROM [Catalog].[Product] p JOIN rawsource r ON r.[ArtNo] = p.ArtNo)
				MERGE [Catalog].[RelatedProducts] AS target
				USING source
				ON (target.ProductId = @ProductId AND target.[LinkedProductID]=source.ProductID)
				WHEN NOT MATCHED THEN
					INSERT ([ProductID],[LinkedProductID],[RelatedType])
					VALUES (@ProductId, source.[ProductId], source.[LinkType]);	
		END

				/*Реиндекс товарных предложений*/
		SET @errorText = 'Реиндекс товарных предложений';
		UPDATE [Catalog].[Offer]  SET [Main]=0 WHERE [ProductID] IN (SELECT ProductId FROM @productIds)
			AND [IsOldOffer] = 0
		;WITH allOffers AS (
			SELECT o.ProductId, o.OfferId, row_number() OVER(PARTITION BY o.ProductId ORDER BY o.PeriodFrom, op.Price) as n 
			FROM [Catalog].[Offer] o
			JOIN [Catalog].[OfferPrice] op ON op.OfferId = o.OfferID
			JOIN [Catalog].[Vendor] v ON o.VendorID=v.VendorId
			WHERE [ProductID] IN (SELECT ProductId FROM @productIds) AND
				o.IsOldOffer=0 AND v.IsOutsideApi=0 AND o.Amount>0  AND op.Price > 0)
		MERGE [Catalog].[Offer] as target
		USING (SELECT ProductId, OfferId FROM allOffers WHERE n=1) AS source
		ON target.OfferId=source.OfferId
		WHEN MATCHED THEN UPDATE SET Main=1;

		SET @errorText = 'Обновление наименований';
		;with offerNames AS (SELECT DISTINCT ProductId, ProductName FROM [Catalog].[Offer] o WHERE 
			o.ProductID IN (SELECT ProductId FROM @productIds) AND o.[IsOldOffer]=0 AND o.[Main]=1)
		MERGE [Catalog].[Product] AS target
		USING offerNames AS source ON target.ProductId=source.ProductId
		WHEN MATCHED THEN UPDATE SET Name=source.ProductName;

		COMMIT TRANSACTION
		SET @errorText = 'Создание [##productIds]';
				/*Загрузка ProductID для последующего запуска [Catalog].[sp_PreCalcProductParamsMassAps]*/
		SET @errorText = 'Загрузка ProductID для последующего запуска [Catalog].[sp_PreCalcProductParamsMassAps]';
		IF OBJECT_ID(N'tempdb..##productIds', N'U') IS NOT NULL 
		DROP TABLE [##productIds];

		CREATE TABLE [##productIds] (
		[ProductId]       INT            NOT NULL
		);

		INSERT INTO [##productIds] (ProductId) SELECT ProductId FROM @productIds;

		SET @errorText = 'Вызов [sp_PreCalcProductParamsMassAps]';
		EXEC [Catalog].[sp_PreCalcProductParamsMassAps];
		IF OBJECT_ID(N'tempdb..##productIds', N'U') IS NOT NULL 
		DROP TABLE [##productIds];

	END TRY
	BEGIN CATCH
		IF @t = 0 and @@trancount > 0
			rollback transaction;
		RAISERROR (@errorText,10,1) ;
	END CATCH;
END
19 окт 16, 23:40    [19802279]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
АвторОтвета
Guest
"Таблиц очень много. Индексы вроде все расставил." - Актуальный план выполнения в студию, плюс статистку sys.dm_db_index_usage_stats - вот и проверим.

а пока читаем про:
XACT_ABORT ON;
table variable vs temporary table
MERGE vs UPDATE/INSERT/DELETE
sp_getapplock
20 окт 16, 00:22    [19802367]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
АвторОтвета
"Таблиц очень много. Индексы вроде все расставил." - Актуальный план выполнения в студию, плюс статистку sys.dm_db_index_usage_stats - вот и проверим.

а пока читаем про:
XACT_ABORT ON;
table variable vs temporary table
MERGE vs UPDATE/INSERT/DELETE
sp_getapplock


План выполнения:
https://yadi.sk/d/-3UQqm6QxBept
Первые запросы вставки в нем - создание тестового набора данных.

Статистика:
https://yadi.sk/d/WUd_JOQWxBetA

По поводу XACT_ABORT ON.
Мне, я считаю, нужно наоборот в OFF. Чтобы если товары загрузились, и после этого произошла ошибка - ничего страшного если не обновятся наименования..

По поводу table variable vs temporary table:
Раньше использовал временные таблицы. Затем нагуглил вариант с табличными переменными. Вроде и немного быстрее грузятся данные из приложения и код более объектно-ориентированный.

По поводу MERGE:
Как я понял тут - https://www.sql.ru/forum/1109229-1/est-li-preimushhestva-merge-pered-insert-update-delete
у MERGE больше плюсов
20 окт 16, 01:12    [19802410]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
aleks2
Guest
Осподи! Избави нас от горе-программиздов!!!

1. Прайс(ы) грузится во ВРЕМЕННУЮ таблицу и, желательно, BULK Insert-ом.
2. Из временной таблицы прайс ЗАПРОСОМ/АМИ, а не императивным говнокодом, распихивается во все нужные дырки.
3. Загрузка во времянки - в параллельных потоках. Загрузка в основную - с блокировкой параллельного исполнения.
20 окт 16, 06:08    [19802471]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
aleks2
Осподи! Избави нас от горе-программиздов!!!

1. Прайс(ы) грузится во ВРЕМЕННУЮ таблицу и, желательно, BULK Insert-ом.
2. Из временной таблицы прайс ЗАПРОСОМ/АМИ, а не императивным говнокодом, распихивается во все нужные дырки.
3. Загрузка во времянки - в параллельных потоках. Загрузка в основную - с блокировкой параллельного исполнения.


1. Чем временная таблица лучше табличной переменной? чтобы подготовить файл под bulk и будет написан говнокод. проходил уже.
2. А у меня не запросы из и таличных переменных распихивают?
3. Загрузка во времянку происходит в десятки раз быстрее чем распихивание в основную и не является узким местом.
20 окт 16, 09:30    [19802797]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Artprog
aleks2
Осподи! Избави нас от горе-программиздов!!!

1. Прайс(ы) грузится во ВРЕМЕННУЮ таблицу и, желательно, BULK Insert-ом.
2. Из временной таблицы прайс ЗАПРОСОМ/АМИ, а не императивным говнокодом, распихивается во все нужные дырки.
3. Загрузка во времянки - в параллельных потоках. Загрузка в основную - с блокировкой параллельного исполнения.


1. Чем временная таблица лучше табличной переменной? чтобы подготовить файл под bulk и будет написан говнокод. проходил уже.
2. А у меня не запросы из и таличных переменных распихивают?
3. Загрузка во времянку происходит в десятки раз быстрее чем распихивание в основную и не является узким местом.


https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/
20 окт 16, 09:32    [19802817]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
TaPaK
Artprog
пропущено...


1. Чем временная таблица лучше табличной переменной? чтобы подготовить файл под bulk и будет написан говнокод. проходил уже.
2. А у меня не запросы из и таличных переменных распихивают?
3. Загрузка во времянку происходит в десятки раз быстрее чем распихивание в основную и не является узким местом.


https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/

+ c временной таблицей можно использовать SqlBulkCopy. С табличной переменной этого не выйдет, и придется изращаться как-то иначе - xml, UDTT, etc.
20 окт 16, 09:38    [19802850]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
serpentariy
Member

Откуда:
Сообщений: 265
Artprog, строки
BEGIN TRANSACTION;
SET @t = @@trancount;
надо поменять местами. В противном случае @t никогда не равно 0 и ROLLBACK в catch не происходит. Отсюда и
Artprog
- Счетчик транзакций после выполнения EXECUTE показывает несовпадение числа инструкций BEGIN и COMMIT. Предыдущее число = 0, текущее число = 1.
Нефиксируемая транзакция обнаружена в конце пакета. Был выполнен откат транзакции.
20 окт 16, 09:55    [19802961]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
Сон Веры Павловны
TaPaK
пропущено...


https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/

+ c временной таблицей можно использовать SqlBulkCopy. С табличной переменной этого не выйдет, и придется изращаться как-то иначе - xml, UDTT, etc.


Я пользовался балком. На прайсах объемом 100к-500к строк кушал очень много памяти. Нашел статью:
http://www.sqlservercentral.com/articles/SQL Server 2008/66554/
Этот метод меньше кушает память и не намного медленнее работает. Поэтому переделал на этот способ.
20 окт 16, 10:03    [19803014]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
serpentariy,

Спасибо!
20 окт 16, 10:03    [19803016]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
автор
надо поменять местами
надо вообще убрать, логики на нём нет никакой
20 окт 16, 10:05    [19803023]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31431
Сон Веры Павловны
TaPaK
пропущено...


https://www.mssqltips.com/sqlservertip/2825/sql-server-temp-table-vs-table-variable-performance-testing/

+ c временной таблицей можно использовать SqlBulkCopy. С табличной переменной этого не выйдет, и придется изращаться как-то иначе - xml, UDTT, etc.
У ТС загрузка не является узким местом, зачем ему Bulk?

Artprog
План выполнения:
https://yadi.sk/d/-3UQqm6QxBept
Первые запросы вставки в нем - создание тестового набора данных.
Процедура занимает 38% выполнения от всего тестового скрипта
И из этих 38% всё время занимает один запрос:
SET @errorText = 'Загрузка цен';
		;with
		source as (SELECT o.OfferID,...

Имеем сканирование [Catalog].[Offer].
Сиквел предлагает сделать индекс
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Catalog].[Offer] ([VendorID],[IsOldOffer])
INCLUDE ([ProductID],[RowNumber],[Amount],[Article],[BrandName],[Unit],[PeriodFrom],[PeriodTo],[Multiplicity],[Information])

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

В общем, концептуально подход ИМХО правильный, проблема именно в деталях реализации процедуры.
Где то можно поправить логику, смотреть профайлером, сделать множество разнообразных тестовых скриптов, и трейсить.
20 окт 16, 10:13    [19803079]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31431
alexeyvg
Сиквел предлагает сделать индекс
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Catalog].[Offer] ([VendorID],[IsOldOffer])
INCLUDE ([ProductID],[RowNumber],[Amount],[Article],[BrandName],[Unit],[PeriodFrom],[PeriodTo],[Multiplicity],[Information])

Непонятно, почему так предлагает...
Я бы попробовал
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Catalog].[Offer] (VendorID, RowNumber )
INCLUDE ([ProductID],[IsOldOffer])
20 окт 16, 10:24    [19803143]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
Artprog
Чем временная таблица лучше табличной переменной?
Тем, что для временной таблицы оптимизатор может оценить кардинальность, а для табличной переменной, без дополнительных манипуляций, кардинальность всегда оценивается в 1 строку.
Соответственно, план выполнения, когда у вас в табличной переменной много строк, может оказаться весьма затратным и неэффективным.
20 окт 16, 14:51    [19804927]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
Artprog
Чем временная таблица лучше табличной переменной?
Тем, что для временной таблицы оптимизатор может оценить кардинальность, а для табличной переменной, без дополнительных манипуляций, кардинальность всегда оценивается в 1 строку.
Соответственно, план выполнения, когда у вас в табличной переменной много строк, может оказаться весьма затратным и неэффективным.

а подскажи, как для табличной переменной это делать? Я так понимаю что решает Recompile, или что то иное?
20 окт 16, 14:58    [19804955]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
TaPaK
Я так понимаю что решает Recompile
Решает.
TaPaK
или что то иное
TF 2453
20 окт 16, 15:09    [19805000]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
TaPaK
или что то иное
TF 2453[/quot]
ага, спасибо
20 окт 16, 15:10    [19805008]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
FOX75
Member

Откуда:
Сообщений: 29
TaPaK
автор
надо поменять местами
надо вообще убрать, логики на нём нет никакой

ага... и плодить счетчик транзакций.
21 окт 16, 09:52    [19807265]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
FOX75
TaPaK
пропущено...
надо вообще убрать, логики на нём нет никакой

ага... и плодить счетчик транзакций.

прослезился.... зачем?
21 окт 16, 09:54    [19807278]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
o-o
Guest
FOX75,

вот ваш код в упрощенном виде:

BEGIN TRANSACTION;
		SET @t = @@trancount; -- 1, если процедура вне транзакции, >1, если внутри
...
	BEGIN CATCH
		IF @t = 0 and @@trancount > 0
			rollback transaction;

между этими двумя кусками вообще нет упоминания о @t.
как вы думаете, если @t присвоено значение >=1 и больше никогда @t не меняется,
хоть когда-нибудь сработает условие IF @t = 0 and @@trancount > 0?
21 окт 16, 10:21    [19807476]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
invm,

Хранимка в качестве параметра принимает табличную переменную. Но, так как TVP должна быть readonly мне приходится данные перекидывать во временную таблицу и далее она используется для всех запросов.
23 окт 16, 22:16    [19813738]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Artprog
Member

Откуда: Краснодар
Сообщений: 55
Прошу прощения - перекидываю не во временную таблицу, а в табличную переменную.

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

Но между пунктом 1,2 таблица может исчезнуть, если сервер посчитает, что она уже не нужна.
23 окт 16, 22:33    [19813780]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Mike_za
Member

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

Про "ненужна" вы что-то напутали. Временные таблицы так просто не грохаются
23 окт 16, 22:52    [19813829]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
Mike_za
Member

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

Ну а табличную переменную и большое число строк вам уже несколько раз выше написали...
23 окт 16, 22:53    [19813831]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация хранимой процедуры, транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
Artprog
Но между пунктом 1,2 таблица может исчезнуть, если сервер посчитает, что она уже не нужна.
Это вы фантазируете.
Если временная таблица "исчезла", - значит 1 и 2 выполнялись в разных сессиях.
23 окт 16, 23:12    [19813869]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить