none
Выбрать из таблицы собеседников RRS feed

  • Вопрос

  • create table PrivateMessage
     (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SenderAccountId INT NOT NULL DEFAULT 1,
    AcceptorAccountId int NOT NULL DEFAULT 1
    );create table Account
    (
     Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     Nick NVARCHAR(25) NOT NULL UNIQUE DEFAULT N'Пользователь'
     );
    CREATE PROCEDURE GetPrivateMessagesAuthors(@AccountId int=1)
    AS 
    BEGIN	
    	select distinct Account.Id,Nick from Account 
    	inner join PrivateMessage on SenderAccountId!=AcceptorAccountId
    	where (Account.Id!=@AccountId)and
    	(SenderAccountId=@AccountId or AcceptorAccountId=@AccountId);
    end

    Имеются 2 таблицы. В таблице Account представлены Id аккаунта, которые могут присутствовать в SenderAccountId или AcceptorAccountId, и ник пользователя с таким Id. В таблице PrivateMessage представлены Id сообщения, Id аккаунта автора сообщения - SenderAccountId - и Id аккаунта получателя сообщения - AcceptorAccountId. На вход процедуры поступает Id аккаунта пользователя, для которого нужно получить Id аккаунтов и Nick пользователей, которые хотя бы раз писали сообщение этому пользователю или получали сообщение от него.  Процедура, которую я составил и привёл выше, выводит даже те Id, которые не переписывались с поданным Id на вход процедуры.
    4 марта 2015 г. 10:32

Ответы

  • Я написал правильный запрос:

    (select distinct Account.Id
     
    ,Nick from Account
    inner join PrivateMessage on Account.Id
     
    =SenderAccountId
    where AcceptorAccountId=@AccountId)union
    (select distinct Account.Id
     
    ,Nick from Account
    inner join PrivateMessage on Account.Id
     
    =AcceptorAccountId
    where SenderAccountId=@AccountId);

    Возможно ли переписать его с join вместо union для лучшего быстродействия?

    • Помечено в качестве ответа Энтомолог 17 марта 2015 г. 12:23
    4 марта 2015 г. 14:14

Все ответы

  • select a.Id, a.Nick
    from Account a
    where a.Id <> @AccountId
    and exists
    (
    	select *
    	from PrivateMessage m
    	where a.Id in ( m.AcceptorAccountId, m.SenderAccountId)
    )
    

    4 марта 2015 г. 11:49
  • Я написал правильный запрос:

    (select distinct Account.Id
     
    ,Nick from Account
    inner join PrivateMessage on Account.Id
     
    =SenderAccountId
    where AcceptorAccountId=@AccountId)union
    (select distinct Account.Id
     
    ,Nick from Account
    inner join PrivateMessage on Account.Id
     
    =AcceptorAccountId
    where SenderAccountId=@AccountId);

    Возможно ли переписать его с join вместо union для лучшего быстродействия?

    • Помечено в качестве ответа Энтомолог 17 марта 2015 г. 12:23
    4 марта 2015 г. 14:14
  • Возможно. Чем вас не устраивает тот вариант, который я привёл раньше? Он возвращает что-то не то? 

    В вашем варианте есть одна основная проблема, свойственная всем начинающим sql разработчикам - лишние агрегации в виде distinct и union. Вместо того, чтобы пользоваться специализированными конструкциями типа exists, вы пытаетесь свести всё к джойнам и сталкиваетесь с размножением строк, которое затыкаете группировкой.

    Группировка - это почти всегда дорого.
    Группировка по PK и каким-либо иным полям той же таблицы - вернейший признак того, что всё сделано не так.
    Подобный код взрывает мозг оптимизатору и обычно гробит производительность на сколько-нибудь серьёзных объёмах данных.

    В определённых ситуациях мой вариант тоже может начать тормозить и тогда его может потребоваться переписать на менее компактный вариант:

    select a.Id, a.Nick
    from Account a
    where a.Id <> @AccountId
    and 
    (
      exists
      (
    	select *
    	from PrivateMessage m
    	where a.Id = m.AcceptorAccountId
      )
      or
      exists
      (
    	select *
    	from PrivateMessage m
    	where a.Id = m.SenderAccountId
      )
    )


    4 марта 2015 г. 15:15
  • Я заполняю для тестирования таблицы так:

    create table Account
    (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Nick NVARCHAR(25) NOT NULL UNIQUE DEFAULT N'Пользователь'
    );
    insert into Account values (N'Тест1'),
    (N'Тест2'),
    (N'Тест3');
    create table PrivateMessage
    (
    	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    	SenderAccountId INT NOT NULL DEFAULT 1,
    	AcceptorAccountId int NOT NULL DEFAULT 1,
    	PrivateText NVARCHAR(1000) NOT NULL DEFAULT N'Сообщение'	
    );
    CREATE PROCEDURE GetPrivateMessagesAuthors(@AccountId int=1)
    AS 
    BEGIN	
    	(select distinct Account.Id,Nick from Account 
    	inner join PrivateMessage on Account.Id=SenderAccountId
    	where AcceptorAccountId=@AccountId)union
    	(select distinct Account.Id,Nick from Account 
    	inner join PrivateMessage on Account.Id=AcceptorAccountId
    	where SenderAccountId=@AccountId);
    	set nocount on;
    end
    go
    insert into PrivateMessage values (1,2,'adfsadf'),
    (2,1,'rtrewet'),
    (2,3,'fgfdsg'),
    (1,2,'trwertewr');

    Согласно последнему insert, при вводе @AccountId = 1 получаем его собеседников(которым он писал хотя бы раз или которые ему написали хотя бы раз) - 2. При вводе 2 получаем 1,3. При вводе 3 получаем 2.

    4 марта 2015 г. 18:50