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

Откуда:
Сообщений: 15
Доброго времени суток!
Есть ms sql 2008 standart, есть маленькая база (~200mb), находится в режиме simple.
Простой запрос к таблице из консоли выполняется быстро:
SELECT TOP 1000 [Id]
,[ObjectId]
,[RouteId]
,[RouteDate]
,[RowId]
,[Time]
,[CreatedTime]
,[PointIndex]
,[TimeStamp]
FROM [MonitorData].[dbo].[ResultTrack]

Если добавляю условие:
where [RouteDate] = '2012-02-03'
то запрос выполняет ооочень долго, правда в это время висит служба (многопоточная), которая постоянно пишет в базу, но небольшыми порциями (нагрузка небольшая).

Id -- это PK IDENTITY (1,1), еще есть составной неуникальный индекс по полям RouteId (nvarchar(15)), RouteDate (date), RowId (int)

Из-за чего такой эффект получается, индекс же по этому полю есть? Что можно сделать?
3 апр 12, 09:07    [12354891]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
RA093, индекс по дате жеж составной, а фильтра по RouteId вы не указываете. Создайте индекс по дате. И вместо '2012-02-03' лучше писать '20120203', так не будет проблем с региональными настройками. А начать надо было с просмотра плана.
3 апр 12, 09:12    [12354901]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
kDnZP, вариант с дефисами тоже стандарт и обрабатывается независимо от региональных настроек.
3 апр 12, 09:17    [12354922]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
RA093
Member

Откуда:
Сообщений: 15
Уточнение: запрос долго выполняется, когда работает программа, которая пишет в базу, в основном это Insert. Если записи в базу не идет, то запрос выполняется махом.
3 апр 12, 09:17    [12354923]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
RA093
Member

Откуда:
Сообщений: 15
kDnZP
RA093, индекс по дате жеж составной, а фильтра по RouteId вы не указываете. Создайте индекс по дате. И вместо '2012-02-03' лучше писать '20120203', так не будет проблем с региональными настройками. А начать надо было с просмотра плана.

Где-то читал, что лучше делать составные индексы... В данном случае по составному не будет так эффективно работать?
3 апр 12, 09:20    [12354934]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Alexander Karavaev
kDnZP, вариант с дефисами тоже стандарт и обрабатывается независимо от региональных настроек.

Не совсем так. Сравните 112 и 126, 127(6, 7) это всеже чуть разные форматы. И по совести для случая ТС тогда надобно писать так: '2012-02-03T00:00:00.000'


http://msdn.microsoft.com/ru-ru/library/ms187928(v=sql.90).aspx
6 Поддерживается только при приведении символьных данных к типу datetime или smalldatetime. При приведении символьных данных, представляющих только дату или только время, к типам datetime и smalldatetime неуказанное время устанавливается в 00:00:00.000, а неуказанная дата — в 1900-01-01.

7 Необязательный признак часового пояса П используется для упрощения сопоставления XML-значений типа datetime, содержащих сведения о часовом поясе, со значениями SQL Server типа datetime без таких сведений. П — это признак часового пояса UTC-0. Другие пояса обозначаются смещением в формате ЧЧ:ММ в направлении + или -. Например: 2006-12-12T23:45:12-08:00.

При преобразовании в символьные данные из smalldatetime стили, включающие секунды или миллисекунды, будут содержать нули в соответствующих позициях. При преобразовании из datetime или smalldatetime ненужные части даты можно усекать с помощью типа данных char или varchar соответствующей длины.
'
3 апр 12, 09:25    [12354950]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
RA093
Где-то читал, что лучше делать составные индексы... В данном случае по составному не будет так эффективно работать?

RA093, ну так поглядите в плане - используется он или нет. У меня есть предположение, что не используется.
3 апр 12, 09:26    [12354956]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RA093
Уточнение: запрос долго выполняется, когда работает программа, которая пишет в базу, в основном это Insert. Если записи в базу не идет, то запрос выполняется махом.
RCSI включите, если версия сервера позволяет.
3 апр 12, 09:34    [12354988]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
RA093
Member

Откуда:
Сообщений: 15
invm
RA093
Уточнение: запрос долго выполняется, когда работает программа, которая пишет в базу, в основном это Insert. Если записи в базу не идет, то запрос выполняется махом.
RCSI включите, если версия сервера позволяет.

RCSI -- это что? Просто я не dba, а программист, но тут пришлось базу наваять...
3 апр 12, 09:41    [12355036]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
iljy
Member

Откуда:
Сообщений: 8711
RA093
kDnZP
RA093, индекс по дате жеж составной, а фильтра по RouteId вы не указываете. Создайте индекс по дате. И вместо '2012-02-03' лучше писать '20120203', так не будет проблем с региональными настройками. А начать надо было с просмотра плана.

Где-то читал, что лучше делать составные индексы... В данном случае по составному не будет так эффективно работать?

Вам не про составной-не составной индекс говорят, а про порядок полей в нем.
3 апр 12, 09:45    [12355057]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
kDnZP, ну то что типы разные и производится неявное преобразование и так понятно, но это запросу никак не пошает. Я говорил лишь о том, что в данном случае мы выполняем преобразование в дату из строки, а строка задана в соотвествии со стандартом ISO8601, поэтому преобразование будет независимо от настроек локали.
[url=]http://msdn.microsoft.com/en-us/library/bb630352.aspx[/url]
3 апр 12, 09:54    [12355107]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
RA093
В данном случае по составному не будет так эффективно работать?
В данном случае индекс у вас вообще не используется.

Что бы запрос быстро работал, нужно либо сделать индекс по полю RouteDate, либо изменить порядок полей в вашем индексе, поставив поле RouteDate первым (но тогда будет медленно работать запрос с выборкой только по полю RouteId)
RA093
Где-то читал, что лучше делать составные индексы...
Нет, нужно делать индексы, правильные для конкретных запросов. Еслиб можно было построить индекс, подходящий всегда, сервер сам бы его создавал для всех таблиц.
3 апр 12, 10:06    [12355175]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
iljy
Member

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

не говорите глупостей.
set dateformat ymd
select cast ('2012-01-02' as datetime), cast ('20120102' as datetime)

set dateformat ydm
select cast ('2012-01-02' as datetime), cast ('20120102' as datetime)


А ISO8601 - это гггг-мм-ддTчч:мм:сс.ммм
3 апр 12, 10:08    [12355187]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RA093
invm
пропущено...
RCSI включите, если версия сервера позволяет.

RCSI -- это что? Просто я не dba, а программист, но тут пришлось базу наваять...
Версионность включите. Чтобы читатели не блокировались писателями. Подробности в документации -- Основные сведения об уровнях изоляции на основе управления версиями строк
3 апр 12, 10:09    [12355192]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iljy,

если тип DATE, то для него стандартный формат 'YYYY-MM-DD'

http://msdn.microsoft.com/ru-ru/library/bb630352(v=sql.100).aspx
3 апр 12, 10:27    [12355274]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
RA093
Где-то читал, что лучше делать составные индексы... В данном случае по составному не будет так эффективно работать?
Если в запросе не используется поиск по первому полю составного индекса, то весь индекс не используется!
Причём для первого поля нужно сравнение оператором "="

Точно так же, если не используется условие для второго поля в индексе,
то он не будет использован для третьего и т.д.
3 апр 12, 10:31    [12355298]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
RA093
Member

Откуда:
Сообщений: 15
Спасибо всем, про индексы более-менее понятно.
А как оптимизировать, если в базу в основном идет параллельная запись (delete/insert/update) и лишь изредка чтение, чтобы свести к минимуму конфликты блокировок?
3 апр 12, 10:34    [12355321]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
RA093
А как оптимизировать, если в базу в основном идет параллельная запись (delete/insert/update) и лишь изредка чтение, чтобы свести к минимуму конфликты блокировок?
1. Максимально короткие транзакции
2. Не делать лишние индексы
3. Если есть вставка большого количества данных, по возможности сделать её групповой, не отдельными записями, по возможности использовать нелогируемую вставку.
3 апр 12, 10:52    [12355427]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
RA093
Спасибо всем, про индексы более-менее понятно.
А как оптимизировать, если в базу в основном идет параллельная запись (delete/insert/update) и лишь изредка чтение, чтобы свести к минимуму конфликты блокировок?


твой запрос тормозит независимо от того, есть ли у тебя параллельная модификация данных.
так что много об этом не думай.
3 апр 12, 11:08    [12355535]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
RA093
Member

Откуда:
Сообщений: 15
alexeyvg
3. Если есть вставка большого количества данных, по возможности сделать её групповой, не отдельными записями, по возможности использовать нелогируемую вставку.

Нелогируемая вставка -- это что за зверь?
3 апр 12, 11:09    [12355540]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
RA093, смотрите bcp или BULK INSERT
3 апр 12, 12:08    [12355934]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
RA093
alexeyvg
3. Если есть вставка большого количества данных, по возможности сделать её групповой, не отдельными записями, по возможности использовать нелогируемую вставку.

Нелогируемая вставка -- это что за зверь?
Вот: https://www.sql.ru/blogs/gladchenko/320

Она не совсем нелогируемая, называется "минимально протоколируемые операции".

Может быть в виде балк-операций или в виде малопротоколируемого инсёрта. Как обычно, малопротоколируемый инсёрт может и замедлить вставку (иначе бы это было включено по умолчанию), так что перед применением нужно почитать по ссылке :-)
3 апр 12, 12:26    [12356133]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
iljy, давайте не будем так безапелляционно. Мы здесь все не глупые люди.

Вообще говоря, тип datetime сейчас уже не рекомендован к использованию и оставлен для совместимости.
Посмотрите, пожалуйста, на вывод:
set dateformat ydm
select cast ('2012-01-02' as datetime2), cast ('20120102' as datetime2)
select cast ('2012-01-02' as date), cast ('20120102' as date)


Но, спасибо большое, что обратили моё внимание на проблему с datetime, я согласен, что учитывая это, вариант без дефисов более универсален.
Более того, я нашёл подтвердающую это статью в MSDN:
http://msdn.microsoft.com/en-us/library/ms191307(v=sql.110).aspx

Ещё раз спасибо за дискуссию.
3 апр 12, 14:44    [12357507]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Alexander Karavaev
Но, спасибо большое, что обратили моё внимание на проблему с datetime, я согласен, что учитывая это, вариант без дефисов более универсален.
При указании даты в формате ISO8601 никаких проблем у datetime нету. 2012-01-02 - это не формат ISO8601
set dateformat ydm
select cast ('2012-01-02' as datetime), cast ('2012-01-02T00:00:00' as datetime)

Вот для datetime написано: "The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting." что полностью соответствует действительности.

http://msdn.microsoft.com/en-us/library/ms187819.aspx
3 апр 12, 14:58    [12357678]     Ответить | Цитировать Сообщить модератору
 Re: Медленно выполняется запрос с условием  [new]
Alexander Karavaev
Member

Откуда: Москва
Сообщений: 40
alexeyvg, cкажем так, SQL Server 2005 использует специфический формат задания даты в формате ISO8601

Сам же стандарт это вполне допускает, в связи с чем и были внесены изменения в datetime2:
http://dotat.at/tmp/ISO_8601-2004_E.pdf
4.1.2.2 Complete representations
When the application identifies the need for a complete representation of a calendar date, it shall be one of
the numeric expressions as follows, where [YYYY] represents a calendar year, [MM] the ordinal number of a
calendar month within the calendar year, and [DD] the ordinal number of a calendar day within the calendar
month.
Basic format: YYYYMMDD Example: 19850412
Extended format: YYYY-MM-DD Example: 1985-04-12
3 апр 12, 15:05    [12357740]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить