Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Лев Толстой
Guest
Интересное кино: BOL утверждает, что:
BOL
There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause.

/* прочёл тут: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm */
То есть, при попытке упорядочить, скажем, строки длинной свыше 8К результат будет непредсказуем (вроде бы...)

Однако, order by оказался способным правильно упорядочить строки гораздо бОльшей длины.
Вот скрипт, заполняющий таблицу строками длиной почти 400 тыс символов.
Строки отличаются только в средней части, см. в выборке поле 'SUF':
IF (SELECT OBJECT_ID('TEMPDB..##T')) IS NOT NULL
   DROP TABLE ##T
CREATE TABLE ##T(ID INT IDENTITY, LONGTEXT VARCHAR(MAX))
INSERT INTO ##T
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'DEF'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'ABC'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'BCD'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'AAF'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'DEE'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069)
INSERT INTO ##T
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'BGD'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'AAA'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'ABA'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'BAA'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069) UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(MAX)), 23471)+'AUA'+REPLICATE(CAST('9876543210' AS VARCHAR(MAX)), 15069)

;SELECT ID,SUF=SUBSTRING(LONGTEXT,234711,3),LONGTEXT FROM ##T
ORDER BY LONGTEXT

Итог выборки (без самих полумиллионных строк, разумеется :-))
IDSUF
7AAA
4AAF
8ABA
2ABC
10AUA
9BAA
3BCD
6BGD
5DEE
1DEF


Сортировка в итоге выполняется правильно, даже на таких строках-монстрах.
Как сиё объяснить ? может, ORDER BY всё-таки юзает совсем не row size= 8060 байт в своих "промежуточных рабочих таблицах", а гораздо больше ?
21 ноя 09, 22:35    [7960307]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Anar
Member

Откуда:
Сообщений: 10
Лев Толстой,

Достоверно не знаю, но есть такое соображение:

Возможно дело в том, что для упорядочивания строк не нужно читать их полностью.

Если бы у строк первые 8к символов совпадали, а различия шли бы после, то наверное могли бы быть проблемы.
24 ноя 09, 11:43    [7969011]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Anar
Member

Откуда:
Сообщений: 10
P.S Попробуйте добавлять различия в конец
24 ноя 09, 11:48    [7969053]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Anar
Лев Толстой,

Достоверно не знаю, но есть такое соображение:

Возможно дело в том, что для упорядочивания строк не нужно читать их полностью.

Если бы у строк первые 8к символов совпадали, а различия шли бы после, то наверное могли бы быть проблемы.
А разве в примере Льва Толстого не так?
24 ноя 09, 11:49    [7969078]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Лев Толстой
Guest
Anar,

пробовал, разумеется. Результат тот же: order by выполняет сортировку правильно.
24 ноя 09, 11:50    [7969087]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Я думаю, что Лев толстой понимает row size не так, как понимает его сервер.
Строка таблицы - это не то же самое, что текстовое поле типа VARCHAR(MAX). IMHO
Если не ошибаюсь для сверхдлинных типов в строке хранится указатель, а не сам текст.
Поэтому предел 8060 байт не достигается.

Может, я, конечно, ошибаюсь...
24 ноя 09, 12:00    [7969206]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
IF (SELECT OBJECT_ID('TEMPDB..##T')) IS NOT NULL
   DROP TABLE ##T
CREATE TABLE ##T(ID INT IDENTITY, s1 varchar(5000), s2 varchar(5000), s3 varchar(5000))
INSERT INTO ##T (s1, s2, s3)
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'DEF' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'ABC' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'BCD' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'AAF' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'DEE'
INSERT INTO ##T (s1, s2, s3)
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'BGD' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'AAA' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'ABA' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'BAA' UNION ALL
SELECT REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), REPLICATE(CAST('1234567890' AS VARCHAR(5000)), 500), 'AUA'

;SELECT ID, s3 FROM ##T
ORDER BY s1+ s2+ s3 desc
опс...

для спящего время бодрствования равносильно сну
24 ноя 09, 12:36    [7969600]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Автор: Алексей2003
> опс...

не. это как раз не опс. здесь просто строка обрезается при конкатенации.
опс будет так:
SELECT ID,SUF=SUBSTRING(LONGTEXT,234711,3),LONGTEXT FROM ##T
ORDER BY cast(LONGTEXT as char(8000)), ID
а в BOL, думается, просто несколько упростили ситуацию, чтоб в излишние
подробности не вдаваться. сервер вполне может сравнивать значения
varchar(max) даже если они длиннее 8000 символов, так почему бы
не уметь их сортировать. с хранением таких значений тоже проблем нет.

но ограничение на размер страницы никуда ведь не делось - отсюда
и оговорка.

Posted via ActualForum NNTP Server 1.4

24 ноя 09, 13:02    [7969908]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
Лев Толстой
Guest
iap
для сверхдлинных типов в строке хранится указатель, а не сам текст.
Поэтому предел 8060 байт не достигается.
так что получается, он всё-таки при сортировке может оперировать строками любой длины, да ?
24 ноя 09, 14:13    [7970594]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка сверхдлинных строк (реальные возможности order by)  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> так что получается, он всё-таки при сортировке может оперировать
> строками любой длины, да ?

есть ограничение на длину _строки таблицы_.
которое в некоторых случаях, впрочем, обходится - в случаях со значениями
больших типов (varchar(max)/nvarchar(max)/varbinary(max)) или Row-Overflow Data.
а проблем с собственно сортировкой, как таковой, нет.

Posted via ActualForum NNTP Server 1.4

24 ноя 09, 14:24    [7970684]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить