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

Откуда:
Сообщений: 18
Имеется БД типа социальной сети, в которой два мембера могут быть друзьями. Ясно что если мембер 1 друг мемберу 2, то и наоборот.
Щас храниться это очевидным образом:

table Friends:
member1
member2

Но запрос на "проверить дружбу" приходится делать сложный -
SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2 OR
member1 = @member2 AND member2 = @member1

Тут кластерный индекс не используешь, работает долго и запрос некрасивый.

Есть идея хранить по ДВЕ записи на каждую дружбу - то есть вместо одной записи
(member1, member2)
хранить две -
(member1, member2)
(member2, member1)
Тогда поиск упростится

SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2

Вопрос к знатокам - будет ли новый способ быстрее, если в имеющейся БД 20 миллионов записей? Имеется индекс сложный некластерный индекс по (member1, member2)
17 авг 12, 10:10    [13024707]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2
UNION
SELECT * FROM Friends
WHERE member1 = @member2 AND member2 = @member1;
Все индексы могут работать, если они есть
17 авг 12, 10:19    [13024763]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
сорри - 600 тысяч записей а не 20 млн
17 авг 12, 10:20    [13024765]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
iap
SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2
UNION
SELECT * FROM Friends
WHERE member1 = @member2 AND member2 = @member1;
Все индексы могут работать, если они есть
Хотя, UNION тут не нужен:
SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2
UNION ALL
SELECT * FROM Friends
WHERE member1 = @member2 AND member2 = @member1;
17 авг 12, 10:21    [13024771]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
iap, согласен сортировка здесь не нужна, но неужели это стандартное решение? Мне казалось что при современном железе СКОРОСТЬ доступа важнее ОБЬЕМА данных, нет?
17 авг 12, 10:48    [13024942]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
aleks2
Guest
oleg2163

Вопрос к знатокам - будет ли новый способ быстрее, если в имеющейся БД 20 миллионов записей? Имеется индекс сложный некластерный индекс по (member1, member2)


Хранить упорядоченные пары: member1 < member2
и будет тибе щастье.
17 авг 12, 10:54    [13024999]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
нет, aleks2, нет тут щастья, я тоже сперва думал так. Попробуй напиши запрос - сам увидишь
17 авг 12, 11:06    [13025113]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34619
oleg2163
Но запрос на "проверить дружбу" приходится делать сложный -
SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2 OR
member1 = @member2 AND member2 = @member1

Тут кластерный индекс не используешь, работает долго и запрос некрасивый.


Ничего особо сложного нет, а что работает долго -- странно, что MSSQL не понимает OR-strategy?
Индексы использовать можно и нужно, должны быть по
( member1,member2 ) в любом порядке.

oleg2163
Есть идея хранить по ДВЕ записи на каждую дружбу - то есть вместо одной записи
(member1, member2)
хранить две -
(member1, member2)
(member2, member1)
Тогда поиск упростится

SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2

Вопрос к знатокам - будет ли новый способ быстрее, если в имеющейся БД 20 миллионов записей? Имеется индекс сложный некластерный индекс по (member1, member2)


Ну, идея конечно хорошая, здравая. На худой конец так можно и поступить.

но очень странно, что запрос с OR
не работает с подачи как надо.
Посмотри план, проupdate-ть статистику по этому индексу, может поможет.

В принципе это

SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2 OR
member1 = @member2 AND member2 = @member1


должно преобразовываться в

SELECT * FROM Friends
WHERE member1 = @member1 AND member2 = @member2 
UNION 
SELECT * FROM Friends
WHERE member1 = @member2 AND member2 = @member1


Если это не происходит, ты можешь сделать это сам, руками
(UNION лучше заменить на UNION ALL, если это можно, ты должен знать лучше).

Ещё может быть проблема с переменными, тогда можно ещё попробовать написать хинты, форсануть индекс.
17 авг 12, 11:11    [13025143]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34619
oleg2163
нет, aleks2, нет тут щастья, я тоже сперва думал так. Попробуй напиши запрос - сам увидишь


если ты будеш хранить всегда пары в таблице по такому правилу,
и во все запросы будешь подставлять параметры по этому принципу, то счастье БУДЕТ,
другое дело, что это конечно не очень удобно, потому что делать это видимо надо на
клиенте либо на среднем слое, и надо об этом всегда помнить, что не очень удобно.
17 авг 12, 11:14    [13025160]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
MasterZiv
если ты во все запросы будешь подставлять параметры по этому принципу, то счастье БУДЕТ

вот именно, а нахрена мне такая щастливая жизнь, каждый раз при вызове проверять кто старше. Кроме того считаю неверно менять presentation layer для того чтоб облегчить жизнь database layer.
Кроме того - запрос на получение ВСЕХ моих друзей выглядит точно так же некрасиво как и исходный код, а большинство хранимок ищут именно это.
17 авг 12, 11:29    [13025251]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
MasterZiv
другое дело, что это конечно не очень удобно, потому что делать это видимо надо на
клиенте либо на среднем слое, и надо об этом всегда помнить, что не очень удобно.
oleg2163
вот именно, а нахрена мне такая щастливая жизнь, каждый раз при вызове проверять кто старше.

Я просто фигею.
MasterZiv
Кроме того считаю неверно менять presentation layer для того чтоб облегчить жизнь database layer.
Лайеризация ГМ. Фетишизм, модинг и самолётопоклоничество. Куда катится мир.
Какой-то дядя сказал "низя" и толпа ринулась.
MasterZiv
Кроме того - запрос на получение ВСЕХ моих друзей выглядит точно так же некрасиво как и исходный код, а большинство хранимок ищут именно это.
Песец. Красотулечки ему нужны.
Лять, а как же функции? Если вы такие вумные по применению паттернов то чё вы обходите мимо Proxy?!

Если вы юзаете RDBS как тупое хранилище, то при чём тут MS SQL, храните в файлах. И как там сейчас модно - в NoSQL.
Как раз по ним вы нарушаете их принципы. Нуна было хранить тупой список друзей у каждого юзверя отдельно.
17 авг 12, 11:53    [13025462]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
MasterZiv
oleg2163
Есть идея хранить по ДВЕ записи на каждую дружбу - то есть вместо одной записи
(member1, member2)
хранить две -
(member1, member2)
(member2, member1)

Ну, идея конечно хорошая, здравая. На худой конец так можно и поступить.

отчего же "на худой" то?
если у мембера1 есть друзья и у мембера2 есть друзья то вовсе не факт, что есть транзитивность этих множеств при дружбе мембер1-мембер2 тогда хранение в виде
memberfriends
member1member2
member1member3
member1member4
member2member1
member2member4
member2member5
17 авг 12, 11:53    [13025463]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34619
отчего же "на худой" то?
если у мембера1 есть друзья и у мембера2 есть друзья то вовсе не факт, что есть транзитивность этих множеств при дружбе мембер1-мембер2 тогда хранение в виде

Это вопрос постановки задачи, и это автор топика описал в начальном посте.
17 авг 12, 12:03    [13025537]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34619
Mnior
MasterZiv
другое дело, что это конечно не очень удобно, потому что делать это видимо надо на
клиенте либо на среднем слое, и надо об этом всегда помнить, что не очень удобно.
oleg2163
вот именно, а нахрена мне такая щастливая жизнь, каждый раз при вызове проверять кто старше.

Я просто фигею.
MasterZiv
Кроме того считаю неверно менять presentation layer для того чтоб облегчить жизнь database layer.
Лайеризация ГМ. Фетишизм, модинг и самолётопоклоничество. Куда катится мир.
Какой-то дядя сказал "низя" и толпа ринулась.
MasterZiv
Кроме того - запрос на получение ВСЕХ моих друзей выглядит точно так же некрасиво как и исходный код, а большинство хранимок ищут именно это.
Песец. Красотулечки ему нужны.
Лять, а как же функции? Если вы такие вумные по применению паттернов то чё вы обходите мимо Proxy?!

Если вы юзаете RDBS как тупое хранилище, то при чём тут MS SQL, храните в файлах. И как там сейчас модно - в NoSQL.
Как раз по ним вы нарушаете их принципы. Нуна было хранить тупой список друзей у каждого юзверя отдельно.



Это всё что в цитатах не я писал. Топикстартер.
17 авг 12, 12:05    [13025551]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34619
oleg2163
MasterZiv
если ты во все запросы будешь подставлять параметры по этому принципу, то счастье БУДЕТ

вот именно, а нахрена мне такая щастливая жизнь, каждый раз при вызове проверять кто старше. Кроме того считаю неверно менять presentation layer для того чтоб облегчить жизнь database layer.
Кроме того - запрос на получение ВСЕХ моих друзей выглядит точно так же некрасиво как и исходный код, а большинство хранимок ищут именно это.


Ну тогда добавляй по две записи, в чём проблема-то?
17 авг 12, 12:06    [13025566]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
А вообще не понимаю первоначальную задачу,
зачем проверять существование пары?
Для какой задачи?
Мне кажется её чрезмерно разбили.
17 авг 12, 12:11    [13025615]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
CREATE TABLE [dbo].[Relation] (
	 [Left]		Int
	 CONSTRAINT [FK_Relation_Left]	REFERENCES [dbo].[Person] ([ID]) ON DELETE CASCADE
	,[Right]	Int
	 CONSTRAINT [FK_Relation_Right]	REFERENCES [dbo].[Person] ([ID]) ON DELETE CASCADE
	,CONSTRAINT [PK_Relation]	PRIMARY KEY (
		 [Left]
		,[Right]
	)
	,CONSTRAINT [CK_Relation]	CHECK ([Left] < [Right])
)
GO
-- CREATE PROCEDURE
/*
INSERT dbo.Reletion	([Left],[Right])
VALUES			(@Left,@Right)
*/
INSERT dbo.Reletion	([Left],[Right])
VALUES			(IIF(@Left < @Right,@Left,@Right)
			,IIF(@Left > @Right,@Left,@Right))
GO
CREATE VIEW [dbo].[vwRelation] AS
	SELECT	[Left],[Right]
	FROM	dbo.Relation
UNION ALL
	SELECT	[Right],[Left]
	FROM	dbo.Relation
GO
CREATE FUNCTION [dbo].[fnRelationship] (
	 @Left	Int
	,@Right	Int
) RETURN TABLE AS RETURN
/*
SELECT	[Left],[Right]
FROM	dbo.Relation
WHERE	    [Left] = IIF(@Left < @Right,@Left,@Right)
	AND [Right]= IIF(@Left > @Right,@Left,@Right)
*/
SELECT	[Left],[Right]
FROM	dbo.vwRelation
WHERE	    [Left] = @Left
	AND [Right]= @Right
GO
Банальное VIEW решает все вопросы.
Но принцип и указанные CHECK даёт отсечение куска плана.
17 авг 12, 12:23    [13025738]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
MasterZiv
Ну тогда добавляй по две записи, в чём проблема-то?


ОК, еще раз постановка задачи. Есть проблемы с производительностью БД, надо улучшить.
Если добавить по 2 записи, увеличиться ОБЬЕМ БД, что скажется на том что в кеше придется хранить ИЗЛИШНИЕ данные, что (видимо) замедлит работу, но зато сам запрос будет работать (вроде бы) быстрее - так как только одна проверка вместо двух.

Где это нужно - например хранимка которая показывает друзей моих друзей или "стена типа фейсбука" - события которые произошли с моими друзьями. То есть идет сперва поиск всех друзей мембера (ов), а потом join на другие таблицы
17 авг 12, 12:28    [13025790]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
oleg2163
Member

Откуда:
Сообщений: 18
Mnior
...

Да, идея с вьюхой интересная, усложнение кода только на вставке, код выборки весьма прост. Да, надо подумать, сенкс...
17 авг 12, 12:31    [13025813]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
oleg2163,

попробуйте так

declare @tmp table(id int)
insert into @tmp values (@member1)
insert into @tmp values (@member2)
SELECT * FROM Friends a
INNER JOIN @tmp b on a.member1=b.id
INNER JOIN @tmp c ON a.member2=c.id

будет поиск по PK (member1, member2)
17 авг 12, 13:28    [13026333]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Matroskin, FacePalm.JPG

Mnior
Но принцип и указанные CHECK даёт отсечение куска плана.
К сожалению не даёт в данном случае, даже с ReCompile. Так что использовать ([Left] < [Right]) не имеет смысла вообще, т.е. вставлять как есть (нашару) и нормально воспринимать что в плане всегда будет Contcatenation.
Если юзать dbo.fnRelationship с функциями (IIF / CASE), то срежется сравнение статистик полей, а точнее линейность отношений. Что гипотетически может иногда путать оптимизатор. Но попробовать стоит.
В остальных же запросах юзать вьюху.

oleg2163
Есть проблемы с производительностью БД, надо улучшить.
Где конкретно. Планы, запросы и базовые задачи укажите. Может дело не в ней.
oleg2163
... - так как только одна проверка вместо двух.
Неужели в этом весь сыр бор? Не верю. (с)
17 авг 12, 16:27    [13027857]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
ОБЬЕМ БД
Guest
[quot oleg2163]
MasterZiv
Если добавить по 2 записи, увеличиться ОБЬЕМ БД

объем БД все равно увеличиться, либо явным добавление записей, либо появлением нового, "обратного", индекса.

ps я за добавление доб записей.
17 авг 12, 17:03    [13028131]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
ОБЬЕМ БД
Guest
съехало квотирование :(
oleg2163
Если добавить по 2 записи, увеличиться ОБЬЕМ БД

объем БД все равно увеличиться, либо явным добавление записей, либо появлением нового, "обратного", индекса.

ps я за добавление доп записей.
17 авг 12, 17:06    [13028160]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
ОБЬЕМ БД
объем БД все равно увеличиться, либо явным добавление записей, либо появлением нового, "обратного", индекса.
ps я за добавление доп записей.
Чёрд. Шаблоны сознания. Ваше утверждение почти прокатило

Но, всё-таки есть неприятные моменты.
Целостность.
Если в данной задаче отношения двунаправленные, то могут быть неприятности в виде потерянных строк.
А с другой стороны FK второй колонки без индекса не кошерно. Удалить вторые строки не так удобно (CASCADE вообще не поставишь).
В данной задаче (при данных требованиях) можно на это плюнуть, но в общем не стоит.
DELETE	F
FROM	     DELETED	D
	JOIN dbo.Friend F ON F.Member = D.Friend
oleg2163, триггер можно не только на удаление, ну и на вставку, тогда вообще всё прозрачно было бы.

Другие запросы.
Есть запросы, аля найти пары друзей у которых ...
Тогда надо ставить там условия Member < Friend, но от излишнего сканирования это не спасает, (а ставить фильтрованный индекс - убивает всю затею на корню).
А это уже критично и неприемлемо, чем какая-то "сложность" IIF при вставке данных и в dbo.fnRelationship
17 авг 12, 23:11    [13029469]     Ответить | Цитировать Сообщить модератору
 Re: для профи - как хранить ПАРУ полей (неупорядоченных)  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
[quot Mnior]Matroskin, FacePalm.JPG

Давайте с разъяснениями!
Глядишь и научусь чему-нибудь.
20 авг 12, 09:04    [13035020]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить