Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Какой то бред (функции)  [new]
_функция_
Guest
Сегодня обнаружилось что запрос выполняется вместо положеного мгновенно - более 30 секунд. (реиндексация БД делается каждую ночь, так же как и обновление статистики)

База по размеру небольшая 2 гб

версия Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standart Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

есть запрос

select top 5 * from vNewsLiteContext nc
where ContextModuleId = dbo.fnContext_InDBe()
order by NewsDate desc

который выполняется более 30 сек.

если сделать вот так

select top 5 * from vNewsLiteContext nc
where ContextModuleId = 8
order by NewsDate desc

то все летает


планы запросов

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((5)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([n].[NewsId], [Expr1010]) WITH ORDERED PREFETCH)
            |--Nested Loops(Inner Join, WHERE:([DB].[dbo].[News].[NewsRubricId] as [n].[NewsRubricId]=[DB].[dbo].[NewsRubric].[NewsRubricId] as [nr].[NewsRubricId]))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], [Expr1007]))
            |    |    |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeWithMismatchedTypes(NULL,getdate(),(42))))
            |    |    |    |--Constant Scan
            |    |    |--Index Seek(OBJECT:([DB].[dbo].[News].[_dta_index_News_vNewsList_GetTopNews] AS [n]), SEEK:([n].[StatusRecordId]=[DB].[dbo].[fnStatusRecordRecord]() AND [n].[NewsDate] > [Expr1008] AND [n].[NewsDate] < [Expr1009]) ORDERED BACK
            |    |--Filter(WHERE:([DB].[dbo].[NewsRubric].[StatusRecordId] as [nr].[StatusRecordId]=[DB].[dbo].[fnStatusRecordRecord]()))
            |         |--Clustered Index Scan(OBJECT:([DB].[dbo].[NewsRubric].[PK_NewsRubric] AS [nr]))
            |--Clustered Index Seek(OBJECT:([DB].[dbo].[NewsContext].[PK_NewsContext] AS [nc]), SEEK:([nc].[NewsId]=[DB].[dbo].[News].[NewsId] as [n].[NewsId] AND [nc].[ContextModuleId]=[DB].[dbo].[fnContext_InDBe]()) ORDERED FORWARD)

(строк обработано: 10)

StmtText
--------------------------------------------------------------------------------------------
  UDF: [DB].[dbo].[fnStatusRecordRecord]
    CREATE FUNCTION [dbo].[fnStatusRecordRecord]()
	RETURNS tinyint
AS
BEGIN
	RETURN 1
  UDF: [DB].[dbo].[fnStatusRecordRecord]
    CREATE FUNCTION [dbo].[fnStatusRecordRecord]()
	RETURNS tinyint
AS
BEGIN
	RETURN 1
  UDF: [DB].[dbo].[fnContext_InDBe]
    create FUNCTION [dbo].[fnContext_InDBe] ()
	RETURNS smallint
AS
BEGIN
	RETURN 8


и для того что без функции

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((5)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([n].[NewsRubricId]))
            |--Sort(ORDER BY:([n].[NewsDate] DESC))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([nc].[NewsId]))
            |         |--Index Seek(OBJECT:([DB].[dbo].[NewsContext].[_dta_index_NewsContext_9_265768004__K2_K1_4364] AS [nc]), SEEK:([nc].[ContextModuleId]=(8)) ORDERED FORWARD)
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], [Expr1007]))
            |              |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeWithMismatchedTypes(NULL,getdate(),(42))))
            |              |    |--Constant Scan
            |              |--Index Seek(OBJECT:([DB].[dbo].[News].[_dta_index_News_vNewsLiteImageContext] AS [n]), SEEK:([n].[NewsId]=[DB].[dbo].[NewsContext].[NewsId] as [nc].[NewsId] AND [n].[StatusRecordId]=[DB].[dbo].[fnStatusRecordRecord]() 
            |--Filter(WHERE:([DB].[dbo].[NewsRubric].[StatusRecordId] as [nr].[StatusRecordId]=[DB].[dbo].[fnStatusRecordRecord]()))
                 |--Clustered Index Seek(OBJECT:([DB].[dbo].[NewsRubric].[PK_NewsRubric] AS [nr]), SEEK:([nr].[NewsRubricId]=[DB].[dbo].[News].[NewsRubricId] as [n].[NewsRubricId]) ORDERED FORWARD)

(строк обработано: 11)

StmtText
--------------------------------------------------------------------------------------------
  UDF: [DB].[dbo].[fnStatusRecordRecord]
    CREATE FUNCTION [dbo].[fnStatusRecordRecord]()
	RETURNS tinyint
AS
BEGIN
	RETURN 1
  UDF: [DB].[dbo].[fnStatusRecordRecord]
    CREATE FUNCTION [dbo].[fnStatusRecordRecord]()
	RETURNS tinyint
AS
BEGIN
	RETURN 1

почему такая разница в плане?
3 июл 11, 11:11    [10913429]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
проходящий.
Guest
_функция_
почему такая разница в плане?
Выполнения функции для каждой записи. К тому же, скалярные функции славятся торомознутостью. Сам подход с использование скалярной функции в where порочный.
3 июл 11, 12:33    [10913530]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Функцию надо сделать детерминированной, добавив with schemabinding. Ну или через переменную.
3 июл 11, 12:39    [10913543]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
_функция_,

или так:

declare @id int
select @id = dbo.fnContext_InDBe()

select top 5 * from vNewsLiteContext nc
where ContextModuleId = @id
order by NewsDate desc
3 июл 11, 14:20    [10913674]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
_функция_
Guest
Спасибо буду знать.

Сейчас пересмотрю функции, некоторые используются во view и там с переменной уже никак не выйдет.

Функции использую как константы
3 июл 11, 15:38    [10913804]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Makar4ik
declare @id int
select @id = dbo.fnContext_InDBe()
...
Много букв.

declare @id int = dbo.fnContext_InDBe()
...


Сообщение было отредактировано: 3 июл 11, 16:40
3 июл 11, 16:40    [10913891]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
aleks2
Guest
Гавриленко Сергей Алексеевич
Много букв.

Еще бы.

select top 5 * from vNewsLiteContext nc INNER JOIN (select dbo.fnContext_InDBe() as X) Y ON
nc.ContextModuleId = Y.X
order by NewsDate desc
3 июл 11, 17:23    [10914003]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич
declare @id int = dbo.fnContext_InDBe()

Мало букв. MSSQL2000/2005, к примеру это не сожрет.
Я понимаю, что вопрос про 2008, но инстинктивно пытаюсь написать более переносимый код
3 июл 11, 18:20    [10914102]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
aleks2
select top 5 * from vNewsLiteContext nc INNER JOIN (select dbo.fnContext_InDBe() as X) Y ON
nc.ContextModuleId = Y.X
order by NewsDate desc
Точно... Вероятно, это - оптимальнее всего.
3 июл 11, 18:26    [10914107]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Makar4ik
Точно... Вероятно, это - оптимальнее всего.
Where в join'ы переносить?... Фи.

Makar4ik
Мало букв. MSSQL2000/2005, к примеру это не сожрет.
Я понимаю, что вопрос про 2008, но инстинктивно пытаюсь написать более переносимый код
А на семерку вы уже забили? А на 6.5? А там еще когда-то 4 с чем-то было.

Сообщение было отредактировано: 3 июл 11, 18:45
3 июл 11, 18:45    [10914149]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
aleks2
Guest
Гавриленко Сергей Алексеевич
Makar4ik
Точно... Вероятно, это - оптимальнее всего.
Where в join'ы переносить?... Фи.


Ну идейные могут попробовать
select top 5 * from vNewsLiteContext nc 
WHERE
nc.ContextModuleId = (select dbo.fnContext_InDBe())
order by NewsDate desc 
... на 2000-м это канало.
3 июл 11, 18:54    [10914169]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич
А на семерку вы уже забили? А на 6.5? А там еще когда-то 4 с чем-то было.
К счастью уже забил.
К несчастью, в конторе живет еще несколько 2000-х, и портировать базы под 2005-2008 нету возможностей.
Хорошо тому живется, кто теоретик...
А на практике - не так всё сладко.
3 июл 11, 19:06    [10914193]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
aleks2
select top 5 * from vNewsLiteContext nc 
WHERE
nc.ContextModuleId = (select dbo.fnContext_InDBe())
order by NewsDate desc 
... на 2000-м это канало.
и до сих пор канает. )))
и план покажет одну строку.
3 июл 11, 19:13    [10914210]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
_функция_
Guest
Немного потестировал и если использовать with schemabinding то запросы проходят куда проще, хотя в целом понял - лучше использовать значение (но при большом наплыве куда удобней ведь использовать названию функий)

есть ли какая альтернатива? пусть даже только под 2008 сервером?

+ по ходу использование where statusrecordid =(select dbo.fnStatusRecord_Moderator()) вроде будет происходить дольше
where statusrecordid = dbo.fnStatusRecord_Moderator() ?

StmtText
--------------------------------------------------------
select top 100 * from news
where statusrecordid = 2

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((100)))
       |--Clustered Index Scan(OBJECT:([DB1].[dbo].[News].[PK_News]), WHERE:([DB1].[dbo].[News].[StatusRecordId]=(2)))

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------

select top 100 * from news
where statusrecordid = dbo.fnStatusRecord_Moderator()

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((100)))
       |--Clustered Index Scan(OBJECT:([DB1].[dbo].[News].[PK_News]), WHERE:([DB1].[dbo].[News].[StatusRecordId]=[DB1].[dbo].[fnStatusRecord_Moderator]()))

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------
  UDF: [DB1].[dbo].[fnStatusRecord_Moderator]
    
CREATE FUNCTION [dbo].[fnStatusRecord_Moderator]()
	RETURNS tinyint

	with schemabinding 
AS
BEGIN
	return 2

select top 100 * from news
where statusrecordid =(select dbo.fnStatusRecord_Moderator())

(3 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((100)))
       |--Filter(WHERE:([DB1].[dbo].[News].[StatusRecordId]=[DB1].[dbo].[fnStatusRecord_Moderator]()))
            |--Clustered Index Scan(OBJECT:([DB1].[dbo].[News].[PK_News]))

(3 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------
  UDF: [DB1].[dbo].[fnStatusRecord_Moderator]
    
CREATE FUNCTION [dbo].[fnStatusRecord_Moderator]()
	RETURNS tinyint

	with schemabinding 
AS
BEGIN
	return 2

(2 row(s) affected)

3 июл 11, 20:08    [10914383]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
_функция_
есть ли какая альтернатива? пусть даже только под 2008 сервером?

Дык!
aleks2 всё рассказал.
select top 5 * from vNewsLiteContext nc 
WHERE
nc.ContextModuleId = (select dbo.fnContext_InDBe())
order by NewsDate desc 
3 июл 11, 20:15    [10914418]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
_функция_
есть ли какая альтернатива? пусть даже только под 2008 сервером?
Но альтернативный вариант алекса
select top 5 * from vNewsLiteContext nc INNER JOIN (select dbo.fnContext_InDBe() as X) Y ON
nc.ContextModuleId = Y.X
order by NewsDate desc
всё-же более идейно правилен. И чисто теоретически, подскажет больше хинтов планировщику в плане создания более оптимального плана выполнения за меньшее время.
3 июл 11, 20:20    [10914436]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
_функция_
есть ли какая альтернатива? пусть даже только под 2008 сервером?
Есть.
Сделайте Inline Table-Valued Function, возвращающую одну запись из одного поля.
Она же будет делать то же самое, что и теперешняя скалярная.
Зато её текст оптимизатор встраивает в запрос перед началом анализа планов выполнения.

http://msdn.microsoft.com/ru-ru/library/ms186755(v=SQL.100).aspx
3 июл 11, 21:34    [10914688]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
iap
_функция_
есть ли какая альтернатива? пусть даже только под 2008 сервером?
Есть.
Сделайте Inline Table-Valued Function, возвращающую одну запись из одного поля.
Она же будет делать то же самое, что и теперешняя скалярная.
Зато её текст оптимизатор встраивает в запрос перед началом анализа планов выполнения.

http://msdn.microsoft.com/ru-ru/library/ms186755(v=SQL.100).aspx

и еще есть...
НЕ оформлять константы в виде скалярных функций, а зашить их в таблицу Констант!!!
3 июл 11, 21:36    [10914696]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Судя по результату вот этого запроса
SELECT * FROM master.dbo.spt_values WHERE type='O9T' AND name LIKE 'IS:%';
ещё можно надеяться, что появятся-таки скалярные inline-функции
3 июл 11, 21:47    [10914746]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
iap
Судя по результату вот этого запроса
SELECT * FROM master.dbo.spt_values WHERE type='O9T' AND name LIKE 'IS:%';
ещё можно надеяться, что появятся-таки скалярные inline-функции
Правда, смутно представляю, как они могут быть реализованы...
3 июл 11, 21:49    [10914753]     Ответить | Цитировать Сообщить модератору
 Re: Какой то бред (функции)  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
iap
Правда, смутно представляю, как они могут быть реализованы...
Как и все инлайны.
Тупой подлинковкой в код запроса.
И будет внутри них куча ограничений по синтаксису :)
3 июл 11, 22:10    [10914847]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить