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

Откуда: Волгоград
Сообщений: 15
Добрый день.
При запуске следующего запроса

EXEC sp_executesql 
N'SELECT column1, column2, column3 FROM base1.dbo.udf_query1(@date)'
, N'@date datetime'
, @date = '2009-06-04'

первый раз он отрабатывает 3 минуты, последующие обработки в рамках текущего соединения происходят в течении 1 секунды, при очередном запуске в рамках другого подключения снова обработка в 3 минуты и так каждый раз при изменении подключения

Подскажите в чем проблема?

Текущая версия:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
4 июн 09, 08:57    [7263402]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Да никакой проблеммы и нет - первый раз строится план и кешируется, в последующие разы план берется из кеша.
4 июн 09, 09:07    [7263422]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Mnemonik
Member

Откуда: Волгоград
Сообщений: 15
Вопрос как раз в том, что при изменении подключения (т.е. изменился пользователь или комп с которого коннектимся) план снова строится и почему снова строится для предыдущего подключения?

Есть два пользователя, когда первый запускает, план строится (3 минуты) и потом отрабатывает быстро, но как только второй запускает такой же запрос первый уходит в ожидание и второй ждет 3 минуты, после завершения у первого снова строится (3 минуты)

Разве планы в кэше не хранятся для каждой сессиии?

Посоветуйте как это обойти?
4 июн 09, 09:23    [7263451]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
1) а что там внутри udf_query1 ?
2) каков(ы) сейчас план(ы) выполнения ?
3) какие таблицы участвуют в запросе(ах), какова их структура, какие на них есть индексы ?
4 июн 09, 09:45    [7263521]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Mnemonik
Member

Откуда: Волгоград
Сообщений: 15
В приложении план выполнения, и сама udf:

USE [RefDataView]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  FUNCTION [dbo].[udf_ProductsOnDateGet]
(@Date DATETIME)

RETURNS @Products TABLE
(Product		INT,
 FullName		VARCHAR (255),
 ShortName		VARCHAR (255),
 Standard		VARCHAR (255),
 OKP			VARCHAR (255),
 ParentProduct	INT,
 Package		INT,
 Density		DECIMAL (18, 6)
)
AS BEGIN

	INSERT INTO @Products
	SELECT	O.IdRec AS Product,
			ISNULL (FullName.ValueText, '') AS FullName,
			ISNULL (ShortName.ValueText, '') AS ShortName,
			ISNULL (Standard.ValueText, '') AS Standard,
			ISNULL (OKP.ValueText, '') AS OKP,
			Parent.ValueInt AS ParentProduct,
			Package.ValueInt AS Package,
			ISNULL (Density.ValueFloat, 0) AS Density
	FROM RefData.DBO.Objects O
	LEFT JOIN RefData.DBO.PropValues FullName 
		ON O.IdRec = FullName.Object AND FullName.[Property] = 1 AND @Date BETWEEN FullName.DateStart AND FullName.DateEnd
	LEFT JOIN RefData.DBO.PropValues ShortName 
		ON O.IdRec = ShortName.Object AND ShortName.[Property] = 6 AND @Date BETWEEN ShortName.DateStart AND ShortName.DateEnd
	LEFT JOIN RefData.DBO.PropValues Standard 
		ON O.IdRec = Standard.Object AND Standard.[Property] = 3 AND @Date BETWEEN Standard.DateStart AND Standard.DateEnd
	LEFT JOIN RefData.DBO.PropValues OKP
		ON O.IdRec = OKP.Object AND OKP.[Property] = 7 AND @Date BETWEEN OKP.DateStart AND OKP.DateEnd
	LEFT JOIN RefData.DBO.PropValues Parent
		ON O.IdRec = Parent.Object AND Parent.[Property] = 19 AND @Date BETWEEN Parent.DateStart AND Parent.DateEnd
	LEFT JOIN RefData.DBO.PropValues Package
		ON O.IdRec = Package.Object AND Package.[Property] = 20 AND @Date BETWEEN Package.DateStart AND Package.DateEnd
	LEFT JOIN RefData.DBO.PropValues Density
		ON O.IdRec = Density.Object AND Density.[Property] = 5 AND @Date BETWEEN Density.DateStart AND Density.DateEnd
	WHERE @Date BETWEEN O.DateStart AND O.DateEnd
	AND O.ObjectType = 1

	RETURN	
END


К сообщению приложен файл (123.rar - 6Kb) cкачать
4 июн 09, 10:03    [7263589]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Интересно, зачем такой изврат с функцией?! И, если уж функция, то почему не inline?!
4 июн 09, 10:15    [7263644]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
про даты не совсем понял

допустим, есть какая-то запись в таблице PropValues и [Property] у нее равно 1
всегда ли будет не более одной записи в этой же таблице
с теми же значениями Object, DateStart и DateEnd
но чтобы [Property] у них было равно 6
?

Другими словами, не может ли быть такого, что для одного FullName
найдется два ShortName ?

и тот же вопрос, но значения DateStart и DateEnd у записей "FullName" и "ShortName" не одинаковые,
но тем не менее у обеих записей они отвечают условию @Date BETWEEN DateStart AND DateEnd

это я к чему... я пока что не понял, почему была выбрана именно модель EAV...если я правильно догадался...
4 июн 09, 10:21    [7263669]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Crimean
Member

Откуда:
Сообщений: 13148
(1) сделайте функцию инлайновой
(2) запрос склейте заранее без параметра и выполняйте с константой внутри
взлетит
4 июн 09, 10:25    [7263692]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Паганель,

В общем все правильно.
А почему была выбрана, это вопрос не к этой ветке.
4 июн 09, 10:26    [7263695]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Crimean,

Код работает уже не первый год на 2000 сервере и переписывать его нет возможности...
4 июн 09, 10:26    [7263698]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
stopw
А почему была выбрана, это вопрос не к этой ветке.
Откуда Вы знаете?
У Вас с автором телепатическая связь ?
4 июн 09, 10:29    [7263707]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Паганель,

В соседних кабинетах сидим...
Так что в полне себе телефонная.
4 июн 09, 10:30    [7263714]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
stopw
Код работает уже не первый год на 2000 сервере и переписывать его нет возможности...
тогда наверное придется менять что-то в настройках сервера (или в железе)
а это уже не по моей части, я в этом не спец
к сожалению, ничем больше помочь не могу
4 июн 09, 10:34    [7263733]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Паганель
stopw
Код работает уже не первый год на 2000 сервере и переписывать его нет возможности...
тогда наверное придется менять что-то в настройках сервера (или в железе)
а это уже не по моей части, я в этом не спец
к сожалению, ничем больше помочь не могу

Железо, вроде, нормальное. Хотя это тоже не по моей части.
А вот совет с настройками сервера был бы интересен.
4 июн 09, 10:37    [7263751]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
hoarfrost
Member

Откуда: Волгоград
Сообщений: 438
А при чём тут железо?

Есть два пользователя, когда первый запускает, план строится (3 минуты) и потом отрабатывает быстро, но как только второй запускает такой же запрос первый уходит в ожидание и второй ждет 3 минуты, после завершения у первого снова строится (3 минуты)
4 июн 09, 10:43    [7263784]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Сейчас провели эксперимент. Вот эта хранимка всегда на 2000 сервере выполняется <1 секунды, а на 2008 около 3х минут...
CREATE PROCEDURE usp_product_test @date DATETIME 
AS

SELECT Product,  Fullname, Shortname, Density FROM RefDataView.dbo.udf_ProductsOnDateGet(@date)
4 июн 09, 10:44    [7263790]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
я кстати еще 1 момент не понял, может кто-то объяснит буду благодарен
(мне для себя нужно, в целях повышения квалификации)

откуда автор мог узнать, что 3 минуты уходит именно на построение плана ?
4 июн 09, 10:50    [7263818]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Crimean
Member

Откуда:
Сообщений: 13148
stopw
Crimean,

Код работает уже не первый год на 2000 сервере и переписывать его нет возможности...


тогда надо смотреть, что происходит. вообще-то функцию сделать инлайновой ничего сложного:

USE [RefDataView]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  FUNCTION [dbo].[udf_ProductsOnDateGet]
(@Date DATETIME)

RETURNS	TABLE
AS
RETURN
(
	SELECT	O.IdRec AS Product,
			ISNULL (FullName.ValueText, '') AS FullName,
			ISNULL (ShortName.ValueText, '') AS ShortName,
			ISNULL (Standard.ValueText, '') AS Standard,
			ISNULL (OKP.ValueText, '') AS OKP,
			Parent.ValueInt AS ParentProduct,
			Package.ValueInt AS Package,
			ISNULL (Density.ValueFloat, 0) AS Density
	FROM RefData.DBO.Objects O
	LEFT JOIN RefData.DBO.PropValues FullName 
		ON O.IdRec = FullName.Object AND FullName.[Property] = 1 AND @Date BETWEEN FullName.DateStart AND FullName.DateEnd
	LEFT JOIN RefData.DBO.PropValues ShortName 
		ON O.IdRec = ShortName.Object AND ShortName.[Property] = 6 AND @Date BETWEEN ShortName.DateStart AND ShortName.DateEnd
	LEFT JOIN RefData.DBO.PropValues Standard 
		ON O.IdRec = Standard.Object AND Standard.[Property] = 3 AND @Date BETWEEN Standard.DateStart AND Standard.DateEnd
	LEFT JOIN RefData.DBO.PropValues OKP
		ON O.IdRec = OKP.Object AND OKP.[Property] = 7 AND @Date BETWEEN OKP.DateStart AND OKP.DateEnd
	LEFT JOIN RefData.DBO.PropValues Parent
		ON O.IdRec = Parent.Object AND Parent.[Property] = 19 AND @Date BETWEEN Parent.DateStart AND Parent.DateEnd
	LEFT JOIN RefData.DBO.PropValues Package
		ON O.IdRec = Package.Object AND Package.[Property] = 20 AND @Date BETWEEN Package.DateStart AND Package.DateEnd
	LEFT JOIN RefData.DBO.PropValues Density
		ON O.IdRec = Density.Object AND Density.[Property] = 5 AND @Date BETWEEN Density.DateStart AND Density.DateEnd
	WHERE @Date BETWEEN O.DateStart AND O.DateEnd
	AND O.ObjectType = 1
)

и запрос сделать из параметризованного статичным (что оч положительно влияет всегда) тоже предельно несложно:

DECLARE	@Date	datetime
SET	@Date	= convert( datetime, '2009-06-04', 120 )

DECLARE	@Cmd	nvarchar(1000)
SET	@Cmd	= N'SELECT column1, column2, column3 FROM base1.dbo.udf_query1( ''' +convert( varchar(20), @Date, 112 )+ ''' )'

PRINT	@Cmd
-- EXEC sp_executesql @Cmd

в результате не будет грузиться tempdb для переливки данных (вообще смысл этого теряется, разве что оптимизатор "обмануть"? но индексов-то нет?..) + будет каждый раз эффективный план (при наличии индексов / статистик)
4 июн 09, 10:52    [7263831]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Crimean,

Если сделать функцию инлайновой, то все сломается. Она слишком в многих местах просто join'ится к табличкам.
4 июн 09, 10:57    [7263858]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Crimean
Member

Откуда:
Сообщений: 13148
stopw
Crimean,

Если сделать функцию инлайновой, то все сломается. Она слишком в многих местах просто join'ится к табличкам.


а чо тада индексов на таблицу результата не сделали? насчет не-инлайновых табличных UDF я в курсе - ими удобно оптимизатор "обманывать", но я тада обычно индексы делаю для таблиц результатов :) но нагрузка на tempdb получается :(
4 июн 09, 11:04    [7263894]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Crimean


а чо тада индексов на таблицу результата не сделали? насчет не-инлайновых табличных UDF я в курсе - ими удобно оптимизатор "обманывать", но я тада обычно индексы делаю для таблиц результатов :) но нагрузка на tempdb получается :(


Сейчас-то проблема не в индексах. :(
4 июн 09, 11:06    [7263920]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Crimean
Member

Откуда:
Сообщений: 13148
stopw
Crimean


а чо тада индексов на таблицу результата не сделали? насчет не-инлайновых табличных UDF я в курсе - ими удобно оптимизатор "обманывать", но я тада обычно индексы делаю для таблиц результатов :) но нагрузка на tempdb получается :(


Сейчас-то проблема не в индексах. :(


ну так смотреть надо, однако
4 июн 09, 11:22    [7264039]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Попробовали на 2005 сервере работает все нормально.
Не покидает чувство, что с сервером что-то не так...
4 июн 09, 11:43    [7264210]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
на мой вопрос про 3 минуты ответьте пожалуйста
4 июн 09, 11:45    [7264227]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите с выполнением sp_executesql  [new]
stopw
Member

Откуда:
Сообщений: 9
Паганель,
На что уходит три минуты я не понимаю. Автор предположил, что на построение плана запроса.
4 июн 09, 11:47    [7264233]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить