Вот крепкий орешек для парней SQL - кто-либо может думать о причине, почему первая из этих функций работает прекрасный, и вторые медленные собакой выполнения?
Функция - Обычно заканчивается в ~5 мс
CREATE FUNCTION dbo.GoodFunction
(
@IDs UniqueIntTable READONLY
)
RETURNS TABLE
AS RETURN
SELECT p.ID, p.Node, p.Name, p.Level
FROM
(
SELECT DISTINCT a.Ancestor AS Node
FROM Hierarchy h
CROSS APPLY dbo.GetAncestors(h.Node.GetAncestor(1)) a
WHERE h.ID IN (SELECT Value FROM @IDs)
) np
INNER JOIN Hierarchy p
ON p.Node = np.Node
Функция B - чрезвычайно медленные Выполнения - я сдался после 5 минут
CREATE FUNCTION dbo.BadFunction
(
@IDs UniqueIntTable READONLY
)
RETURNS TABLE
AS RETURN
WITH Ancestors_CTE AS
(
SELECT DISTINCT a.Ancestor AS Node
FROM Hierarchy c
CROSS APPLY dbo.GetAncestors(c.Node.GetAncestor(1)) a
WHERE c.ID IN (SELECT Value FROM @IDs)
)
SELECT p.ID, p.Node, p.Name, p.Level
FROM Ancestors_CTE ac
INNER JOIN Hierarchy p
ON p.Node = ac.Node
Я объясню ниже того, что эта функция делает, но прежде чем я войду в это, я хочу указать, что я не думаю, что это важно, потому что насколько я могу сказать, эти две функции являются точно тем же! Единственная разница - то, что каждый использует CTE, и каждый использует подзапрос; содержание подзапроса в A и CTE в B идентично.
В случае, если любой решает, что это имеет значение: цель этой функции состоит в том, чтобы только выбрать всех возможных предков (родитель, прародитель, и т.д.) произвольного числа местоположений в иерархии. Node
столбец является a hierarchyid
, и dbo.GetAncestors
функция CLR, которая просто идет по пути, это не делает никакого доступа к данным.
UniqueIntTable
то, что это подразумевает - это - пользовательский тип таблицы с одним столбцом, Value int NOT NULL PRIMARY KEY
. Все здесь, которое должно быть индексировано, индексируется - план выполнения функции A является по существу всего двумя индексами, ищет и соответствие хеша, как это должно быть с функцией B.
Некоторые еще более странные аспекты к этой странной проблеме:
Я даже не могу получить предполагаемый план выполнения относительно простого запроса с помощью функции B. Почти похоже, что проблема производительности имеет некоторое отношение к компиляции этой просто выглядящей функции.
Если я беру "тело" из функции B и просто засовываю его во встроенный запрос, это обычно работает, то же представление в качестве функции A. Таким образом, это только, кажется, проблема с CTE в UDF, или с другой стороны, только с UDF, который использует CTE.
Использование ЦП на одном ядре на тестовой машине пронзает полностью до 100%, когда я пытаюсь выполнить B. Кажется, нет большого количества ввода-вывода.
Я хочу просто не обратить внимания на него как на ошибку SQL Server и версию A использования, но я всегда пытаюсь сохранить Правило № 1 ("ВЫБОР, не Повреждается"), в памяти, и я обеспокоен, что хорошими результатами функции A является так или иначе локализованная счастливая случайность, что это "приведет к сбою" тот же способ, которым B делает на другом сервере.
Какие-либо идеи?
ОБНОВЛЕНИЕ - я теперь включаю полный автономный сценарий для репродуцирования.
Функция GetAncestors
[SqlFunction(FillRowMethodName = "FillAncestor",
TableDefinition = "Ancestor hierarchyid", IsDeterministic = true,
IsPrecise = true, DataAccess = DataAccessKind.None)]
public static IEnumerable GetAncestors(SqlHierarchyId h)
{
while (!h.IsNull)
{
yield return h;
h = h.GetAncestor(1);
}
}
Создание схемы
BEGIN TRAN
CREATE TABLE Hierarchy
(
ID int NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_Hierarchy PRIMARY KEY CLUSTERED,
Node hierarchyid NOT NULL,
[Level] as Node.GetLevel(),
Name varchar(50) NOT NULL
)
CREATE INDEX IX_Hierarchy_Node
ON Hierarchy (Node)
INCLUDE (Name)
CREATE INDEX IX_Hierarchy_NodeBF
ON Hierarchy ([Level], Node)
GO
INSERT Hierarchy (Node, Name)
SELECT CAST('/1/' AS hierarchyid), 'Alice' UNION ALL
SELECT CAST('/1/1/' AS hierarchyid), 'Bob' UNION ALL
SELECT CAST('/1/1/1/' AS hierarchyid), 'Charles' UNION ALL
SELECT CAST('/1/1/2/' AS hierarchyid), 'Dave' UNION ALL
SELECT CAST('/1/1/3/' AS hierarchyid), 'Ellen' UNION ALL
SELECT CAST('/1/2/' AS hierarchyid), 'Fred' UNION ALL
SELECT CAST('/1/3/' AS hierarchyid), 'Graham' UNION ALL
SELECT CAST('/1/3/1/' AS hierarchyid), 'Harold' UNION ALL
SELECT CAST('/1/3/2/' AS hierarchyid), 'Isabelle' UNION ALL
SELECT CAST('/1/4/' AS hierarchyid), 'John' UNION ALL
SELECT CAST('/2/' AS hierarchyid), 'Karen' UNION ALL
SELECT CAST('/2/1/' AS hierarchyid), 'Liam' UNION ALL
SELECT CAST('/2/2/' AS hierarchyid), 'Mary' UNION ALL
SELECT CAST('/2/2/1/' AS hierarchyid), 'Nigel' UNION ALL
SELECT CAST('/2/2/2/' AS hierarchyid), 'Oliver' UNION ALL
SELECT CAST('/2/3/' AS hierarchyid), 'Peter' UNION ALL
SELECT CAST('/2/3/1/' AS hierarchyid), 'Quinn'
GO
CREATE TYPE UniqueIntTable AS TABLE
(
Value int NOT NULL,
PRIMARY KEY (Value)
)
GO
COMMIT
GO
Вышеупомянутый код может использоваться для создания схемы функции/DB CLR; используйте то же GoodFunction
и BadFunction
сценарии в оригинале.
Хаха, попробуйте это:
IF OBJECT_ID('_HappyFunction' ) IS NOT NULL DROP FUNCTION _HappyFunction
IF OBJECT_ID('_SadFunction' ) IS NOT NULL DROP FUNCTION _SadFunction
IF TYPE_ID ('_UniqueIntTable') IS NOT NULL DROP TYPE _UniqueIntTable
GO
CREATE TYPE _UniqueIntTable AS TABLE (Value int NOT NULL PRIMARY KEY)
GO
CREATE FUNCTION _HappyFunction (@IDs _UniqueIntTable READONLY)
RETURNS TABLE AS RETURN
SELECT Value FROM @IDs
GO
CREATE FUNCTION _SadFunction (@IDs _UniqueIntTable READONLY)
RETURNS TABLE AS RETURN
WITH CTE AS (SELECT Value FROM @IDs)
SELECT Value FROM CTE
GO
-- this will return an empty record set
DECLARE @IDs _UniqueIntTable
SELECT * FROM _HappyFunction(@IDs)
GO
-- this will hang
DECLARE @IDs _UniqueIntTable
SELECT * FROM _SadFunction(@IDs)
GO
Кто бы догадался?
Это предполагаемое и просто предположение, но, возможно, у него есть что-то делать с тем, как оптимизатор делает довольно хорошее догадка в лучших планах выполнения, но не делает исчерпывающий поиск один.
Итак, выполнение запросов работает так, как это:
Parse -> Bind -> Оптимизация -> Execute
Разборные деревья для ваших двух запросов, безусловно, будут отличаться. Вентиляционные деревья , вероятно, разные. Я не знаю достаточно о фазе связывания, чтобы указать, что конфигурация, но предполагая, что привязка деревьев различна , то может потребоваться другое количество преобразований для получения деревьев A и B, к тому же выполнению. строить планы.
Если требуется два дополнительных преобразования, чтобы получить запрос B до плана ~ 5 мс, оптимизатор может сказать «достаточно хорошо», прежде чем его обнаружить. Принимая во внимание, что для запроса A ~ 5 мс планируется, может быть, просто внутри порога затрат на поиск.
В первом утверждении ваше соединение
np INNER JOIN Hierarchy p
ON p.Node = np.Node
Ваше второе утверждение является
Ancestors_CTE a
INNER JOIN Hierarchy p
ON p.Node = a.Node
, однако, также используется как Псевдоним для DBO.Getancestors (C.node.Getancestor (1)) в КТ. Попробуйте обменять ANCESSORS_CTE A
с E.g. ACCESTOR_CTE ACTE
, чтобы обеспечить путаницу оптимизатора с двойным использованием A в качестве псевдонима.
Это сказал, что я не уверен, насколько хороший SQL Server прилагает правильные индексы при создании CTE. У меня были проблемы с этим ранее, и вместо этого использовали переменные таблицы с большим успехом.
Несколько точки:
Параметры командной строки:
доступно через commandArgs ()
, поэтому
см. справку (commandArgs)
для
обзор.
Файл Rscript.exe
можно использовать на всех платформах, включая Windows. Он будет поддерживать commandArgs ()
. littler можно было портировать на Windows, но сейчас он работает только на OS X и Linux.
В CRAN имеются два дополнительных пакета: getopt и optparse , которые оба были написаны для анализа командной строки.
Редактировать в Ноя 2015: Появились новые альтернативы, и я от всего сердца рекомендую docopt .
-121--558348-Существует несколько способов расширения приложения Rails и/или запуска одного из них. Другие:
Templates Обычно используются для запуска приложения, но если они разработаны правильно, их можно использовать для расширения. Например, многие шаблоны запаса там помогают настроить репозиторий кода, установить несколько плагинов/драгоценных камней и, возможно, немного исправить макет, чтобы вам не пришлось. Конечно, вы обычно делаете большинство из них, когда впервые начинаете проект. В нашей компании (3 разработчика) я создал шаблон, который настраивает наши среды разработки и производства, репозитории, отслеживание проектов. Вот мой любимый шаблон запаса: http://github.com/lhoeg/app_lego/network (Оригинал не сделал много работы над ним за некоторое время, и я считаю, что это лучшая вилка там сейчас)
плагины и самоцветы более или менее становятся синонимами, и нет никаких оснований для людей создавать плагины, так как драгоценные камни являются более Ruby-стандартный способ упаковки функциональности. Как правило, вы будете использовать плагины, чтобы добавить биты очень определенных функций в ваше приложение. Это может варьироваться от добавления аутентификации пользователя до календаря, до добавления расположений и сопоставления с вашим приложением. Большинство подключаемых модулей, которые я использовал, расширяют ActiveRecord или часть базы данных приложения. Если плагин поставляется с представлениями, контроллерами или изменяет таблицы базы данных, он обычно поставляется с генератором. Хорошим примером является генератор плагина Restful Authentication. Относительно хороший список http://agilewebdevelopment.com/
Двигатели являются королями модульности. Они обычно обеспечивают гораздо больше функциональности, чем плагины. В то время как плагины и драгоценные камни, как правило, расширяют саму Rails, двигатели намерены расширить ВАШЕ приложение, добавив полный набор видов, контроллеров и моделей. Таким образом, двигатели представляют собой целые приложения Rails, упакованные в аккуратную коробку, которую можно просто встроить в приложение и мгновенно добавить набор функциональных возможностей. Существует также каталог Engine по адресу http://agilewebdevelopment.com/ , но, как вы увидите, их не так много.
Надеюсь, это поможет!
Бернс
-121--4244760-Я воспроизвел поведение в SQL 2008 SP1,замена пользовательского элемента SQL для CLF UDF dbo. GetAncestors. Я попробовал и возвращающую табличное значение функцию, и встроенную функцию; ни один из них не изменил ситуацию.
Я еще не знаю, что происходит, но польза других, я включу свои определения ниже.
-- try a recursive inline UDF...
CREATE FUNCTION dbo.GetAncestors(@hierarchyid hierarchyid)
RETURNS TABLE AS RETURN (
WITH recurse AS (
SELECT @hierarchyid AS Ancestor
WHERE @hierarchyid IS NOT NULL
UNION ALL
SELECT Ancestor.GetAncestor(1) FROM recurse
WHERE Ancestor.GetAncestor(1) IS NOT NULL
)
SELECT * FROM recurse
)
-- ...or a table-valued UDF, it makes no difference
CREATE FUNCTION dbo.GetAncestors(@hierarchyid hierarchyid)
RETURNS @return TABLE (Ancestor hierarchyid)
AS BEGIN
WHILE @hierarchyid IS NOT NULL BEGIN
INSERT @return (Ancestor)
VALUES (@hierarchyid)
SET @hierarchyid = @hierarchyid.GetAncestor(1)
END
RETURN
END
Выберите одно из приведенных выше определений, а затем выполните его, чтобы посмотреть, как оно висит:
DECLARE @IDs UniqueIntTable
INSERT @IDs SELECT ID FROM Hierarchy
RAISERROR('we have inserted %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
SELECT * FROM dbo.GoodFunction(@IDs) a
RAISERROR('we have returned %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
GO
DECLARE @IDs UniqueIntTable
INSERT @IDs SELECT ID FROM Hierarchy
RAISERROR('we have inserted %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
SELECT * FROM dbo.BadFunction(@IDs) a
RAISERROR('we have returned %i rows.',-1,-1,@@ROWCOUNT) WITH NOWAIT
GO
Вторая партия никогда даже не начинается . Он проходит стадию синтаксического анализа, но, похоже, теряется где-то между привязкой и оптимизацией.
Тела обеих функций компилируются в один и тот же план выполнения, вне оболочки функции:
SET SHOWPLAN_TEXT ON
GO
DECLARE @IDs UniqueIntTable
INSERT @IDs SELECT ID FROM Hierarchy
SELECT p.ID, p.Node, p.Name, p.[Level]
FROM
(
SELECT DISTINCT a.Ancestor AS Node
FROM Hierarchy c
CROSS APPLY dbo.GetAncestors_IF(c.Node.GetAncestor(1)) a
WHERE c.ID IN (SELECT Value FROM @IDs)
) np
INNER JOIN Hierarchy p
ON p.Node = np.Node
;WITH Ancestors_CTE AS
(
SELECT DISTINCT a.Ancestor AS Node
FROM Hierarchy c
CROSS APPLY dbo.GetAncestors_IF(c.Node.GetAncestor(1)) a
WHERE c.ID IN (SELECT Value FROM @IDs)
)
SELECT p.ID, p.Node, p.Name, p.[Level]
FROM Ancestors_CTE ac
INNER JOIN Hierarchy p
ON p.Node = ac.Node
-- both return this:
|--Nested Loops(Inner Join, OUTER REFERENCES:([p].[Node]))
|--Compute Scalar(DEFINE:([p].[Level]=[Scratch].[dbo].[Hierarchy].[Level] as [p].[Level]))
| |--Compute Scalar(DEFINE:([p].[Level]=[Scratch].[dbo].[Hierarchy].[Node] as [p].[Node].GetLevel()))
| |--Index Scan(OBJECT:([Scratch].[dbo].[Hierarchy].[IX_Hierarchy_Node] AS [p]))
|--Top(TOP EXPRESSION:((1)))
|--Filter(WHERE:([Recr1005]=[Scratch].[dbo].[Hierarchy].[Node] as [p].[Node]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([c].[Node]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Value]))
| |--Clustered Index Scan(OBJECT:(@IDs))
| |--Clustered Index Seek(OBJECT:([Scratch].[dbo].[Hierarchy].[PK_Hierarchy] AS [c]), SEEK:([c].[ID]=[Value]) ORDERED FORWARD)
|--Index Spool(WITH STACK)
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1011]=(0)))
| |--Constant Scan(VALUES:(([Scratch].[dbo].[Hierarchy].[Node] as [c].[Node].GetAncestor((1)))))
|--Assert(WHERE:(CASE WHEN [Expr1013]>(100) THEN (0) ELSE NULL END))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1013], [Recr1003]))
|--Compute Scalar(DEFINE:([Expr1013]=[Expr1012]+(1)))
| |--Table Spool(WITH STACK)
|--Compute Scalar(DEFINE:([Expr1004]=[Recr1003].GetAncestor((1))))
|--Filter(WHERE:(STARTUP EXPR([Recr1003].GetAncestor((1)) IS NOT NULL)))
|--Constant Scan
Очень интересно. Отправьте отчет об ошибке в Microsoft Connect, попросите их сообщить вам, что происходит.
Насколько я понимаю, при использовании CTE в пакетном режиме оператор должен заканчиваться знаком «;». Это как-то связано с интерпретацией предложения WITH. Попробуйте это:
IF OBJECT_ID('_HappyFunction' ) IS NOT NULL DROP FUNCTION _HappyFunction
IF OBJECT_ID('_NowHappyFunction') IS NOT NULL DROP FUNCTION _NowHappyFunction
IF TYPE_ID ('_UniqueIntTable') IS NOT NULL DROP TYPE _UniqueIntTable
GO
CREATE TYPE _UniqueIntTable AS TABLE (Value int NOT NULL PRIMARY KEY)
GO
CREATE FUNCTION _HappyFunction (@IDs _UniqueIntTable READONLY)
RETURNS TABLE AS RETURN
SELECT Value FROM @IDs
GO
CREATE FUNCTION _NowHappyFunction (@IDs _UniqueIntTable READONLY)
RETURNS @Table TABLE
(
Value INT
)
BEGIN
;WITH CTE AS (SELECT Value FROM @IDs)
INSERT INTO @Table
SELECT Value FROM CTE
RETURN
END
GO
-- this will return an empty record set
DECLARE @IDs _UniqueIntTable
SELECT * FROM _HappyFunction(@IDs)
GO
-- this will no longer hang and will also return an empty record set
DECLARE @IDs _UniqueIntTable
SELECT * FROM _NowHappyFunction(@IDs)
GO