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

Откуда:
Сообщений: 2
Здравствуйте, совсем недавно начал изучать хранимки. Так вот, есть хранимка для фильтрации данных, которая работает отлично. Скопировал весь код с нее в другую хранимку, вот при выполнении ее та же операция занимает 20-25 секунд. База одна, обе хранимки мои, просто не могу понять куда копать.....

+

USE [Domchel]
GO
/****** Object:  StoredProcedure [dbo].[FilterNewBuilding]    Script Date: 05/03/2013 23:31:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[FilterNewBuilding](@location nvarchar(200),@region nvarchar(200),@lendmark nvarchar(200),
							  @squaremin real,@squaremax real, @pricefullmin real,@pricefullmax real,@Series nvarchar(200),
							  @countofrooms nvarchar(100),@storeymin int, @storeymax int,@numberofstoreymin int, @numberofstoreymax int, @typeOfHouse nvarchar(100)) 
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
declare @rr nvarchar(2)
	set @rr='/'
	
	if(@squaremin=0) 
		begin 
			set @squaremin=null  
			
		end
	if(@squaremax=0) set @squaremax=null
	if (@pricefullmin=0) set @pricefullmin=null
	if (@pricefullmax=0) set @pricefullmax=null
	if (@storeymin=0) set @storeymin=null
	if (@storeymax=0) set @storeymax=null
	if (@numberofstoreymin=0) set @numberofstoreymin=null
	if (@numberofstoreymax=0) set @numberofstoreymax=null
	if (@location='') set @location=null
	if (@region='') set @region=null
	if (@lendmark='') set @lendmark=null
	if (@Series='') set @Series=null
	if (@countofrooms='') set @countofrooms=null
	if (@typeOfHouse='') set @typeOfHouse=null
	
	
	
	
	if ((@squaremax is not null) or (@squaremin is not null) 
		or (@pricefullmax is not null) or (@pricefullmin is not null)
		or (@storeymax is not null)  or (@storeymin is not null) 
		or (@numberofstoreymax is not null) or (@numberofstoreymin is not null)
		or (@location is not null) or (@lendmark is not null) 
		or (@Series is not null) or (@countofrooms is not null) or (@typeOfHouse is not null)
		or (@region is not null))
	begin
	select NB.ID,NB.Location,PNB.PriceFull,NB.[Square],
                                        NB.CountOfRooms, (CONVERT(varchar(12),NB.Storey) +@rr+CONVERT(varchar(12),NB.NumberOfStorey))as Storeys from [dbo].NewBuilding NB
                                        LEFT JOIN [dbo].Price_NewBuild PNB on
		                                NB.ID=PNB.ID_newBuilding
                                        where 
                                        (PNB.ID in (select MAX(PNB.ID) as Price from [dbo].Price_NewBuild PNB 
				                        LEFT JOIN [dbo].NewBuilding NB on NB.ID=PNB.ID_newBuilding Group BY NB.ID))
				                        AND (@location is null or nb.Location like (@location+'%')) 
				                        AND (@lendmark is null or NB.Lendmark=@lendmark)
				                        AND (@region is null or nb.Region=@region)
				                        AND (@Series is null or NB.Series=@Series)
				                        AND (@countofrooms is null or NB.CountOfRooms=@countofrooms)
				                        AND (@squaremin is null or NB.[Square]>@squaremin)  
				                        AND (@squaremax is null or NB.[Square]<@squaremax)
				                        AND (@storeymin is null or NB.Storey>@storeymin)
				                        AND (@storeymax is null or NB.Storey<@storeymax)
				                        AND (@numberofstoreymin is null or NB.NumberOfStorey>@numberofstoreymin)
				                        AND (@numberofstoreymax is null or NB.NumberOfStorey<@numberofstoreymax)
				                        AND (@pricefullmin is null or PNB.PriceFull>@pricefullmin) 
				                        AND (@pricefullmax is null or PNB.PriceFull<@pricefullmax)
				                        AND (@typeOfHouse is null or NB.TypeOfHouse=@typeOfHouse)  
    end
    else
    begin
    select NB.ID,NB.Location,PNB.PriceFull,NB.[Square],
                                        NB.CountOfRooms, (CONVERT(varchar(12),NB.Storey) +@rr+CONVERT(varchar(12),NB.NumberOfStorey))as Storeys from NewBuilding NB
                                        LEFT JOIN Price_NewBuild PNB on
		                                NB.ID=PNB.ID_newBuilding
                                        where 
                                        (PNB.ID in (select MAX(PNB.ID) as Price from Price_NewBuild PNB 
				                        LEFT JOIN NewBuilding NB on NB.ID=PNB.ID_newBuilding Group BY NB.ID))
    end  
  
END



Записей пока не много около 15000. При чем если выполнять просто запросом, то тоже все отлично... менее секунды...
4 май 13, 14:49    [14257819]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения хранимаой процедуры  [new]
Exproment
Member

Откуда:
Сообщений: 416
SKG, если в итоге обе хранимки абсолютно идентичны и одна из них при тех же входных параметрах выполняется дольше другой, то однозначно виновата неверная оценка кардинальности в случая.

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

Или возможно вы старую выполняли на закешированных страницах, а новую без кеша. Для начало было бы неплохо привечти примеры планов, а потом уже копать.
4 май 13, 19:22    [14258344]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения хранимаой процедуры  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
SKG,

Ваша проблема скорее всего связана с кэшированнием плана выполнения запроса. Можете проверить это выполнив процедуру sp_recompile для обеих ваших процедур, затем выполните сами процедуры с одинаковыми параметрами, между вызовами выполните DBCC DROPCLEANBUFFERS для чистоты эксперимента. Время выполнения процедур будет одинаковым (хотя может и отличаться в некоторых случаях). Все эти эксперименты нужно проводить на тестовом сервере.

exec sp_recompile N'proc1'
exec sp_recompile N'proc2'
GO
DBCC DROPCLEANBUFFERS
GO
exec proc1
GO
DBCC DROPCLEANBUFFERS
GO
exec proc2

Тоже самое можно проверить, посмотрев актуальные планы запросов.

В вашей процедуре вы допустили одну из самых распространенных ошибок: конструкцию вида (@region is null or nb.Region=@region) при динамических условиях фильтрации. Здесь подробно описаны варианты решения данной проблемы.

PS приведите актуальные планы запросов и результат SELECT @@version

Успехов!
4 май 13, 19:55    [14258409]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения хранимаой процедуры  [new]
SKG
Member

Откуда:
Сообщений: 2
Спасибо, но проблема была почему-то в условиях:
if(@squaremax=0) set @squaremax=null
	if (@pricefullmin=0) set @pricefullmin=null
	if (@pricefullmax=0) set @pricefullmax=null
	if (@storeymin=0) set @storeymin=null
	if (@storeymax=0) set @storeymax=null
	if (@numberofstoreymin=0) set @numberofstoreymin=null
	if (@numberofstoreymax=0) set @numberofstoreymax=null
	if (@location='') set @location=null
	if (@region='') set @region=null
	if (@lendmark='') set @lendmark=null
	if (@Series='') set @Series=null
	if (@countofrooms='') set @countofrooms=null
	if (@typeOfHouse='') set @typeOfHouse=null


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

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

P.S. Переделал процедуру по совету Ruuu. Правда не совсем понял почему в первом варианте не верно? Ведь работает же.... Можете объяснить в двух словах.
+

USE [Domchel]
GO
/****** Object:  StoredProcedure [dbo].[FilterNewBuilding1]    Script Date: 05/04/2013 22:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
alter PROCEDURE [dbo].[FilterNewBuilding2] 
	-- Add the parameters for the stored procedure here

 @location nvarchar(200)=null,
 @region nvarchar(200)=null,
 @lendmark nvarchar(200)=null,
 @squaremin real=null,
 @squaremax real=null,
 @pricefullmin real=null,
 @pricefullmax real=null,
 @Series nvarchar(200)=null,
 @countofrooms nvarchar(100)=null,
 @storeymin int=null,
 @storeymax int=null,
 @numberofstoreymin int=null,
 @numberofstoreymax int=null,
 @typeOfHouse nvarchar(100)=null,
 @debug bit =0 as

declare @query nvarchar(MAX),	
	    @rr nvarchar(2),
	    @paramlist  nvarchar(4000) 
	
	set @rr='/'
	
		
	select @query='select NB.ID,NB.Location,PNB.PriceFull,NB.[Square],NB.CountOfRooms, 
				  (CONVERT(varchar(12),NB.Storey)+''/''+CONVERT(varchar(12),NB.NumberOfStorey))as Storeys 
				   from [dbo].NewBuilding NB
                   LEFT JOIN [dbo].Price_NewBuild PNB on
		                      NB.ID=PNB.ID_newBuilding
                   WHERE (PNB.ID in (select MAX(PNB.ID) as Price from [dbo].Price_NewBuild PNB
                            LEFT JOIN [dbo].NewBuilding NB on NB.ID=PNB.ID_newBuilding Group BY NB.ID))'
				                        
	if (@location is not null)	select @query=@query+'AND (nb.Location like (@xlocation+''%'')' 
    if (@lendmark is not null) 	select @query=@query+'AND (NB.Lendmark=@xlendmark)'
    if (@region is not null)	select @query=@query+'AND (nb.Region=@xregion)'
    if (@Series is not null)    select @query=@query+'AND (NB.Series=@xSeries)'
    if (@countofrooms is not null) select @query=@query+'AND (NB.CountOfRooms=@xcountofrooms)'
    if (@squaremin is not null)	select @query=@query+'AND (NB.[Square]>@xsquaremin)'
    if (@squaremax is not null) select @query=@query+'AND (NB.[Square]<@xsquaremax)'
    if (@storeymin is not null) select @query=@query+'AND (NB.Storey>@xstoreymin)'
    if (@storeymax is not null) select @query=@query+'AND (NB.Storey<@xstoreymax)'
    if (@numberofstoreymin is not null) select @query=@query+'AND (NB.NumberOfStorey>@xnumberofstoreymin)'
	if (@numberofstoreymax is not null) select @query=@query+'AND (NB.NumberOfStorey<@xnumberofstoreymax)'
	if (@pricefullmin is not null) select @query=@query+'AND (PNB.PriceFull>@xpricefullmin)' 
	if (@pricefullmax is not null) select @query=@query+'AND (PNB.PriceFull<@xpricefullmax)'
	if (@typeOfHouse is not null) select @query=@query+'AND (NB.TypeOfHouse=@xtypeOfHouse)'
	
	if @debug=1
	print @query
	
	select @paramlist='@xlocation nvarchar(200), 
					   @xregion nvarchar(200),
					   @xlendmark nvarchar(200),
					   @xsquaremin real,
					   @xsquaremax real,
                       @xpricefullmin money,
                       @xpricefullmax money,
				       @xSeries nvarchar(200),
					   @xcountofrooms nvarchar(100),
	                   @xstoreymin int,
                       @xstoreymax int,
                       @xnumberofstoreymin int,
                       @xnumberofstoreymax int,
                       @xtypeOfHouse nvarchar(100)'
	
	exec sp_executesql @query,@paramlist,@location, @region, @lendmark, @squaremin,
					   @squaremax, @pricefullmin, @pricefullmax, @Series,
					   @countofrooms, @storeymin, @storeymax, @numberofstoreymin,
					   @numberofstoreymax, @typeOfHouse 
    
    
  

4 май 13, 22:31    [14258730]     Ответить | Цитировать Сообщить модератору
 Re: Время выполнения хранимаой процедуры  [new]
Exproment
Member

Откуда:
Сообщений: 416
SKG
Правда не совсем понял почему в первом варианте не верно? Ведь работает же.... Можете объяснить в двух словах.

1. Первый вариант так-же верный, просто он в разы хуже динамики
2. Вам нет смысла ничего объяснять, т.к. вы все еще не видели планы выполнения этих процедур, в то время как вас уже дважды просили их предоставить.

Возьмите один и тот-же набор входных параметров. Посмотрите актуальные планы новой и старой версии процедур и сразу все поймете.
5 май 13, 12:52    [14259540]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить