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

Откуда: Краснодар
Сообщений: 20
В таблице содержатся номера бланков строгой отчетности. Такой запрос нужен для выдачи диапазонов бланков. В нем ищутся левые концы интервалов. Есть естественно более длинный запрос, но вроде как из-за этого куска запрос зависает. Сначала работал нормально, но потом с ростом таблицы начал зависать.
2 дек 11, 00:52    [11693208]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Что показало вскрытие...
Косяк реально есть.. причем для меня было открытие что LEFT JOIN с NULL работает медленнее чем NOT IN....
Ну да ладно...

exec sp_executesql N'SELECT T1._Fld11345 FROM _AccumRg10557 T1 WITH(NOLOCK) WHERE (NOT ((T1._Fld11345 IN (SELECT
(T2._Fld11346) AS Q_001_F_000_ FROM _AccumRg10557 T2 WITH(NOLOCK) WHERE (T1._Fld11345 = (T2._Fld11346)))))) GROUP BY T1._Fld11345', N'@P1 numeric(1,0)', 1


Отлетает на ура...
Но как Вы видите я добавила поле ( физически) и сделала ему +1 по номера.

Вполне ясно что Вам регистр так пучить не надо.

Сделайте запрос в 1c

ВЫБРАТЬ
	ДвиженияБСО.НомерНачальный+1 Как Номер1 
В
      промежуток
ИЗ
	РегистрНакопления.ДвиженияБСО КАК ДвиженияБСО
СГРУППИРОВАТЬ ПО
	ДвиженияБСО.НомерНачальный+1

ВЫБРАТЬ
	ДвиженияБСО.НомерНачальный
ИЗ
	РегистрНакопления.ДвиженияБСО КАК ДвиженияБСО
ГДЕ
	(НЕ ДвиженияБСО.НомерНачальный В
				(ВЫБРАТЬ
					ДвиженияБСО1.Номер1 
				ИЗ
					      промежуток)
СГРУППИРОВАТЬ ПО
	ДвиженияБСО.НомерНачальный





Вообще запуском sp_executesql еще со времен 7 SQL старалась не пользоваться, по одной простой причине, динамические запросы игнорировали ( как мне казалось) индексы и есть проблемы с производительность.
Да и поле надо бы индексировать, по хорошему.... Но это +1 индекс и не факт что база не начнет пухнуть.
2 дек 11, 06:49    [11693387]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
max11111
Member

Откуда: Краснодар
Сообщений: 20
Volochkova, спасибо большое за идею. Хотелось в одном запросе, но все равно намного быстрее получается. Интересно на 2008 сервере есть тот же глюк?
2 дек 11, 10:11    [11693901]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
SQL2008R2 - тот же глюк )
Но это сложно назвать глюком...
Вы в Вашем тестовом клоне оставьте 1000 записей и посмотрите планы запросов...

Отличаются тем, что ( т.к. нет индекса) идет скан таблицы.. но это не так критично...
Хуже всего то, то "не включается оптимизатор"..
Скан табличек идет одним ядром..... И видимо раньше успевал, а теперь все.... не те мощности..

А вот если выполнить такой запрос без Execsql, то SQL включает параллельное сканирование таблицы...
2 дек 11, 10:20    [11693957]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
max11111
Member

Откуда: Краснодар
Сообщений: 20
Volochkova, а почему не глюк, оптимизатор то "не включается" ?
2 дек 11, 10:45    [11694114]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
max11111
Volochkova, а почему не глюк, оптимизатор то "не включается" ?



Вам бы вместо пачек запаянных дали бы мешок денег.... в монетах
Да еще бы и отсортировать бы попросили....
2 дек 11, 10:49    [11694147]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
max11111
Member

Откуда: Краснодар
Сообщений: 20
Volochkova, понял. Динамический запрос - зло :)
2 дек 11, 10:54    [11694173]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
max11111
Volochkova, понял. Динамический запрос - зло :)


А 1с к сожалению иначе никак...
2 дек 11, 10:58    [11694207]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Ув. Volochkova, у меня планы одинаковы
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (Intel X86)
Jun 24 2011 13:19:23 Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 3)
в ssms, в окне первого подключения создается тестовая табличка и заполняется данными
+
set nocount on;
use tempdb;
go
if OBJECT_ID('dbo._AccumRg10557', 'U') is not null drop table dbo._AccumRg10557;
go
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go
create table [dbo].[_AccumRg10557]
( [_Period] [datetime] not null
, [_RecorderTRef] [binary](4) not null
, [_RecorderRRef] [binary](16) not null
, [_LineNo] [numeric](9, 0) not null
, [_Active] [binary](1) not null
, [_RecordKind] [numeric](1, 0) not null
, [_Fld11345] [numeric](10, 0) not null
, [_Fld10563RRef] [binary](16) not null
, [_Fld10562RRef] [binary](16) not null
, [_Fld10564RRef] [binary](16) not null
, [_Fld10558RRef] [binary](16) not null
, [_Fld10559RRef] [binary](16) not null
, [_Fld10560RRef] [binary](16) not null
, [_Fld10561RRef] [binary](16) not null
, [_Fld10565] [numeric](10, 0) not null
, [_Fld10567] [numeric](10, 0) not null )
on [primary];
go
--create index _AccumRg10557_Fld11345 on dbo._AccumRg10557(_Fld11345);
go
declare @i int = 0, @guid binary(16);
while @i < 1000 begin
 set @guid = newid();
 insert into dbo._AccumRg10557
 ( _Period
 , _RecorderTRef, _RecorderRRef
 , _LineNo
 , _Active, _RecordKind
 , _Fld11345
 , _Fld10563RRef, _Fld10562RRef, _Fld10564RRef
 , _Fld10558RRef, _Fld10559RRef, _Fld10560RRef, _Fld10561RRef
 , _Fld10565, _Fld10567 )
 values
 ( dateadd(d, @i % 30, '20111101')
 , substring(@guid, 1, 4), @guid
 , abs(checksum(newid())) % 1000000000
 , abs(checksum(newid())) % 2, @i % 3
 , @i + abs(checksum(newid())) % 2 + 1
 , @guid, @guid, @guid
 , @guid, @guid, @guid, @guid
 , abs(checksum(newid())), abs(checksum(newid())) );
 set @i += 1;
end;
--select * from dbo._AccumRg10557;
в окне второго подключения выполняется прямой запрос
+
set nocount on;
use tempdb;
go
checkpoint;
dbcc dropcleanbuffers;
go
declare @p1 numeric(1,0) = 1;
select _Fld11345
  from dbo._AccumRg10557 as o with(nolock)
 where not(_Fld11345 in (select i._Fld11345 + @p1
                           from dbo._AccumRg10557 as i with(nolock)
                          where i._Fld11345 = o._Fld11345 - @p1))
-- where _Fld11345 != (select coalesce(max(i._Fld11345), o._Fld11345) + @p1
--                      from dbo._AccumRg10557 as i with(nolock)
--                     where o._Fld11345 > i._Fld11345)
 group by o._Fld11345
--option (maxdop 1)
;
получается следующий реальный план исполнения

К сообщению приложен файл (not-sp_executesql.sqlplan - 13Kb) cкачать
2 дек 11, 12:14    [11695091]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
для динамического sql
+
set nocount on;
use tempdb;
go
--drop index _AccumRg10557_Fld11345 on dbo._AccumRg10557;
checkpoint;
dbcc dropcleanbuffers;
go
exec sp_executesql N'
select _Fld11345
  from dbo._AccumRg10557 as o with(nolock)
 where not (_Fld11345 in (select i._Fld11345 + @p1
                            from dbo._AccumRg10557 as i with(nolock)
                           where i._Fld11345 - @p1 = o._Fld11345))
 group by o._Fld11345
'
, N'@p1 numeric(1,0)', 1;
и реальный план исполнения

К сообщению приложен файл (sp_executesql.sqlplan - 13Kb) cкачать
2 дек 11, 12:16    [11695114]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Бывает.
1. Я гоняла на триал версии.
2. У вас и запросы разные
where i._Fld11345 = o._Fld11345 - @p1
против
where i._Fld11345 - @p1 = o._Fld11345

Да и тут же пробегала база...
Гоняйте на ней

Еще и в базе TempDB....

Ну простите, я так не умею...
2 дек 11, 13:00    [11695509]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
скрипты исправлены, выполнение перенесено в другую базу
+
set nocount on;
use Puzzles;
go
checkpoint;
dbcc dropcleanbuffers;
go
declare @p1 numeric(1,0) = 1;
select _Fld11345
  from dbo._AccumRg10557 as o with(nolock)
 where not(_Fld11345 in (select i._Fld11345 + @p1
                           from dbo._AccumRg10557 as i with(nolock)
                          where i._Fld11345 + @p1 = o._Fld11345))
-- where _Fld11345 != (select coalesce(max(i._Fld11345), o._Fld11345) + @p1
--                      from dbo._AccumRg10557 as i with(nolock)
--                     where o._Fld11345 > i._Fld11345)
 group by o._Fld11345
--option (maxdop 1)
;
планы не изменились
2 дек 11, 13:10    [11695613]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
реальный план исполнения для прямого запроса

К сообщению приложен файл (not-sp_executesql.sqlplan - 13Kb) cкачать
2 дек 11, 13:11    [11695628]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
для динамического sql скрипт и реальный план
+
set nocount on;
use Puzzles;
go
--drop index _AccumRg10557_Fld11345 on dbo._AccumRg10557;
checkpoint;
dbcc dropcleanbuffers;
go
exec sp_executesql N'
select _Fld11345
  from dbo._AccumRg10557 as o with(nolock)
 where not (_Fld11345 in (select i._Fld11345 + @p1
                            from dbo._AccumRg10557 as i with(nolock)
                           where i._Fld11345 + @p1 = o._Fld11345))
 group by o._Fld11345
'
, N'@p1 numeric(1,0)', 1;


К сообщению приложен файл (sp_executesql.sqlplan - 13Kb) cкачать
2 дек 11, 13:14    [11695648]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
попробовал на предоставленной max11111 бд - результаты те же, что и для tempdb и отдельной бд
2 дек 11, 13:59    [11696068]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
max11111
Member

Откуда: Краснодар
Сообщений: 20
andrey odegov, так у Вас проблемы с зависанием есть?
2 дек 11, 14:25    [11696290]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Ув. andrey odegov.
Я уже в выходных и допуска до сервера нет, но есть под рукой экспресс версия.
Проверила - планы разные.
SELECT 
Top 1000
	[_Fld11345]
INTO 
[_AccumRg10558]
  FROM [Test1].[dbo].[_AccumRg10557]

SELECT 
	[_Fld11345]
INTO 
[_AccumRg10559]
  FROM [Test1].[dbo].[_AccumRg10558]


exec sp_executesql N'SELECT T1._Fld11345 FROM _AccumRg10558 T1 WITH(NOLOCK) WHERE (NOT ((T1._Fld11345 IN (SELECT
(T2._Fld11345 +@P1 ) AS Q_001_F_000_ FROM _AccumRg10558 T2 WITH(NOLOCK) WHERE (T1._Fld11345 = (T2._Fld11345 + @P1)))))) GROUP BY T1._Fld11345', N'@P1 numeric(1,0)', 1


SELECT T1._Fld11345 FROM _AccumRg10558 T1 WITH(NOLOCK) WHERE (NOT ((T1._Fld11345 IN (SELECT
(T2._Fld11345 +1 ) AS Q_001_F_000_ FROM _AccumRg10558 T2 WITH(NOLOCK) WHERE (T1._Fld11345 = (T2._Fld11345 + 1)))))) GROUP BY T1._Fld11345

SELECT T1._Fld11345 FROM _AccumRg10559 T1 WITH(NOLOCK) WHERE (NOT ((T1._Fld11345 IN (SELECT
(T2._Fld11345 +1 ) AS Q_001_F_000_ FROM _AccumRg10559 T2 WITH(NOLOCK) WHERE (T1._Fld11345 = (T2._Fld11345 + 1)))))) GROUP BY T1._Fld11345



AccumRg10558 - 1000 записей из таблички автора
AccumRg10559 - 1000 записей из таблички автора, есть кластерный индекс

У меня +1 сразу в тексте.
Планы для 2-3 одинаковые, план для 1 отличный.

По базам могла ошибиться, но ИМХО, автору мой совет должен помочь.
2 дек 11, 15:20    [11696833]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
max11111, на миллионе строк - да
и для прямого запроса и для динамического sql
но это не "зависание" - просто так долго идет
выполнение запросов
я тестировал для 1000, 10000 строк окончания дождался
для 100000 строк - уже нет
а Вы индекс создать для этой таблицы можете?
2 дек 11, 16:28    [11697599]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
max11111
Member

Откуда: Краснодар
Сообщений: 20
andrey odegov, у меня в базе такой индекс

SE [Buh8]
GO
/****** Object:  Index [_Accum10557_ByDims11346_NTRN]    Script Date: 12/02/2011 16:45:09 ******/
CREATE UNIQUE NONCLUSTERED INDEX [_Accum10557_ByDims11346_NTRN] ON [dbo].[_AccumRg10557] 
(
	[_Fld11345] ASC,
	[_Period] ASC,
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
2 дек 11, 16:56    [11697856]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
max11111, а каков будет результат если в запросе 1C условие во внутреннем запросе заменить на
ГДЕ ДвиженияБСО.НомерНачальный - 1 = ДвиженияБСО1.НомерНачальный
, что должно бы изменить условие внуреннего запроса sql на
WHERE T1._Fld11345 - @P1 = T2._Fld11345
, что в свою очередь должно привести к использованию вышеприведенного Вами индекса
у меня он был использован и для запросов на Вашей бд и в моих примерах для количества строк 1000000
общее время выполнения прямого и динамического запросов с параметрами составило ~20 сек
2 дек 11, 20:09    [11698912]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Если -1 вынести в верхний запрос, то он не вернет последний номер....
Итого результат может оказаться неверным на 1 запись.
3 дек 11, 02:49    [11700224]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Volochkova, не нужно выносить -1 во внешний запрос
цель - заставить оптимизатор сканировать индекс во внешнем
запросе и выполнять индексный поиск во внутреннем
3 дек 11, 13:53    [11700701]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Вы В запросах ставите у o._Fld11345
А именно оно сканится на вхождение...
В итоге самый последний номер не попадет в not in (Select)
3 дек 11, 15:03    [11700814]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
Ув. Volochkova,
есть таблица _AccumRg10557 (далее T)
в таблице T есть колонка _Fld11345 (далее C)
найти такие значения в C, для которых среди
значений в C нет меньших на 1.

заполним нашу T строками со следующими
значениями (для простоты отсортированными
по возрастанию) в C: 1, 2, 5, ..., 19 и 23

пусть O - внешний запрос, I (или i) - внутренний, а S - результат

S = ()
для каждого значения в C из O
старт
RSi = вернуть из I значения I.C + 1, для которых верно I.C + 1 = O.C
IN = в RSi есть значения равные текущему O.C?
IN = not IN
если IN, то S += O.C
финиш

для O.C = 1 RSi = пустое множество,
т.к среди значений C нет 0 (+ 1 = 1) => IN = false (далее F),
отрицание IN дает true (далее T) и 1 попадает в S

для O.C = 2 RSi = (2),
т.к среди значений C есть 1 (+ 1 = 2) => IN = T,
отрицание IN дает F и 2 не попадает в S

для O.C = 5 RSi = (),
т.к среди значений C нет 4 (+ 1 = 5) => IN = F,
отрицание IN дает T и 5 попадает в S

для O.C = 23 RSi = (),
т.к среди значений C нет 22 (+ 1 = 23) => IN = F,
отрицание IN дает T и 23 попадает в S
--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8--->8
S = ()
для каждого значения в C из O
старт
RSi = вернуть из I значения I.C + 1, для которых верно O.C - 1 = I.C
IN = в RSi есть значения равные текущему O.C?
IN = not IN
если IN, то S += O.C
финиш

для O.C = 1 RSi = (),
т.к среди значений C нет (1 - 1 =) 0 => IN = F,
отрицание IN дает T и 1 попадает в S

для O.C = 2 RSi = (2),
т.к среди значений C есть (2 - 1 =) 1 => IN = T,
отрицание IN дает F и 2 не попадает в S

для O.C = 5 RSi = (),
т.к среди значений C нет (5 - 1 =) 4 => IN = F,
отрицание IN дает T и 5 попадает в S

для O.C = 23 RSi = (),
т.к среди значений C нет (23 - 1 =) 22 => IN = F,
отрицание IN дает T и 23 попадает в S

что не так?
3 дек 11, 17:25    [11701153]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с sp_executesql  [new]
Volochkova
Member

Откуда:
Сообщений: 2321
Простите, ничего не поняла.
4 дек 11, 07:00    [11702285]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить