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

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

я построила для себя личный сайт и вот когда туда стало заходить 100+ человек одновременно, SQL зашкаливает и срубается, сайт падает. я поискала проблемные запросы и решила их оптимизировать. начала читать статьи об оптимизации, включила статистику и получила среди отчётов такую сторку:

автор
Table 'Worktable'. Scan count 1, logical reads 1176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


что за таблица 'Worktable'? нигде не могу найти внятного объяснения, а у меня самой таблицы с таким названием нет.

вот сам запрос, если нужен конкретный случай:

SELECT TOP (150) tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, inna.tblBlogs.UserID, 
tblUsers.Username, tblUserpics.UserpicName, COUNT(tblBlogComments.CommentID) AS Comments 
FROM tblBlogs  
INNER JOIN tblUsers ON tblBlogs.UserID = tblUsers.UserID
LEFT OUTER JOIN tblBlogComments ON tblBlogs.RecordID = tblBlogComments.RecordID 
LEFT OUTER JOIN tblUserpics ON tblBlogs.UserID = tblUserpics.UserID 
GROUP BY tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, tblUsers.Username, tblUserpics.UserpicName 
ORDER BY tblBlogs.RecordID DESC
10 мар 12, 03:52    [12219896]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Worktables

The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are typically generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested.

Worktables are built in tempdb and are dropped automatically at the end of the statement.
10 мар 12, 03:54    [12219897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
udar_molota
я веб-программист, с SQL работаю поверхностно, обычно проблем с ресурсами у наших сайтов нет, посему за оптимизацией SQL запросов никто не следит.

я построила для себя личный сайт и вот когда туда стало заходить 100+ человек одновременно, SQL зашкаливает и срубается, сайт падает. я поискала проблемные запросы и решила их оптимизировать. начала читать статьи об оптимизации, включила статистику и получила среди отчётов такую сторку:

автор
Table 'Worktable'. Scan count 1, logical reads 1176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


что за таблица 'Worktable'? нигде не могу найти внятного объяснения, а у меня самой таблицы с таким названием нет.

вот сам запрос, если нужен конкретный случай:

SELECT TOP (150) tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, inna.tblBlogs.UserID, 
tblUsers.Username, tblUserpics.UserpicName, COUNT(tblBlogComments.CommentID) AS Comments 
FROM tblBlogs  
INNER JOIN tblUsers ON tblBlogs.UserID = tblUsers.UserID
LEFT OUTER JOIN tblBlogComments ON tblBlogs.RecordID = tblBlogComments.RecordID 
LEFT OUTER JOIN tblUserpics ON tblBlogs.UserID = tblUserpics.UserID 
GROUP BY tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, tblUsers.Username, tblUserpics.UserpicName 
ORDER BY tblBlogs.RecordID DESC

Это проблемный запрос? Чтобы его оптимизировать нужен хотя бы план. Да, и как вы пришли к выводу, что это проблемный запрос?
10 мар 12, 05:16    [12219909]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
aleks2
Guest
udar_molota
вот сам запрос, если нужен конкретный случай:


Вы сильно уменьшите уровень геморроя для сервера осознав тривиальную истину:

НЕ пихайте в группировку больше таблиц, чем МИНИМАЛЬНО необходимо

SELECT tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID 
			, Comments, tblUsers.Username, tblUserpics.UserpicName, 
FROM 
(
    SELECT tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, COUNT(tblBlogComments.CommentID) AS Comments 
    FROM 
    (SELECT TOP (150) tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, 
         FROM tblBlogs  
         ORDER BY tblBlogs.RecordID DESC
     ) as tblBlogs
     LEFT OUTER JOIN tblBlogComments ON tblBlogs.RecordID = tblBlogComments.RecordID 
     GROUP BY tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID 
) as tblBlogs
INNER JOIN tblUsers ON tblBlogs.UserID = tblUsers.UserID
LEFT OUTER JOIN tblUserpics ON tblBlogs.UserID = tblUserpics.UserID 
10 мар 12, 15:39    [12220780]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
Mind
вот сам запрос, если нужен конкретный случай:

SELECT TOP (150) tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, inna.tblBlogs.UserID, 
tblUsers.Username, tblUserpics.UserpicName, COUNT(tblBlogComments.CommentID) AS Comments 
FROM tblBlogs  
INNER JOIN tblUsers ON tblBlogs.UserID = tblUsers.UserID
LEFT OUTER JOIN tblBlogComments ON tblBlogs.RecordID = tblBlogComments.RecordID 
LEFT OUTER JOIN tblUserpics ON tblBlogs.UserID = tblUserpics.UserID 
GROUP BY tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, tblUsers.Username, tblUserpics.UserpicName 
ORDER BY tblBlogs.RecordID DESC

Это проблемный запрос? Чтобы его оптимизировать нужен хотя бы план. Да, и как вы пришли к выводу, что это проблемный запрос?[/quot]

нашла какую-то программу, которая выискивает проблемные запросы и исправляет их. она мне их нашла, а исправлять предложила за деньги :)

план есть, прикрепляю. есть также статистика по запросу:
+
Table 'tblBlogComments'. Scan count 151, logical reads 341, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1180, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUserpics'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUsers'. Scan count 0, logical reads 320, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblBlogs'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.



locky, спасибо.


aleks2
Вы сильно уменьшите уровень геморроя для сервера осознав тривиальную истину:
НЕ пихайте в группировку больше таблиц, чем МИНИМАЛЬНО необходимо
это переделанный пример запроса вы мне привели выше? в чём там логика?
насколько я знаю, в гуппировку при появлении агрегационной (надеюсь правильно написала) функции входят все остальные поля из селсекта.

К сообщению приложен файл (tblBlogsPlan.sqlplan - 26Kb) cкачать
11 мар 12, 01:28    [12223229]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
udar_molota
это переделанный пример запроса вы мне привели выше? в чём там логика?
насколько я знаю, в гуппировку при появлении агрегационной (надеюсь правильно написала) функции входят все остальные поля из селсекта.

Логика в том, что не нужно группировать по "ненужным" (излишним) полям.

второе - не понял. Поподробнее?
11 мар 12, 01:34    [12223237]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
locky,
а есть ли смысл в том, что derived tables вы назвали именем уже существующей таблицы? одинаковым. это умышлено? в чём смысл?

а то что я писала, я имела в виду, что если есть запрос на поля x, y, z и count(f), то поля x, y и z должны быть в группировке. но я заметила, что вы из 6 моих оставили 4.

а derived tables не усложняют запрос? вроде как проще тогда сделать уже view, нет?
11 мар 12, 01:48    [12223252]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
aleks2
Guest
udar_molota
а есть ли смысл в том, что derived tables вы назвали именем уже существующей таблицы? одинаковым. это умышлено? в чём смысл?

А шоб алиасы таблиц в select-е не переписывать.
11 мар 12, 06:08    [12223394]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
SELECT tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID , Comments, tblUsers.Username, tblUserpics.UserpicName 
FROM 
(
    SELECT tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID, COUNT(tblBlogComments.CommentID) AS Comments 
    FROM 
    (SELECT TOP (150) tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID 
         FROM tblBlogs  
         ORDER BY tblBlogs.RecordID DESC
     ) as tblBlogs
     LEFT OUTER JOIN tblBlogComments ON tblBlogs.RecordID = tblBlogComments.RecordID 
     GROUP BY tblBlogs.RecordID, tblBlogs.RecordText, tblBlogs.CDate, tblBlogs.UserID 
) as tblBlogs
INNER JOIN tblUsers ON tblBlogs.UserID = tblUsers.UserID
LEFT OUTER JOIN tblUserpics ON tblBlogs.UserID = tblUserpics.UserID 


locky, я прикладываю план вашего запроса. там в одном месте стоит 57% и я не знаю улучшилась ли производительность. не понимаю какой из пламов лучше (план оригинала приложен выше) . не поможете?

К сообщению приложен файл (tblBlogsPlan2.sqlplan - 29Kb) cкачать
11 мар 12, 13:26    [12225060]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
aleks2
Guest
udar_molota
и я не знаю улучшилась ли производительность. не понимаю какой из пламов лучше (план оригинала приложен выше)


Производительность могете измерить тока вы сами.
Смотрите статистику запроса: 0) банальное время исполнения; 1) число чтений; 2) время процессора.
11 мар 12, 13:43    [12225159]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
aleks2
udar_molota
и я не знаю улучшилась ли производительность. не понимаю какой из пламов лучше (план оригинала приложен выше)


Производительность могете измерить тока вы сами.
Смотрите статистику запроса: 0) банальное время исполнения; 1) число чтений; 2) время процессора.


а как? в тех статистиках, которые я могу извлечь исчезла Worktable. а больше там никакой ценной (понятной мне) информации нет. и времени исполнения запроса нет.
11 мар 12, 13:55    [12225231]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
iljy
Member

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

set statistics io on set statistics time on
11 мар 12, 14:41    [12225585]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
iljy, большое спасибо!

CPU time = 31 ms, elapsed time = 173 ms.
и
CPU time = 0 ms, elapsed time = 92 ms.

теперь всё ясно.
большое спасибо.
11 мар 12, 15:47    [12226070]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
udar_molota
locky, я прикладываю план вашего запроса. там в одном месте стоит 57% и я не знаю улучшилась ли производительность. не понимаю какой из пламов лучше (план оригинала приложен выше) . не поможете?

Что меня смущает в этом плане так это - Reason For Early Termination: Time Out.
Учитывая относительную простоту запроса и ничтожное количество строк в таблицах, не совсем понятно как серверу может не хватать ресурсов чтобы перебрать все возможные планы. Не то чтобы этот план плохой и можно придумать что-то на порядок лучше, но сам по себе признак не хороший. Если это план с продакшн сервера, то что-то вашему серверу очень нехорошо.
13 мар 12, 10:05    [12236529]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация SQL запроса  [new]
udar_molota
Member

Откуда: Тель-Авив
Сообщений: 61
Mind
udar_molota
locky, я прикладываю план вашего запроса. там в одном месте стоит 57% и я не знаю улучшилась ли производительность. не понимаю какой из пламов лучше (план оригинала приложен выше) . не поможете?

Что меня смущает в этом плане так это - Reason For Early Termination: Time Out.
Учитывая относительную простоту запроса и ничтожное количество строк в таблицах, не совсем понятно как серверу может не хватать ресурсов чтобы перебрать все возможные планы. Не то чтобы этот план плохой и можно придумать что-то на порядок лучше, но сам по себе признак не хороший. Если это план с продакшн сервера, то что-то вашему серверу очень нехорошо.

ну... сервер хостинговый. какой дали. у них там с выдачей ресурсов туго.
13 мар 12, 12:43    [12237960]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить