Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Execution Plan  [new]
Гость888
Guest
С вас примерно $7 - отнесите в ближайший детский дом пожалуйста.

Mind
Решение получено.
Решение фактически сводится к созданию новой таблицы (indexed view), а по ней опять же кластерный скан, что и требовалось доказать, так что я не вижу как это расходится с моим утверждением о том что скан эффективней в данном случае, так что никакого "наоборот" тут не вижу, как и примера.


CREATE TABLE AAA ( 
     aKey int identity( 10000000,1 ) PRIMARY KEY, 
     fname VARCHAR(50)
)

INSERT into AAA (fname) values ('sss') 
GO 10000

INSERT into AAA (fname) values ('bbb') 
GO 90000

Create index CCC on AAA (fname)

select * from AAA where fname = 'sss'

Index Seek.
8 янв 10, 09:02    [8156480]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
aleks2
Гость888
aleks2

...


По-моему вы молдованин. Смотрите объяснения этому феномену выше.


Хе-хе... как ты думаешь, что лучше: быть молдаванином или идиотом с высоким рейтингом?


Вам выбор - вам и выбирать.

PS Для вашего образования - рейт от рейтинга отличаейтся примерно так же как Петя от пейтинга. А теперь пшел вон, дурак.
8 янв 10, 09:04    [8156484]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Очепятался ибо сам не нуждаюсь: петтинга конечно
8 янв 10, 09:09    [8156488]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Какая фееричная тема
8 янв 10, 09:12    [8156489]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Если по теме - тоже интересует вопрос.
У меня вот есть запрос, который в процедуре формирует неоптимальный план (на мой взгляд), тогда как если запускать его как запрос - все чудесно. Самое интересное запрос в процедуре один единственный.
3 дня страдала, в итоге использую хинт (на форум выкладывать лень заела :) ).
Теперь думаю может тут спросить, столько знающих людей собралось!
8 янв 10, 09:24    [8156496]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
С вас примерно $7 - отнесите в ближайший детский дом пожалуйста.

Считайте что это было волонтерство во имя истины )


Гость888

CREATE TABLE AAA ( 
     aKey int identity( 10000000,1 ) PRIMARY KEY, 
     fname VARCHAR(50)
)

INSERT into AAA (fname) values ('sss') 
GO 10000

INSERT into AAA (fname) values ('bbb') 
GO 90000

Create index CCC on AAA (fname)

select * from AAA where fname = 'sss'

Index Seek.


Добавьте хотя бы одно значимое поле, а иначе aKey и fname содержатся в индексе CCC:

ALTER TABLE dbo.AAA ADD
	fname2 varchar(50) NULL

и получим Clustered Index Scan
8 янв 10, 09:34    [8156501]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Mind
и получим Clustered Index Scan


а там кластерного индекса вроде нет
8 янв 10, 09:49    [8156505]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Кудряшка
Mind
и получим Clustered Index Scan


а там кластерного индекса вроде нет


а стоп! PRIMARY KEY обозначен! :) сорри
8 янв 10, 09:50    [8156508]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
Гость888
С вас примерно $7 - отнесите в ближайший детский дом пожалуйста.

Считайте что это было волонтерство во имя истины )


Договорились. Но дети не поймут.

Mind

Гость888

Create index CCC on AAA (fname)

select * from AAA where fname = 'sss'


Добавьте хотя бы одно значимое поле, а иначе aKey и fname содержатся в индексе CCC:

ALTER TABLE dbo.AAA ADD
	fname2 varchar(50) NULL

и получим Clustered Index Scan


Clustered не получу за наличием его отсутствия. Но при наличии таки да - все может быть. Пробовать лень. Да и * это для примера - в реальных системах за такое девелоперов надо пристреливать. Желательно насмерть. Так что если после селекта будет стоять только одно поле что и в вхере - ничего не поменяется.
8 янв 10, 09:51    [8156511]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
Mind
Так только при этих определенных условиях Scan оптимальнее чем Seek. В эти условия как раз попадает низкая селективность, выборка полей при отсутствии покрывающего индекса и относительно небольшие таблицы, где, условно, количество прочитанных страниц памяти для полного сканирования кластерного индекса оказывается меньше чем страниц прочитанных для Index Seek + для каждого найденного значения Key Lookup.
Можно конечно долго дискутировать на тему того что на каждый запрос должны быть покрывающие индексы и селективность должна быть высокая, но не всегда это возможно. Поэтому Seek "в большинстве случаев" оптимален, но не всегда.


Говорят в Молдавии детей в младенчестве вином разбавленым поят - что бы спали крепче. На поля с низкой селективностью будут строить индексы только те кого поили неразбаленым вином. Но такие обычно не работают с SQL Server и прочими схожими системами - им более подходит лопата и кайло.

Mind
Поэтому Seek "в большинстве случаев" оптимален, но не всегда.


Понимаете ли ... ответ товарища jekaSQL вот тут:

jekaSQL
Гость888
Mind
а не повис бы на час, пытаясь сделать Index Seek для миллиона строк.

Интересно, вы знаете операции более эффективные чем Index Seek?

Конечно, например Index scan и Table scan.


является немногим более содержательным чем ответ: а не надо в нашу базу данных вообще лезть.

Меня, если честно, сильно за интриговал вариат:

повис бы на час, пытаясь сделать Index Seek для миллиона строк

Можете привести пример когда оптимизатор принимает такое решение - использовать индекс сик когда тейблскан или тейблсик будет куда дешевле. Сваливания с сика на скан я наблюдал много раз, вот наоборот - пока еще нет. Очень любопытно - что же я пропустил. Заранее спасибо.


Легко. Но проблема не столько оптимизатора, сколько различных параметров передаваемых оптимизатору для выбора плана, ну и собственно кэширование неправильного плана, но про это по-моему на форуме уже писали много раз.


CREATE TABLE AAA ( 
	aKey int identity( 10000000,1 ) PRIMARY KEY, 
    fname VARCHAR(50),
	fname2 varchar(50) NULL
)
GO

INSERT into AAA (fname) values ('sss') 
GO 100

INSERT into AAA (fname) values ('bbb') 
GO 99900

Create index CCC on AAA (fname)
GO

CREATE PROCEDURE PROC1
@fname VARCHAR(50)
AS
select * from AAA where fname = @fname
GO

EXEC PROC1 'sss' -- Index Seek + lookup
EXEC PROC1 'bbb' -- Index Seek + lookup -- Но Scan был бы оптимальнее 

-- А теперь тоже самое
DROP PROCEDURE PROC1
GO
CREATE PROCEDURE PROC1
@fname VARCHAR(50)
AS
select * from AAA where fname = @fname
GO

EXEC PROC1 'bbb' -- Index Scan
8 янв 10, 09:57    [8156512]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Кудряшка
Кудряшка
Mind
и получим Clustered Index Scan


а там кластерного индекса вроде нет


а стоп! PRIMARY KEY обозначен! :) сорри


И на старуху бывает проруха, как говорила польская красавица Инга Заёнц. Я тоже не придал значения что клипбоард принес...
8 янв 10, 09:59    [8156513]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
Легко. Но проблема не столько оптимизатора, сколько различных параметров передаваемых оптимизатору для выбора плана, ну и собственно кэширование неправильного плана, но про это по-моему на форуме уже писали много раз.


Я имел ввиду что когда по всем правилам балета ДОЛЖЕН быть скан а вываливает сик. Что наоборот я видел - я ужо сказал.
8 янв 10, 10:04    [8156517]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888

Clustered не получу за наличием его отсутствия. Но при наличии таки да - все может быть. Пробовать лень.


PRIMARY KEY по умолчанию создается кластерным. Но если в этом примере его создать некластерным, то картина еще красивее - Table Scan!

Гость888

Да и * это для примера - в реальных системах за такое девелоперов надо пристреливать. Желательно насмерть. Так что если после селекта будет стоять только одно поле что и в вхере - ничего не поменяется.


Конечно, если у нас покрывающий индекс то маловероятно что будет использован кластерный скан, но это вырожденный пример. А при добавлении полей в SELECT эффективность Seek по такому индексу быстро падает.
8 янв 10, 10:11    [8156523]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Кудряшка
Если по теме - тоже интересует вопрос.
У меня вот есть запрос, который в процедуре формирует неоптимальный план (на мой взгляд), тогда как если запускать его как запрос - все чудесно. Самое интересное запрос в процедуре один единственный.
3 дня страдала, в итоге использую хинт (на форум выкладывать лень заела :) ).
Теперь думаю может тут спросить, столько знающих людей собралось!


Проблема в том что да, для разных условий where может быть оптимален либо один либо другой план - тут дело как раз в статистике по полям. Мне следовало задать вопрос поподробнее - дело в том что из моего опыта часто prepared statements выдают совершенно иной план запроса чем недавно скомпилированая SP. И там еще ряд у меня мелких вопросов есть по поводу

Ответ мог бы дать кто-либо из Самого Микрософта - как оптимизатор это все переваривает в себе.
8 янв 10, 10:12    [8156524]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Кудряшка
Если по теме - тоже интересует вопрос.
У меня вот есть запрос, который в процедуре формирует неоптимальный план (на мой взгляд), тогда как если запускать его как запрос - все чудесно. Самое интересное запрос в процедуре один единственный.
3 дня страдала, в итоге использую хинт (на форум выкладывать лень заела :) ).
Теперь думаю может тут спросить, столько знающих людей собралось!


Возможно там тоже проблема с параметрами.
8 янв 10, 10:15    [8156529]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Гость888
aleks2
Гость888
aleks2

...


По-моему вы молдованин. Смотрите объяснения этому феномену выше.


Хе-хе... как ты думаешь, что лучше: быть молдаванином или идиотом с высоким рейтингом?


Вам выбор - вам и выбирать.

PS Для вашего образования - рейт от рейтинга отличаейтся примерно так же как Петя от пейтинга. А теперь пшел вон, дурак.


А маэстро петтинга нервничает?
Очень помогает размышление о собственной незаменимости...
8 янв 10, 10:15    [8156530]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
Конечно, если у нас покрывающий индекс то маловероятно что будет использован кластерный скан, но это вырожденный пример.


Я бы сказал что наоборот - с появлением INCLUDED и WHERE в CREATE INDEX - картина куда симпатичнее.

Кстати, а никто не игрался КАК влияет compression на планы запросов? Не на скорость выплнения оных - а на планы именно.
8 янв 10, 10:16    [8156532]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
aleks2
Гость888
aleks2
Гость888
aleks2

...


По-моему вы молдованин. Смотрите объяснения этому феномену выше.


Хе-хе... как ты думаешь, что лучше: быть молдаванином или идиотом с высоким рейтингом?


Вам выбор - вам и выбирать.

PS Для вашего образования - рейт от рейтинга отличаейтся примерно так же как Петя от пейтинга. А теперь пшел вон, дурак.


А маэстро петтинга нервничает?
Очень помогает размышление о собственной незаменимости...


Сочуствую. Вы себе бабу что ли резиновую купите.
8 янв 10, 10:17    [8156533]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Кудряшка
Если по теме - тоже интересует вопрос.
У меня вот есть запрос, который в процедуре формирует неоптимальный план (на мой взгляд), тогда как если запускать его как запрос - все чудесно. Самое интересное запрос в процедуре один единственный.
3 дня страдала, в итоге использую хинт (на форум выкладывать лень заела :) ).
Теперь думаю может тут спросить, столько знающих людей собралось!


Ежели лень выкладывать - то можно только очень абстрактно рекомендации выдать.

1. Выложить всеж.
2. Применить разложение запроса на части с использованием временных таблиц. Помогает практически всегда и очень сильно.
3. Создать правильные индексы (наводящие оптимизатор на путь истинный...) и преписать сам запрос, но тута опять же п.1 нужен.
4. Оставить как есть - могет быть рассосется.
8 янв 10, 10:19    [8156534]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Гость888
aleks2
Гость888
aleks2
Гость888
aleks2

...


По-моему вы молдованин. Смотрите объяснения этому феномену выше.


Хе-хе... как ты думаешь, что лучше: быть молдаванином или идиотом с высоким рейтингом?


Вам выбор - вам и выбирать.

PS Для вашего образования - рейт от рейтинга отличаейтся примерно так же как Петя от пейтинга. А теперь пшел вон, дурак.


А маэстро петтинга нервничает?
Очень помогает размышление о собственной незаменимости...


Сочуствую. Вы себе бабу что ли резиновую купите.


Немножко однобоко, что наводит на мысль: у вас проблемы?
Желаете об ЭТОМ поговорить?
8 янв 10, 10:20    [8156535]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
aleks2
Желаете об ЭТОМ поговорить?


О вас я поговорить не желаю. ЭТО меня не интересует.
8 янв 10, 10:22    [8156537]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
Кудряшка
Если по теме - тоже интересует вопрос.
У меня вот есть запрос, который в процедуре формирует неоптимальный план (на мой взгляд), тогда как если запускать его как запрос - все чудесно. Самое интересное запрос в процедуре один единственный.
3 дня страдала, в итоге использую хинт (на форум выкладывать лень заела :) ).
Теперь думаю может тут спросить, столько знающих людей собралось!


Проблема в том что да, для разных условий where может быть оптимален либо один либо другой план - тут дело как раз в статистике по полям. Мне следовало задать вопрос поподробнее - дело в том что из моего опыта часто prepared statements выдают совершенно иной план запроса чем недавно скомпилированая SP. И там еще ряд у меня мелких вопросов есть по поводу

Ответ мог бы дать кто-либо из Самого Микрософта - как оптимизатор это все переваривает в себе.


Скомпилированая SP оптимизируется под параметры с которыми она была запущена в первый раз, собственно в этот момент и происходит построение плана. А просто запрос оптимизируется несколько по другому, я думаю это должно быть подробно описано в майкрософтовых мануалах, но пока что мне такое на глаза не попадалось.
8 янв 10, 10:23    [8156540]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
Скомпилированая SP оптимизируется под параметры с которыми она была запущена в первый раз, собственно в этот момент и происходит построение плана. А просто запрос оптимизируется несколько по другому, я думаю это должно быть подробно описано в майкрософтовых мануалах, но пока что мне такое на глаза не попадалось.


Я и говорю именно о случае когда параметры одни и те же что в prepared statements что в SP. Или вы хотите сказать что prepared statements строит план по сферическому коню в вакууме - то есть без учета хоть каких значений?
8 янв 10, 10:25    [8156544]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
Mind
Скомпилированая SP оптимизируется под параметры с которыми она была запущена в первый раз, собственно в этот момент и происходит построение плана. А просто запрос оптимизируется несколько по другому, я думаю это должно быть подробно описано в майкрософтовых мануалах, но пока что мне такое на глаза не попадалось.


Я и говорю именно о случае когда параметры одни и те же что в prepared statements что в SP. Или вы хотите сказать что prepared statements строит план по сферическому коню в вакууме - то есть без учета хоть каких значений?


Именно :-)

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



Best Practices: Using Constant Folding and Compile-Time Expression Evaluation for Generating Optimal Query Plans

To make sure you generate optimal query plans, it is best to design queries, stored procedures, and batches so that the query optimizer can accurately estimate the selectivity of the conditions in your query, based on statistics about your data distribution. Otherwise, the optimizer must use a default estimate when estimating selectivity (such as 30 percent in the previous example).

To make sure that the cardinality estimator of the optimizer provides good estimates, you should first make sure that the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS database SET options are ON (the default setting), or that you have manually created statistics on all columns referenced in a query condition. Then, when you are designing the conditions in your queries, do the following when it is possible:

* Avoid the use of local variables in queries. Instead, use parameters, literals, or expressions in the query.
* Limit the use of operators and functions embedded in a query that contains a parameter to those listed under Compile-Time Expression Evaluation for Cardinality Estimation.
* Make sure that constant-only expressions in the condition of your query are either constant-foldable, or can be evaluated at compilation time.
* If you have to use a local variable to evaluate an expression to be used in a query, consider evaluating it in a different scope than the query. For example, it may be helpful to perform one of the following:
o Pass the value of the variable to a stored procedure that contains the query you want to evaluate, and have the query use the procedure parameter instead of a local variable.
o Construct a string that contains a query based in part on the value of the local variable, and then execute the string by using dynamic SQL (EXEC or sp_executesql).
o Parameterize the query and execute it by using sp_executesql, and pass the value of the variable as a parameter to the query.

http://msdn.microsoft.com/en-us/library/ms175933.aspx
8 янв 10, 10:55    [8156574]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Оно! И уже даже с примерами:

Avoid use of local variables in queries

If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a reduced-quality estimate, or a guess for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer typically can pick a better query plan. For example, consider this query, which uses a local variable.
Transact-SQL

declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate

The number of rows from Sales.SalesOrderHeader that the optimizer estimates will qualify versus the condition h.OrderDate >= @StartOrderDate is 9439.5, which is exactly 30 percent of the size of the table. You can use the graphical showplan for the query and right-click the plan node for Sales.SalesOrderHeader to display this cardinality estimate. In a prerelease version of SQL Server 2008 used while preparing this paper, the plan chosen uses a merge join (the observations that follow are based on this same SQL Server 2008 version; your results may differ depending on your SQL Server version, available memory, and so on.). Now, consider this equivalent query, which doesn't use a local variable:
Transact-SQL

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= '20040731'

The cardinality of the result set for the predicate "h.OrderDate >= '20040731'" is estimated as 40 in the graphical showplan for the query (right-click the Filter operator), for a selectivity of 0.13%. The plan chosen for this query uses a nested loop join instead of a merge join because of this improved estimate.

Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, a selectivity estimate of 1/(number of unique values in column) is used, which is accurate.

To eliminate the use of local variables, consider (1) rewriting the query to use literals instead of variables, (2) using sp_executesql with parameters that replace your use of local variables, or (3) using a stored procedure with parameters that replace your use of local variables. Dynamic SQL via EXEC may also be useful for eliminating local variables, but it typically results in higher compilation overhead and more complex programming. A new enhancement in SQL Server 2008 is that the OPTION(RECOMPILE) hint expands both variables and parameters inline as constant literals before optimization. If you can tolerate the extra compile time, this is an excellent way to work around the problem of poor selectivity estimates associated with variables in query predicates.

http://msdn.microsoft.com/en-us/library/dd535534.aspx

С вас $10, но так и быть, отдайте детям.
8 янв 10, 11:14    [8156590]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить