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

Откуда:
Сообщений: 118
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c)
1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6001: Service Pack 1)


Есть запрос:
declare @period datetime
declare @greu varchar(4)

set @period='2009.06.01'
set @greu='2-8'

declare @tbl table(
	storage_id numeric(18,0),
	lshn varchar(12),
	fio_s varchar(35),
	account_id numeric(18,0),
	flat_id numeric(18,0),
	service_id numeric(18,0),
	comment varchar(100),
	amount numeric(12,2),
	pt_comment varchar(100),
	provider_id numeric(18,0)
)

declare @grp table(
	greu varchar(4),
	kod_text varchar(6)
)

insert into @grp select greu, kod_text from fMakeGRP2()

insert into @tbl
select ss.storage_id,ss.lshn, ss.fio_s, ss.account_id, ss.flat_id, s.service_id ,s.comment 'Услуга',
 ss.amount 'Стоимость', pt.comment 'Требуется', ss.provider_id 'id поставщика' 
from hStorageService ss, hService s, hProviderType pt
where period=@period
and left(lshn,6) in (select kod_text from @grp where greu=@greu)
and ss.service_id=s.service_id
and pt.type_id in (select type_id from hProviderService ps where ps.service_id=s.service_id and period=@period)


select storage_id, lshn, fio_s, account_id, flat_id, service_id , comment 'Услуга', amount 'Стоимость',
 pt_comment 'Требуется', provider_id 'id поставщика' from @tbl

select service_id, comment 'Услуга', count(*) 'Кол-во' from @tbl group by service_id, comment order by service_id
, который стабильно выполняется за полторы минуты. Основная таблица, откуда берутся данные - это hStorageService. В ней около 40 млн. записей, из которых периоду '2009.06.01' соответствуют 2 млн.
Далее, есть этот же код, но ввиде хранимой процедуры:
alter procedure [dbo].[phViewChargesWithAnyPrividerIDForGREU] (@period datetime, @greu varchar(4))
as 
begin

declare @tbl table(
	storage_id numeric(18,0),
	lshn varchar(12),
	fio_s varchar(35),
	account_id numeric(18,0),
	flat_id numeric(18,0),
	service_id numeric(18,0),
	comment varchar(100),
	amount numeric(12,2),
	pt_comment varchar(100),
	provider_id numeric(18,0)
)

declare @grp table(
	greu varchar(4),
	kod_text varchar(6)
)

insert into @grp select greu, kod_text from fMakeGRP2()

insert into @tbl
select ss.storage_id,ss.lshn, ss.fio_s, ss.account_id, ss.flat_id, s.service_id ,s.comment 'Услуга',
 ss.amount 'Стоимость', pt.comment 'Требуется', ss.provider_id 'id поставщика' 
from hStorageService ss, hService s, hProviderType pt
where period=@period
and ss.service_id=s.service_id
and pt.type_id in (select type_id from hProviderService ps where ps.service_id=s.service_id and period=@period)
and left(lshn,6) in (select kod_text from @grp where greu=@greu)

select storage_id, lshn, fio_s, account_id, flat_id, service_id , comment 'Услуга', amount 'Стоимость',
 pt_comment 'Требуется', provider_id 'id поставщика' from @tbl
select service_id, comment 'Услуга', count(*) 'Кол-во' from @tbl group by service_id, comment order by service_id
end

Данная процедура с такими же параметрами:
exec phViewChargesWithAnyPrividerIDForGREU '2009.06.01', '2-8'
выполняется уже не полторы минуты, а стабильно полчаса.

Большую часть времени в обоих случаях занимает селект в табличную переменную.
Планы запросов в двух случаях различаются, но в обоих случаях более 90% времени уходит на самую первую операцию: Clustered Index Scan для таблицы hStorageservice c предикатом
<ScalarOperator ScalarString="[jurist].[dbo].[hStorageService].[period] as [ss].[period]=[@period]">
.
Только в случае запроса скан выполняется непосредственно на исходной таблице:
<RunTimeInformation>
	<RunTimeCountersPerThread Thread="0" ActualRows="1956876" ActualEndOfScans="1" ActualExecutions="1" />
  </RunTimeInformation>
.
А в случае с хп исходная таблица по-ходу увеличивается в 30 раз с прицелом на последующее соединение:
<RunTimeInformation>
	<RunTimeCountersPerThread Thread="0" ActualRows="58706280" ActualEndOfScans="30" ActualExecutions="30" />
  </RunTimeInformation>
.
Читал здесь же статью, но версия сервера отличается, да и все равно непонятно мне как заставить хп выполняться по более подходящему плану запроса. Может нужно как-то переиначить исходный селект?
30 июн 09, 09:25    [7357220]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
Упс, только сейчас обратил внимание, что запросы немного разные: критерий
and left(lshn,6) in (select kod_text from @grp where greu=@greu)
в разных местах находится. Проверяю его влияние на постороение плана запроса...
30 июн 09, 09:32    [7357239]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
После изменения процедура все равно выполняется очень медленно. Использует старый план запроса. Пробовал перекомпилировать процедуру. Даже создал другую с другим именем и тем же кодом. Все равно не помогает. В чем может быть причина?
30 июн 09, 10:11    [7357400]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
harrique
...Читал здесь же статью, но версия сервера отличается...
И попробовать не удалось?
30 июн 09, 10:15    [7357415]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
tpg
И попробовать не удалось?

А что именно попробовать? В статье немного другая ситуация. Изменил процедуру, как там написано:
alter procedure [dbo].[phViewChargesWithAnyProviderIDForGREU] (@period datetime, @greu varchar(4), @def_period datetime='2009.06.01', @def_greu varchar(4)='2-8')
as 
begin

set @def_period=@period
set @def_greu=@greu
.
.
.
Результата это не дало.
30 июн 09, 11:22    [7357821]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
step_ks
Member

Откуда:
Сообщений: 936
а так
alter procedure [dbo].[phViewChargesWithAnyProviderIDForGREU] (@period datetime='2009.06.01', @greu varchar(4)='2-8')
as 
begin
.
.
.
30 июн 09, 12:06    [7358190]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
flexgen
Member

Откуда: Город на песке
Сообщений: 765
harrique,

fMakeGRP2() это функция?
30 июн 09, 12:40    [7358455]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
flexgen
harrique,

fMakeGRP2() это функция?

Да, это функция. А что? Я специально результаты ее работы поместил в табличную переменную, чтобы сто тыщ раз не вызывалась.

step_ks
а так
alter procedure [dbo].[phViewChargesWithAnyProviderIDForGREU] (@period datetime='2009.06.01', @greu varchar(4)='2-8')
as 
begin
.
.
.

Так тоже использует медленный план. А если бы что-то и изменилось, какой в этом был бы смысл - параметры-то как-то менять надо.
30 июн 09, 13:26    [7358750]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
step_ks
Member

Откуда:
Сообщений: 936
harrique

Так тоже использует медленный план. А если бы что-то и изменилось, какой в этом был бы смысл - параметры-то как-то менять надо.

Ну так и меняли бы как обычно. Другое дело, что не повезло с таким вариантом.
30 июн 09, 13:58    [7359006]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
step_ks
Member

Откуда:
Сообщений: 936
и где dll таблиц и индексов?
30 июн 09, 14:00    [7359025]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
step_ks
Member

Откуда:
Сообщений: 936
step_ks
и где dll таблиц и индексов?

конечно же, имелось ввиду ddl
30 июн 09, 14:01    [7359031]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
step_ks
и где ddl таблиц и индексов?

Ну если вам это поможет, то:
USE [jurist]
GO
/****** Object:  Table [dbo].[hStorageService]    Script Date: 06/30/2009 14:08:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hStorageService](
	[storage_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[lshn] [numeric](12, 0) NOT NULL,
	[FIO_S] [varchar](35) COLLATE Cyrillic_General_CI_AS NULL,
	[account_id] [numeric](18, 0) NOT NULL,
	[flat_id] [int] NOT NULL,
	[period] [smalldatetime] NOT NULL,
	[paid_id] [numeric](18, 0) NOT NULL,
	[service_id] [numeric](18, 0) NOT NULL,
	[amount] [numeric](12, 2) NOT NULL,
	[provider_id] [numeric](18, 0) NULL,
 CONSTRAINT [PK_hStorageService] PRIMARY KEY CLUSTERED 
(
	[storage_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [hTables]
) ON [hTables]

GO

/****** Object:  Index [PK_hStorageService]    Script Date: 06/30/2009 14:09:31 ******/
ALTER TABLE [dbo].[hStorageService] ADD  CONSTRAINT [PK_hStorageService] PRIMARY KEY CLUSTERED 
(
	[storage_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [hTables]

USE [jurist]
GO
/****** Object:  Table [dbo].[hService]    Script Date: 06/30/2009 14:10:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[hService](
	[service_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[itog_name] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[comment] [varchar](100) COLLATE Cyrillic_General_CI_AS NULL,
	[parent_id] [numeric](18, 0) NULL,
	[av_name] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[per_name] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
 CONSTRAINT [PK_hService] PRIMARY KEY CLUSTERED 
(
	[service_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[hService]  WITH CHECK ADD  CONSTRAINT [FK_hService_hService] FOREIGN KEY([parent_id])
REFERENCES [dbo].[hService] ([service_id])

USE [jurist]
GO
/****** Object:  Table [dbo].[hProviderType]    Script Date: 06/30/2009 14:11:43 ******/
GO
CREATE TABLE [dbo].[hProviderType](
	[type_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[itog_name] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[comment] [varchar](100) COLLATE Cyrillic_General_CI_AS NOT NULL,
 CONSTRAINT [PK_hProviderType] PRIMARY KEY CLUSTERED 
(
	[type_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

USE [jurist]
GO
/****** Object:  Table [dbo].[hProviderService]    Script Date: 06/30/2009 14:12:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hProviderService](
	[type_id] [numeric](18, 0) NOT NULL,
	[service_id] [numeric](18, 0) NOT NULL,
	[period] [datetime] NOT NULL,
 CONSTRAINT [PK_hProviderService] PRIMARY KEY CLUSTERED 
(
	[type_id] ASC,
	[service_id] ASC,
	[period] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[hProviderService]  WITH CHECK ADD  CONSTRAINT [FK_hProviderService_hProviderType] FOREIGN KEY([type_id])
REFERENCES [dbo].[hProviderType] ([type_id])
GO
ALTER TABLE [dbo].[hProviderService]  WITH CHECK ADD  CONSTRAINT [FK_hProviderService_hService] FOREIGN KEY([service_id])
REFERENCES [dbo].[hService] ([service_id])

Все индексы таблицы hStorageService перечислять не стал, так как в обоих случаях используется только кластеризованный.
30 июн 09, 14:16    [7359133]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
prohodil mimo
Guest
попробуй создавать временную табличку используя синтаксис create table #t... на MSSQL 2005 тоже столкнулись с тем, что если просто запустить запрос, в котором создается временная табличка с @, то работает нормально, как только это дело зашивалось в процедуру, то были тормоза. Нам помогло простое решение, создаем временные таблички используя #.
30 июн 09, 14:17    [7359146]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
step_ks
Member

Откуда:
Сообщений: 936
таблица hProviderType pt с единственным условием на нее pt.type_id in (...) не поджойнена с другими таблицами запроса - это вам так нужно?
30 июн 09, 15:03    [7359441]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
step_ks
таблица hProviderType pt с единственным условием на нее pt.type_id in (...) не поджойнена с другими таблицами запроса - это вам так нужно?

Почему же не поджойнена? Поджойнена (select ... , pt.comment 'Требуется'...)
30 июн 09, 15:09    [7359480]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
prohodil mimo
попробуй создавать временную табличку используя синтаксис create table #t... на MSSQL 2005 тоже столкнулись с тем, что если просто запустить запрос, в котором создается временная табличка с @, то работает нормально, как только это дело зашивалось в процедуру, то были тормоза. Нам помогло простое решение, создаем временные таблички используя #.

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

А вообще я уже давно первый вариант (с запросом без хранимки) жестко зашил в прогу (через задницу конечно, но хоть работает относительно быстро). Просто интересно, что за фигня все-таки.
30 июн 09, 15:15    [7359508]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
harrique,

А вы все-таки попробуйте как предложил мимопроходящий - заменить переменные на таблицы, это не долго:)
30 июн 09, 15:35    [7359656]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
prohodil mimo
создается временная табличка с @
C @ начинается имя табличной переменной, а не таблицы
harrique
и если уж вставлять не в память, а во временную таблицу на диск
То есть, Вы полагаете, что табличная переменная всегда в памяти, а временная таблица - всегда на диске?
Это заблуждение. Обсуждалось на форуме неоднократно.
Для большого количества записей временная таблица намного эффективнее.

Поскольку имеются SELECTы из табличных переменных, то, возможно, стоит создать индексы и у них.
30 июн 09, 15:45    [7359717]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
harrique,

давно как то было...

Beaver
При выборке из временных таблиц SQL server собирает статистику по полям и в зависимости от этого строит план выполнения.
При выборке из таблиц-переменных статистика не собирается и план выполнения всегда один и тот же вне зависимости от данных в таблице-переменной.
Время на сбор статистики может компенсироваться выбором более правильного плана выполнения.


Табличная переменна vs Постоянная таблица
30 июн 09, 15:46    [7359726]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
То есть, Вы полагаете, что табличная переменная всегда в памяти, а временная таблица - всегда на диске?
Это заблуждение. Обсуждалось на форуме неоднократно.
Для большого количества записей временная таблица намного эффективнее.


Пусть будет так, я не спорю. Дело все равно не в этом.

Поскольку имеются SELECTы из табличных переменных, то, возможно, стоит создать индексы и у них.


Не стоит, потому что селекты из табличных переменных выполняются очень быстро. Тормозит селект из таблиц базы данных. Если убрать два последних селекта (которые извлекают данные из табличной переменной @tbl), а оставить только самый большой селект (можно даже его результаты никуда не запихивать, ни в @, ни в #) - все равно время выполнения практически не изменится.
30 июн 09, 16:02    [7359834]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
Ан нет. Ошибся. Прошу прощенья за самоуверенность. Оставил от запроса и процедуры соответственно:
declare @period datetime
declare @greu varchar(4)

set @period='2009.06.01'
set @greu='2-8'

declare @grp table(
	greu varchar(4),
	kod_text varchar(6)
)

insert into @grp select greu, kod_text from fMakeGRP2()

select ss.storage_id,ss.lshn, ss.fio_s, ss.account_id, ss.flat_id, s.service_id ,s.comment 'Услуга', ss.amount 'Стоимость', pt.comment 'Требуется', ss.provider_id 'id поставщика' 
from hStorageService ss, hService s, hProviderType pt
where period=@period
and left(lshn,6) in (select kod_text from @grp where greu=@greu)
and pt.type_id in (select type_id from hProviderService ps where ps.service_id=s.service_id and period=@period)
and ss.service_id=s.service_id
и
ALTER procedure [dbo].[phViewChargesWithAnyProviderIDForGREU] (@period datetime, @greu varchar(4)) WITH RECOMPILE
as 
begin

declare @grp table(
	greu varchar(4),
	kod_text varchar(6)
)

insert into @grp select greu, kod_text from fMakeGRP2()


select ss.storage_id,ss.lshn, ss.fio_s, ss.account_id, ss.flat_id, s.service_id ,s.comment 'Услуга', ss.amount 'Стоимость', pt.comment 'Требуется', ss.provider_id 'id поставщика' 
from hStorageService ss, hService s, hProviderType pt
where period=@period
and left(lshn,6) in (select kod_text from @grp where greu=@greu)
and pt.type_id in (select type_id from hProviderService ps where ps.service_id=s.service_id and period=@period)
and ss.service_id=s.service_id

end

Теперь план запросов совпадает и они выполняются одинаковое время. Получается, последующая выборка из табличной переменной как-то влияет на оптимизацию запроса, который заполняет эту табличную переменную?
30 июн 09, 16:47    [7360135]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
йцу1
Guest
harrique
step_ks
таблица hProviderType pt с единственным условием на нее pt.type_id in (...) не поджойнена с другими таблицами запроса - это вам так нужно?

Почему же не поджойнена? Поджойнена (select ... , pt.comment 'Требуется'...)


В продолжении...
Действительно связь какая-то невнятная...
Пожет лучше потом проабдейтить pt.comment...
Попробуй прогнать без hProviderType
30 июн 09, 17:02    [7360266]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
Действительно связь какая-то невнятная...

Да уж, с поставщиками и типами поставщиков у нас в жэках действительно каша. Но логически все правильно. Попробую сделать без них.
30 июн 09, 17:13    [7360333]     Ответить | Цитировать Сообщить модератору
 Re: Разное время выполнения запроса и этого же запроса, обернутого в хп  [new]
harrique
Member

Откуда:
Сообщений: 118
Вот ведь как бывает-то. Дело действительно было в типе таблицы tbl. Когда к селекту дописал
insert into @tbl
, то планы запросов стали опять отличаться в запросе и в хп, и хп опять стала работать полчаса. Заменил табличную переменную на временную таблицу, как изначально мимопроходящий советовал и теперь планы запросов стали одинаковые и работают одинаково быстро. Всем спасибо)
30 июн 09, 17:26    [7360395]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить