Cruel SQL


out of memory while reading tuples при импорте из PostgreSQL

Импортировал давеча SSIS-ом в меру увесистую (несколько миллионов записей) таблицу на SQL Server из PostgreSQL. Скачал официальный бесплатный ODBC-драйвер ( http://www.postgresql.org/ftp/odbc/versions/ ), пульнул DataFlow.

И всё бы пучком, но на небольших выборках.
А вот забрать всю таблицу разом не получалось. Вместо данных вылезала ошибка из сабжа.
Погуглил. Ну ладно, может, лениво погуглил, но, кроме каких-то невнятных кувырков с импортированием данных по кускам и рекламы коммерческих OLEDB-шных провайдеров, ничего не нашёл. Пришлось действовать методом тыка.

В общем, коллеги, если у вас вдруг вскочит аналогичный прыщ по работе, то вот рецепт.
В ConnectionString-е находим свойство useserversideprepare=0 и проставляем там единичку вместо нуля. Ошибка out of memory возникает на уровне ODBC-шного драйвера при подготовке данных на стороне клиента и, соответственно, исчезает при виде этой самой единички.

Ну и да, если импорт запускается по расписанию, нужно в свойствах jobstep-а проставить выполнение в 32-битной среде, иначе валится с мутной ошибкой о невозможности подключения.
добавлено: 17 май 17 просмотры: 1092, комментарии: 0



Вычисление перцентиля на T-SQL

Абсолютно неоригинальная задача, но для тех, кто ищет готовое решение, а не советы/ссылки - примерно так:

create type dbo.FloatArray as table (Val float not null);
go
create function dbo.GetPercentil(@Percent tinyint, @Values dbo.FloatArray readonly) 
returns float
as begin
	return (select	avg(p.Val)
	from	(
				select	mr.Val 
					,	IsPercentilVal = cast(case when mr.RowNum between mr.PercentRow and mr.PercentRow + 1 then 1 else 0 end as bit)
				from	(
							select	r.Val 
								,	r.RowNum 
								,	PercentRow	= cast((count(*) over()) * @Percent / 100.0 as float)
							from	(
										select	v.Val 
											,	RowNum = row_number() over (order by v.Val)
										from	@Values as v
									) as r
						) as mr
			) as p
	where	p.IsPercentilVal = 1);
end;
добавлено: 30 мар 13 просмотры: 1179, комментарии: 0



Транспонирование произвольного ряда на T-SQL

Отнюдь не всем и не часто, но может пригодиться. Если нужно представить одну строку (например, из какой-нибудь настроечной таблицы) в виде набора строк "имя поля - значение", то можно, в частности, поступить вот так.

; with	CTE_SingleRowXML as (
			select	x = (select * from dbo.[ВашаТаблица] for xml path(''), elements xsinil, type)
		)
select	ColName		= v.value('local-name(.)', 'sysname')
	,	Value		= v.value('text()[1]', 'nvarchar(max)')
from	CTE_SingleRowXML as srx
		cross apply srx.x.nodes('*') as f(v);


При добавлении/удалении полей из таблицы такой скрипт переписывать (в отличие от скрипта с использованием штатного unpivot-а) не придётся (тут есть и плюсы, и минусы, поэтому просто назову это особенностью :))
добавлено: 30 мар 13 просмотры: 1088, комментарии: 0



Генерация диапазонов чисел и дат на T-SQL

Как-то раз столкнулся с неприятной багой. А именно - для получения диапазона дат в качестве исходного массива использовалась системная таблица в молчаливом предположении, что уж в ней-то записей достаточно. И вот при формировании отчёта за большой отрезок времени даты с определённого момента просто не появлялись.

Соответственно, задался вопросом - как это правильно сделать-то, не закладываясь ни на какие таблицы. Быстрее всего, понятное дело, отработает CLR-ная функция. А если по правилам игры CLR использовать нельзя?

В итоге получилась вот такая штука.

CREATE FUNCTION dbo.GetIntRange ( @StartNum int, @EndNum int)
RETURNS TABLE
AS
	RETURN 
		WITH NumberRange AS (
			SELECT 1 AS Num
			UNION ALL
			SELECT
			  nr.Num * 2 + t.RecType  
			FROM NumberRange nr
			CROSS JOIN (
				SELECT 0 AS RecType
				UNION ALL 
				SELECT 1
			) t
			WHERE nr.Num * 2 + t.RecType <= @EndNum - @StartNum + 1
		)
		SELECT @StartNum + Num  - 1 AS Num
		FROM NumberRange;
GO

CREATE FUNCTION dbo.GetDateRange ( @StartDate date, @EndDate date )
RETURNS TABLE
AS
	RETURN
		SELECT DATEADD(DAY, gsr.Num - 1, @StartDate) AS [dDate]
		FROM dbo.GetIntRange(1, DATEDIFF(DAY, @StartDate, @EndDate)) gsr

GO

SELECT * 
FROM dbo.GetDateRange('19000101',SYSDATETIME())
ORDER BY dDate 

Для не особо больших диапазонов даже довольно быстро работает.

UPD. Если вдруг случилось, что этот пост - первое, что нашлось по генерации последовательностей, то настоятельно советую прочитать как минимум первые шесть комментариев.

UPD2. Если после прочтения комментариев желания или возможности делать отдельную таблицу всё ж не появилось, а запрос упирается в быстродействие, то для небольших диапазонов (примерно до 20000 значений) слегка поправить ситуацию может использование вот такой конструкции:

DECLARE @StartNum int = 10, @EndNum int = 20000;

WITH NumXML AS (
	SELECT CONVERT(xml,REPLICATE ('<r/>', 1000)) AS x
),
GenRows AS (
	SELECT 1 AS t
	FROM NumXML n
	CROSS APPLY x.nodes('r') AS r(nn)
)
SELECT TOP(@EndNum - @StartNum + 1) 
	ROW_NUMBER() OVER (ORDER BY @StartNum) + @StartNum - 1
FROM GenRows gr1
CROSS JOIN GenRows gr2;

(будет работать примерно в 6-10 раз быстрее варианта с CTE)
добавлено: 23 ноя 11 просмотры: 3883, комментарии: 10