Таблица SQL со многими конкретными датами. Как мне сделать это более масштабируемым?

У меня довольно хлопотная таблица, которая выглядит так:

EventTimeLog 

Id (bigint) | Time (datetime) | LogId (FK to Log tables Id column)

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

Проблема в том, что даты настолько специфичны, и эти даты могут иметь повторяющиеся значения. Пример: 

2015-08-03 23:54:58.000 | 1983
2015-08-03 23:54:58.000 | 1934
2015-08-03 23:54:56.000 | 1647

Через некоторое время запрос становится очень трудным. Обычно около 500 тысяч строк или около того, он начинает пыхтеть, даже если я помещаю индекс в LogId и Time. К тому времени, как я достигаю диапазона в 1 милю и выше, запросы замедляются до ползания ...

Мне действительно нужно это конкретное время, поэтому агрегирование по началу часа или дня - не вариант. Я также сомневаюсь, что реализация столбцов Count для повторяющихся значений времени поможет во многом, поскольку эти даты настолько детализированы. Индексу все равно придется перескакивать через все эти конкретные даты, что происходит очень медленно.

Я не знаю, как сделать эту таблицу более масштабируемой. Может быть, разбить это на ежемесячные таблицы?

По запросу, вот используемый запрос, который начинает пыхтение

SELECT b.User, b.Ip, b.AccountId, a.Time FROM 
EventTimeLog a 
inner join [Log] b on a.LogId = b.Id
WHERE 
b.UserId = '<param>' AND
a.Time >= '<param>' AND
a.Time <= '<pamam>'

Если разница во времени> 2 дней, он пыхтит как сумасшедший. И да, у меня есть индексы в журнале для UserId.


sql-server large-data-volumes
person mBrice1024    schedule 03.09.2015    source источник
comment
почему этой информации нет в журнальных таблицах?   -  person Paolo    schedule 03.09.2015
comment
500К строк, и ваш запрос начинает пыхтеть, хммм, вы смотрели счетчики ресурсов? память и ЦП и т. д. Строки 500 тыс. и всего 3 столбца - это не совсем большая таблица, сервер sql с разумными ресурсами должен справиться с этим довольно хорошо.   -  person M.Ali    schedule 03.09.2015
comment
Вы можете показать нам такой медленный запрос?   -  person Thorsten Kettner    schedule 03.09.2015
comment
Добавлен медленный запрос   -  person mBrice1024    schedule 03.09.2015
comment
Возможно, вам нужно настроить свои запросы и индексы (трудно сказать без дополнительной информации), единственное, что я могу сказать наверняка: журналы имеют тенденцию быстро увеличиваться, но также теряют значение так же быстро, и если они редко используются, вам даже не нужно чтобы побеспокоиться об увеличении производительности запросов. Во-первых, имейте в виду свои требования: зачем у вас журнал и насколько он полезен, во-вторых: можете ли вы разрезать его по дням и просто держать свежие строки готовыми к использованию?   -  person jean    schedule 03.09.2015
comment
Я с @Paolo по этому поводу. Кажется, что наличие таблицы logTime слишком нормализовано, особенно когда кажется, что она взаимосвязана один-к-одному. Почему бы просто не добавить столбец LogTime в таблицу регистрации? Здесь помогло бы, если бы вы могли опубликовать фактический ddl и индексы для обеих таблиц.   -  person Sean Lange    schedule 03.09.2015

Ответы (1)


arrow_upward
0
arrow_downward

В вашей таблице много моментов, которые незначительно отличаются. Это сделало бы индекс огромным и бесполезным.

Так что рассмотрите вычисляемый столбец вместо того, чтобы дать вам менее точное время. Затем создайте индекс для logid + этого вычисляемого столбца.

Я не знаю, какой промежуток времени вы обычно запрашиваете. Для примера возьмем часы. Вы бы усекли дату до часа (например, dateadd(hour, datediff(hour, 0, time), 0) или в виде строки: substring(convert(varchar(25), time, 120), 1, 13)):

Новый столбец (здесь я использую строку):

alter table mytable add comp_hour as substring(convert(varchar(25), time, 120), 1, 13);

Так вы получите, например:

time                            comp_hour
2015-09-03 14:12:10.2158145     '2015-09-03 14'
2015-09-03 14:45:27.4457813     '2015-09-03 14'

Индекс:

create index index_comp_hour on mytable(logid, comp_hour);

Запрос:

select l.user, l.ip, l.accountid, e.time 
from log l
join eventtimelog e on e.logid = l.id and e.comp_hour in ('2015-09-03 13', '2015-09-03 14')
where l.userid = 123;

(Я не уверен, лучше ли использовать индекс mytable(logid, comp_hour) или mytable(comp_hour, logid), или это вообще важно. Вы можете просто создать оба, а затем посмотреть на план выполнения и удалить тот, который не используется.)

person Thorsten Kettner    schedule 03.09.2015
comment
Извини, что так долго не возвращался к тебе. Казалось, это сработало. Для этого добавлен новый столбец TimeAggregate. Затем удалил и переделал индекс для LogId и TimeAggregate, а также сделал «Включить» в столбце «Время». Резко сократите время! - person mBrice1024; 05.09.2015