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

DECLARE @r char(7)
SET @r = '1234567'

select *
from Table
where r = @r 

и

select *
from Table
where r = '1234567'

производит разные execution plans? Интересуют не поверхностные рассуждения и домыслы а принцип - ПОЧЕМУ? Что такого различного и почему видит компилятор и оптимизатор.

Спасибо
6 янв 10, 01:53    [8151156]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

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

Компилятор видит параметризированный запрос в первом случае и константу в выражении WHERE во втором. Соответственно пытается оптимизировать либо под одно конкретное значение, либо в общем, что-бы какое бы значение вы туда не передали запрос все равно бы выполнялся с удовлетворительной производительностью, а не повис бы на час, пытаясь сделать Index Seek для миллиона строк.
6 янв 10, 04:23    [8151211]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Если придраться к словам (не к смыслу), то параметризовываться могут/будут оба. Просто, как было сказано, генератор планов может/будет учитывать значение параметров.
В первом запросе значения параметров не "видны".

DECLARE @r char(7)
SET @r = '1234567'

select *
from Table
where r = @r
option(optimize for(@r = '1234567'))

Лично бы я, неу употреблял бы выражение "удовлетворительной производительностью", а оставил бы так - нет оптимизации по конкретным значениям. Ибо в каждом случае всё может выйти противоположным образом.
6 янв 10, 23:06    [8153483]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
а не повис бы на час, пытаясь сделать Index Seek для миллиона строк.


Интересно, вы знаете операции более эффективные чем Index Seek?
7 янв 10, 02:19    [8153972]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
jekaSQL
Member

Откуда: Бабруйск
Сообщений: 596
Гость888
Mind
а не повис бы на час, пытаясь сделать Index Seek для миллиона строк.


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


Конечно, например Index scan и Table scan.
А по хорошему, вы приведите планы, а там разберемся.
7 янв 10, 02:26    [8153990]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
jekaSQL
Гость888
Интересно, вы знаете операции более эффективные чем Index Seek?


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


WHAT????

http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.
7 янв 10, 03:42    [8154041]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
jekaSQL
Гость888
Интересно, вы знаете операции более эффективные чем Index Seek?


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


WHAT????

http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.


А вы знаете что-либо эффективней карьерного экскаватора для выкапываения ям? Почему же вы тогда его не используете чтобы дерево посадить?

В том тексте, что вы скинули, как раз все и объясняется, перечитайте еще раз.
Если у вас в таблице под миллион строк и 90% значений поля r = '1234567' то Index Scan будет намного эффективней чем Index Seek, в данном конкретном случае.
Другой вариант, для того же запроса. Допустим у нас есть кластерный индекс по некоему ID, а также индекс по полю r. Так вот, при небольшой селективности по r, может оказаться значительно выгоднее сделать Clustered Index Scan чем Index Seek + Key Lookup, если же изменить запрос на

select r
from Table
where r = '1234567'

то вероятность что оптимизатор выберет Seek будет уже значительно больше.
7 янв 10, 04:16    [8154055]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind

А вы знаете что-либо эффективней карьерного экскаватора для выкапываения ям? Почему же вы тогда его не используете чтобы дерево посадить?


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

Mind

В том тексте, что вы скинули, как раз все и объясняется, перечитайте еще раз.


Я знаю что такое селективность поля и что такое статистика.

Mind

Если у вас в таблице под миллион строк и 90% значений поля r = '1234567' то Index Scan будет намного эффективней чем Index Seek, в данном конкретном случае.


Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Mind

Другой вариант, для того же запроса. Допустим у нас есть кластерный индекс по некоему ID, а также индекс по полю r. Так вот, при небольшой селективности по r, может оказаться значительно выгоднее сделать Clustered Index Scan чем Index Seek + Key Lookup, если же изменить запрос на

select r
from Table
where r = '1234567'

то вероятность что оптимизатор выберет Seek будет уже значительно больше.


Вот это вот, пожалуй, ближе к истине, да.
7 янв 10, 05:47    [8154081]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
jekaSQL
Member

Откуда: Бабруйск
Сообщений: 596
Гость888

Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Было бы интересно, давайте ваш пример!
7 янв 10, 23:37    [8156115]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
1
jekaSQL
Гость888

Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Было бы интересно, давайте ваш пример!


Ну во-первых если

where r = @r

попадет в эти самые 10%...

Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx
8 янв 10, 01:29    [8156233]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
jekaSQL
Member

Откуда: Бабруйск
Сообщений: 596
Гость888
1
jekaSQL
Гость888

Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Было бы интересно, давайте ваш пример!


Ну во-первых если

where r = @r

попадет в эти самые 10%...

Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx


Ну вообще-то я просил ВАШ пример, а не ссылку на достаточно скучную для меня лично статью.
Как тут принято, приведите DDL скрипт, скрипт для заполнения полученного данными и собственно запросы (и их планы), которыми вы хотите что-то доказать.
8 янв 10, 03:57    [8156337]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
jekaSQL
Гость888
1
jekaSQL
Гость888

Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Было бы интересно, давайте ваш пример!


Ну во-первых если

where r = @r

попадет в эти самые 10%...

Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx


Ну вообще-то я просил ВАШ пример, а не ссылку на достаточно скучную для меня лично статью.
Как тут принято, приведите DDL скрипт, скрипт для заполнения полученного данными и собственно запросы (и их планы), которыми вы хотите что-то доказать.


My rate starts from $70ph+benefits. Не хотите читать или вам скушно читать - это уже не мои проблемы. Хотите доказательств - свой рейт я обозначил.
8 янв 10, 05:19    [8156404]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Гость888

My rate starts from $70ph+benefits. Не хотите читать или вам скушно читать - это уже не мои проблемы. Хотите доказательств - свой рейт я обозначил.


Уууу... как мы пальцы гнуть умеем!

=========================
- Что вы знаете о великих гуру MS SQL?
- Ну... нас немного.
8 янв 10, 06:30    [8156428]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

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

А вы знаете что-либо эффективней карьерного экскаватора для выкапываения ям? Почему же вы тогда его не используете чтобы дерево посадить?


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

Мы о том, что эффективность инструмента очень сильно зависит от конкретной задачи, и говорить о том что Index Seek всегда будет самым эффективным способом доступа к таблице не совсем корректно.

Гость888

Mind

В том тексте, что вы скинули, как раз все и объясняется, перечитайте еще раз.


Я знаю что такое селективность поля и что такое статистика.


Тогда я думаю должны знать как это влияет на эффективность Seek и Scan.
8 янв 10, 07:25    [8156440]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
aleks2
Гость888

My rate starts from $70ph+benefits. Не хотите читать или вам скушно читать - это уже не мои проблемы. Хотите доказательств - свой рейт я обозначил.


Уууу... как мы пальцы гнуть умеем!

=========================
- Что вы знаете о великих гуру MS SQL?
- Ну... нас немного.


Вы разве не поняли что ваш уровень общения меня не интересует? Идите-ка по одному известному адресу.
8 янв 10, 07:33    [8156441]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
Мы о том, что эффективность инструмента очень сильно зависит от конкретной задачи, и говорить о том что Index Seek всегда будет самым эффективным способом доступа к таблице не совсем корректно.


Безусловно. Правильно говорить "в большинстве случаев". Под такое обычно попадает "Index Seek для миллиона строк" - он обычно существенно быстрее чем любой Table scan или Index Scan. Особенно когда у нас есть покрывающие запрос индексы.

Mind
Тогда я думаю должны знать как это влияет на эффективность Seek и Scan.


Именно потому и удивился. Если я неправ - не могли бы вы мне показать пример когда Scan будет быстрее чем Seek. Естественно таблицы размером 8кб меня не интересуют, как и не интересуют малоселективные поля.
8 янв 10, 07:38    [8156443]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

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

Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали. Но мой вопрос был опять не об этом.

Было бы интересно, давайте ваш пример!


Ну во-первых если

where r = @r

попадет в эти самые 10%...

Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx


Ну вообще-то я просил ВАШ пример, а не ссылку на достаточно скучную для меня лично статью.
Как тут принято, приведите DDL скрипт, скрипт для заполнения полученного данными и собственно запросы (и их планы), которыми вы хотите что-то доказать.


My rate starts from $70ph+benefits. Не хотите читать или вам скушно читать - это уже не мои проблемы. Хотите доказательств - свой рейт я обозначил.


А ты сам читал эту ссылку, или рейта хватило только на заголовок?
Статья мягко говоря совсем не об этом, а о том, как путем создания индексированного представления увеличить быстродействие запроса для определенного значения. Но вот Index Seek там так и не заработал на Low-Selectivity Column, так что не зачет.

А первоначальный вопрос был рассмотрен в 2-х первых ответах, а дальше пошли рассуждения и домыслы так и не подкрепленные ни одним реальным примером или внятной ссылкой на первоисточник.
8 янв 10, 07:55    [8156445]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гость888
Mind
Мы о том, что эффективность инструмента очень сильно зависит от конкретной задачи, и говорить о том что Index Seek всегда будет самым эффективным способом доступа к таблице не совсем корректно.


Безусловно. Правильно говорить "в большинстве случаев". Под такое обычно попадает "Index Seek для миллиона строк" - он обычно существенно быстрее чем любой Table scan или Index Scan. Особенно когда у нас есть покрывающие запрос индексы.

Mind
Тогда я думаю должны знать как это влияет на эффективность Seek и Scan.


Именно потому и удивился. Если я неправ - не могли бы вы мне показать пример когда Scan будет быстрее чем Seek. Естественно таблицы размером 8кб меня не интересуют, как и не интересуют малоселективные поля.


Так только при этих определенных условиях Scan оптимальнее чем Seek. В эти условия как раз попадает низкая селективность, выборка полей при отсутствии покрывающего индекса и относительно небольшие таблицы, где, условно, количество прочитанных страниц памяти для полного сканирования кластерного индекса оказывается меньше чем страниц прочитанных для Index Seek + для каждого найденного значения Key Lookup.
Можно конечно долго дискутировать на тему того что на каждый запрос должны быть покрывающие индексы и селективность должна быть высокая, но не всегда это возможно. Поэтому Seek "в большинстве случаев" оптимален, но не всегда.
8 янв 10, 08:11    [8156447]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
Mind
А ты сам читал эту ссылку, или рейта хватило только на заголовок?


Вас зависть мучает или только неумение читать? Для особо одареных повторяю что было сказано:

Гость888

Mind

Если у вас в таблице под миллион строк и 90% значений поля r = '1234567' то Index Scan будет намного эффективней чем Index Seek, в данном конкретном случае.


Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали.
...
Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx


Слово решение заметили?

Mind
Статья мягко говоря совсем не об этом, а о том, как путем создания индексированного представления увеличить быстродействие запроса для определенного значения. Но вот Index Seek там так и не заработал на Low-Selectivity Column, так что не зачет.


Решение было получено или нет? Попробуйте ответить одним словом.

Mind
А первоначальный вопрос был рассмотрен в 2-х первых ответах, а дальше пошли рассуждения и домыслы так и не подкрепленные ни одним реальным примером или внятной ссылкой на первоисточник.


Забыли добавить еще что-то про духовность - для полноты осознания диагноза.
8 янв 10, 08:20    [8156450]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Опять же, на примере одной таблицы это все просто, а если скажем селективность по полю высокая, а все равно в результате выполнения запроса выбирается значительная часть строк из таблицы. Самый простой пример - JOIN двух таблиц. Там тоже вполне может произойти ситуация когда проще вычитать весь кластерный индекс целиком, а потом уже делать HASH.
8 янв 10, 08:22    [8156452]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Mind
Member

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

Гость888

Mind

Если у вас в таблице под миллион строк и 90% значений поля r = '1234567' то Index Scan будет намного эффективней чем Index Seek, в данном конкретном случае.


Могу поспорить что я вам покажу пример что даже с вашими 90% может все оказаться все совершено наоборот тому что вы написали.
...
Но вцелом решение вот тут:

http://www.sql-server-performance.com/articles/per/indexing_low_sel_cols_p1.aspx


Слово решение заметили?

Mind
Статья мягко говоря совсем не об этом, а о том, как путем создания индексированного представления увеличить быстродействие запроса для определенного значения. Но вот Index Seek там так и не заработал на Low-Selectivity Column, так что не зачет.


Решение было получено или нет? Попробуйте ответить одним словом.


Решение получено.
Решение фактически сводится к созданию новой таблицы (indexed view), а по ней опять же кластерный скан, что и требовалось доказать, так что я не вижу как это расходится с моим утверждением о том что скан эффективней в данном случае, так что никакого "наоборот" тут не вижу, как и примера.
8 янв 10, 08:33    [8156458]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Гость888
aleks2
Гость888

My rate starts from $70ph+benefits. Не хотите читать или вам скушно читать - это уже не мои проблемы. Хотите доказательств - свой рейт я обозначил.


Уууу... как мы пальцы гнуть умеем!

=========================
- Что вы знаете о великих гуру MS SQL?
- Ну... нас немного.


Вы разве не поняли что ваш уровень общения меня не интересует? Идите-ка по одному известному адресу.


Хе-хе... я не о тебе пекусь. О неофитах...

А так-то, меня тоже не вдохновляют пальцы веером. И полное отсутствие мозгов.

select * FROM aTable WHERE aBitField=cast(1 as bit)

Tablе/Index scan будет эффективнее для многомиллионной таблицы и равновероятных значений (0 и 1) поля bitField. Если чуть-чуть напрячь мозги, достаточно очевидно, что это верно для ЛЮБОГО поля с конечным числом значений.

И только в одном случае Index seek ЗАВЕДОМО эффективнее:
select * FROM aTable WHERE aUniqueIndexField=<some value>
8 янв 10, 08:35    [8156463]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
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 для миллиона строк

Можете привести пример когда оптимизатор принимает такое решение - использовать индекс сик когда тейблскан или тейблсик будет куда дешевле. Сваливания с сика на скан я наблюдал много раз, вот наоборот - пока еще нет. Очень любопытно - что же я пропустил. Заранее спасибо.
8 янв 10, 08:36    [8156465]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
Гость888
Guest
aleks2

...


По-моему вы молдованин. Смотрите объяснения этому феномену выше.
8 янв 10, 08:41    [8156468]     Ответить | Цитировать Сообщить модератору
 Re: Execution Plan  [new]
aleks2
Guest
Гость888
aleks2

...


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


Хе-хе... как ты думаешь, что лучше: быть молдаванином или идиотом с высоким рейтингом?
8 янв 10, 08:56    [8156478]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить