Alexander Gladchenko

BCP и BLOB

Заметил одну неприятную странность в работе BCP. Связана она с выгрузкой в файл данных BLOB полей, в моём случае, это были поля с типом text. Как я догадываюсь, из-за особенностей хранения этих типов, выгрузка данных выполняется сервером существенно медленнее, чем данных не BLOB типов, даже если объёмы данных (в исчислении получаемого на диске текстового файла) будут равны. Причём, разница во времени выгрузки одинакового объёма данных, в моих реальных случаях, была очень большая, приблизительно в 10 раз BLOB поля выгружались медленнее! Например, на одном сервере, выгрузка BLOB полей занимала 16 часов (размер текстового файла получился 60Гб), а обычных данных – 20 минут (в тексте 8Гб). У таблицы предварительно были удалены все индексы, включая кластеризованный, а также были удалены все ограничения и объекты, которые ссылались на эту таблицу.

Вот пример скрипта, который для этого использовался:

EXEC master..xp_cmdshell 'bcp "SELECT Article_ID, ArticleBody, Annotation, Remarks, mainXML FROM DBNAME.dbo.Articles" queryout "F:\Articles_blobs.txt" -C866 -b 50000 -T -S SERVERANDINSTANCENAME -f"F:\Articles_blobs.fmt"'
GO

Новая подсказка в запросе: OPTIMIZE FOR

В SQL Server 2005 появилась новая фича, которая позволяет немножко уменьшить "головную боль" от того, что оптимизатор запросов автоматически использует значения параметра, переданного в хранимую процедуру для определения плана исполнения запроса. Детали проблемы хорошо изложены тут: Методика, которая гарантирует в SQL Server 2000 выбор определенного плана исполнения запроса.
Только что наткнулся в BOL на новый и достаточно интересных хинт запроса, далее просто цитирую BOL (не взирая на все неточности и "ляпы" перевода)…

OPTIMIZE FOR - Подсказывает оптимизатору запросов, что возможно использовать частичное значение для локальных переменных, если запрос скомпилирован и оптимизирован. Значение используется только в процессе оптимизации запроса, но не в процессе выполнения. Подсказка OPTIMIZE FOR может быть использована для отмены определений параметров оптимизатором или при создании руководства плана. Дополнительные сведения см. в разделах Перекомпиляция хранимых процедур и Оптимизация запросов в используемых приложениях с помощью руководств планов.

Использование подсказки OPTIMIZE FOR

В следующем пример оптимизатору запросов дается распоряжение использовать значение 'Seattle' для локальной переменной @city_name при оптимизации запроса.

DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') )

Статистика, как фактор полезности индекса

  SELECT CAST ('['+ OBJECT_NAME(id) + '].[' + name + ']' AS nvarchar(261)) AS [Индекс]
        ,CONVERT (char(11), STATS_DATE(id, indid),13)			  AS [Статиcтика от:]
        ,CASE
           WHEN indid > 1 
           THEN CAST ((8 * CAST (used AS decimal(9,0)))/1000 AS decimal(9,2))
           WHEN indid = 1 AND OBJECTPROPERTY(id, 'IsView') = 1
           THEN CAST ((8 * CAST (used AS decimal(9,0)))/1000 AS decimal(9,2))
           ELSE NULL	
         END							  AS [Вес (МБ)]
    FROM sysindexes
   WHERE OBJECTPROPERTY(id,       'IsSystemTable'   ) = 0 
     AND INDEXPROPERTY (id, name, 'IsAutoStatistics') = 0
     AND INDEXPROPERTY (id, name, 'IsHypothetical'  ) = 0
     AND INDEXPROPERTY (id, name, 'IsStatistics'    ) = 0
     AND INDEXPROPERTY (id, name, 'IsFulltextKey'   ) = 0
     AND (indid between 2 and 250 OR (indid = 1 AND OBJECTPROPERTY(id, 'IsView') = 1))
     AND (STATS_DATE(id, indid) IS NULL OR STATS_DATE(id, indid) < DATEADD(m, -1, GETDATE()))
ORDER BY CONVERT (char(6), STATS_DATE(id, indid),112), [Вес (МБ)]

101 далматинец

Иногда, жизнь нам преподносит сюрпризы... Кто бы мог подумать, что в оптимизаторе заложена продемонстрированная ниже бомба :( Одно радует, что разработчики обещали в будущем это пофиксить... Если Вас заинтересовало, о чём тут идёт речь, обратите внимание на разницу во времени исполнения разных вариантов применения инструкции TOP и ROWCOUNT

-- SQL Server 2005
USE AdventureWorks
GO

PRINT '---------------------------- проба TOP 100 --------------------------------';
SET STATISTICS TIME ON;
SELECT TOP 100 * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '---------------------------- ROWCOUNT 100 ---------------------------';
SET ROWCOUNT 100;
SET STATISTICS TIME ON;
SELECT * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber
SET STATISTICS TIME OFF;
GO
SET ROWCOUNT 0;

PRINT '---------------------------- TOP 101 --------------------------------';
SET STATISTICS TIME ON;
SELECT TOP 101 * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '---------------------------- TOP 101 WITH TIES ----------------------';
SET STATISTICS TIME ON;
SELECT TOP 101 WITH TIES * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '---------------------------- TOP 101 witl list all filds ------------';
SET STATISTICS TIME ON;
SELECT TOP 101
                   [SalesOrderID]
                  ,[SalesOrderDetailID]
                  ,[CarrierTrackingNumber]
                  ,[OrderQty]
                  ,[ProductID]
                  ,[SpecialOfferID]
                  ,[UnitPrice]
                  ,[UnitPriceDiscount]
                  ,[LineTotal]
                  ,[rowguid]
                  ,[ModifiedDate]
FROM [AdventureWorks].[Sales].[SalesOrderDetail]
ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '----------------------------- TOP (@top=101) -----------------------';
DECLARE @top int;
SET @top = 101;
SET STATISTICS TIME ON;
SELECT TOP (@top) * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '----------------------------- TOP (@top=101) WITH TIES -------------';
DECLARE @top int;
SET @top = 101;
SET STATISTICS TIME ON;
SELECT TOP (@top) WITH TIES * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

PRINT '----------------------------- ROWCOUNT 101 -------------------------';
SET ROWCOUNT 101
SET STATISTICS TIME ON;
SELECT * FROM Sales.SalesOrderDetail ORDER BY CarrierTrackingNumber;
SET STATISTICS TIME OFF;
GO

-- На SQL Server 2000 этот порог равен TOP 1023

Получается, что если в условии исполнения запроса подразумевается сортировка по символьному, не индексированному полю, то для ограничения выборки превышающим 100 числом записей намного эффективнее использовать ROWCOUNT */