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

Откуда:
Сообщений: 39
Ситуация следующая:
Есть табличка
Id Rating
1 3000
1405832 2999
2 2999
89734 2998
...............
387462 1


Вот таких вот записей может быть миллионов 10. Rating - индекс order desc. Rating изменяется в пределах от 1 до 3000.
Задача - вытащить позицию записи по Id.

Есть запрос
SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC), t.Id FROM table 1) t1 WHERE t1.Id=@id

Но даже при 2млн. записей работает медленно. Можно ли как-то обойтись без ORDER BY? Может я чего-то не понимаю, но она же и так отсортирована. каким способом еще можно вытащить номер записи?
Думал может дополнительное поле создать в табличке "position" но как его обновлять шустро тоже не пойму(

Help!
20 фев 13, 14:44    [13955056]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
hpv
Member

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

Здравствуйте.
Для начала приведите реальный план вашего запроса.
20 фев 13, 15:19    [13955327]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

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

а чем этот не реальный?

SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC), t.Id FROM table 1) t1 WHERE t1.Id=1


результат = 1

SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC), t.Id FROM table 1) t1 WHERE t1.Id=1405832


результат = 2

в дальнейшем допустим произошли изменения
update table set Rating=3000 where Id=89734


SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC), t.Id FROM table 1) t1 WHERE t1.Id=89734


результат = 2
20 фев 13, 15:42    [13955553]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Jeneckiy,
так не на тему оптимизации, но выходит что у записей с одинаковым рейтингом разная позиция И как определяется эта позиция не важно (среди записей с одинаковым рейтингом)?
20 фев 13, 15:51    [13955631]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
Мистер Хенки,

Позиция с одинаковым рейтингом определяется по принципу "чем меньше id - тем ты круче") да, табличку примера заполнил калечно id 1405832 должен быть на третей позиции изначально,а id 2 на второй.
20 фев 13, 15:57    [13955674]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Jeneckiy
Есть запрос
SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC), t.Id FROM table 1) t1 WHERE t1.Id=@id

Не смущает, что этот запрос может на одних и тех же данных возвращать разный результат?
"ROW_NUMBER() OVER (ORDER BY Rating DESC)" — у вас ведь, судя по описанию, тысячи записей с одним и тем же рейтингом — и в пределах этого рейтинга row_number() может вычисляться произвольным образом. Я смоделировал вашу ситуацию, и для одной и той же записи запрос возвращает то 253571, то 253580, то 253828...

Jeneckiy
она же и так отсортирована

Отсортирована, но порядковый номер (row_number) никоим образом нигде не хранится. Поэтому он каждый раз вычисляется.
20 фев 13, 15:57    [13955677]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Jeneckiy
Позиция с одинаковым рейтингом определяется по принципу "чем меньше id - тем ты круче")

То есть запрос выглядит так?
SELECT position FROM (SELECT position = ROW_NUMBER() OVER (ORDER BY Rating DESC, t.Id), t.Id FROM table t) t1 WHERE t1.Id=@id
?
В любом случае, не надо ожидать, что row_number для вашего запроса хранится где-то в индексе.
20 фев 13, 16:02    [13955723]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Jeneckiy
Мистер Хенки,

Позиция с одинаковым рейтингом определяется по принципу "чем меньше id - тем ты круче") да, табличку примера заполнил калечно id 1405832 должен быть на третей позиции изначально,а id 2 на второй.

ну в вашем запросе такого нет если что. иначе было бы position = ROW_NUMBER() OVER (ORDER BY Rating DESC,id asc) . Ну и индекс тогда бы соответствующий строить
20 фев 13, 16:02    [13955727]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
declare @id int 
declare @r int


select @r = rating from table where id = ID
select position = (
(SELECT count(*) from table  where Rating  > @r) + (SELECT count(*) from table  where Rating  = @r and id < @id)
)

+ индекс Rating desc, id asc
20 фев 13, 16:34    [13956003]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
Гость333,

Поле Id является первичным ключом, и отсортировано asc. Поэтому значение позиции всегда одно(отклонение в 1 позицию допускается). Простите не сказал сразу.

Гость333
Отсортирована, но порядковый номер (row_number) никоим образом нигде не хранится. Поэтому он каждый раз вычисляется.
Вот жаль,что не хранится... Как тогда быть? Смотрю в сторону секционирования. Думаю наделать 20 табличек, запрос оставить тот же, но с учетом номера таблицы. Но не уверен,что мне это поможет, тк при изменении рейтинга выходящей за пределы одной таблицы необходимо выполнять 2 DML запроса(delete,insert), причем в разных таблицах..
Есть еще какие варианты?
20 фев 13, 16:35    [13956013]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
count(*)
declare @id int 
declare @r int


select @r = rating from table where id = ID
select position = (
(SELECT count(*) from table  where Rating  > @r) + (SELECT count(*) from table  where Rating  = @r and id < @id)
) + 1

+ индекс Rating desc, id asc
20 фев 13, 16:35    [13956017]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Не совсем понял - а что, запрос
select t1.ID, count(*)
from #t t1
     join #t t2
      on t1.Rating < t2.Rating or (
         t1.Rating = t2.Rating and
         t1.ID  <= t2.ID )
where t1.ID = @ID
group by t1.ID


выдаст не то же самое?

У меня он работает гораздо быстрее сортировки, и выдает детеминированный реультат
20 фев 13, 16:36    [13956018]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
count(*),

Спасибо.

Возвращает не позицию в таблице,а позицию в "секции" с одинаковым рейтингом. Как быть дальше?
20 фев 13, 16:40    [13956057]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
Кот Матроскин,

Быстрее, но все равно достаточно долго(
20 фев 13, 16:46    [13956106]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
iap
Member

Откуда: Москва
Сообщений: 47194
Гость333
Jeneckiy
она же и так отсортирована

Отсортирована.
Это почему же? Ничего там не отсортировано, ибо ORDER BY не наблюдаю
20 фев 13, 16:48    [13956122]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
iap
Member

Откуда: Москва
Сообщений: 47194
Jeneckiy
Кот Матроскин,

Быстрее, но все равно достаточно долго(
А Вам надо неправильно, но зато быстро?
20 фев 13, 16:49    [13956128]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Кот Матроскин, еще быстрее будет с вложенным запросом:)
select t1.ID, 
  (select count(*) 
   from #t t2 
   where t1.Rating < t2.Rating 
    or (t1.Rating = t2.Rating and t1.ID <= t2.ID)) as position
from #t t1
where t1.ID = @ID
20 фев 13, 16:50    [13956133]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
fix
count(*)
count(*)
declare @id int 
declare @r int


select @r = rating from table where id = @ID
select position = (
(SELECT count(*) from table  where Rating  > @r) + (SELECT count(*) from table  where Rating  = @r and id < @id)
) + 1

+ индекс Rating desc, id asc
20 фев 13, 16:50    [13956139]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
Кот Матроскин,

и результат не верный, должна быть позициия 2000000,а результат 1999672
t1.ID <= t2.ID
заменил
t1.ID >= t2.ID - так вернее
20 фев 13, 16:51    [13956145]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Jeneckiy
count(*),

Спасибо.

Возвращает не позицию в таблице,а позицию в "секции" с одинаковым рейтингом. Как быть дальше?

а по-моему
declare @id int 
declare @r int


select @r = rating from table where id = ID
select position = (
(SELECT count(*) from table  where Rating  > @r) + (SELECT count(*) from table  where Rating  = @r and id < @id)
)

возвращает как раз позицию сквозную по рейтингам с преимуществом у записей с меньшим id
20 фев 13, 16:51    [13956153]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Jeneckiy
Member

Откуда:
Сообщений: 39
count(*),

Не верно запрос работает
20 фев 13, 16:53    [13956170]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
Cygapb-007
Кот Матроскин, еще быстрее будет с вложенным запросом:)
select t1.ID, 
  (select count(*) 
   from #t t2 
   where t1.Rating < t2.Rating 
    or (t1.Rating = t2.Rating and t1.ID <= t2.ID)) as position
from #t t1
where t1.ID = @ID

1. а планы не одинаковые разве?
2. не люблю or-ы в seek предикатах
20 фев 13, 16:54    [13956180]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
Кот Матроскин
Member

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

я писал запрос исходя из таблицы-примера, где сортировка rating Desc, ID Desc. Если у Вас на самом деле ID asc - тогда надо менять направление условия, да
20 фев 13, 16:56    [13956196]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
Jeneckiy
count(*),

Не верно запрос работает

тестовые данные и желаемый результат.
а то у вас НИГДЕ не сказано, что же вам нужно получить.
20 фев 13, 16:56    [13956197]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса. Номер записи в таблице.  [new]
count(*)
Guest
Мистер Хенки
Jeneckiy
count(*),

Спасибо.

Возвращает не позицию в таблице,а позицию в "секции" с одинаковым рейтингом. Как быть дальше?

а по-моему
declare @id int 
declare @r int


select @r = rating from table where id = ID
select position = (
(SELECT count(*) from table  where Rating  > @r) + (SELECT count(*) from table  where Rating  = @r and id < @id)
)

возвращает как раз позицию сквозную по рейтингам с преимуществом у записей с меньшим id

id = ID нужно заменить на id = @ID (опечатка)
и +1
я уже привел исправленный пример
20 фев 13, 16:58    [13956218]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить