Как быстро склеить зависимые записи в одну строку

#c_sharp #sql #net #ms_access #office_interop

Допустим, есть 2 таблицы:

1)Должность

2)Персонал, которая ссылается на Должность.

На одну запись Должность могут приходится несколько записей из Персонал.

Задача обновить [Весь персонал] в Должность склеенными в одну строчку зависимыми
записями из Персонал.

Допустим приходится на 1 запись приходится 2 строчки ФИО из Персонал, мы их склеиваем
и записываем в родительскую запись Должность.

Как это сделать быстро? Используется БД Access.

Сейчас я использую Interop, открываю RecordSet из Должность, и иду сверху вниз, и
на каждую запись я открываю второй RecordSet из запроса 'Select PID,FIO From [Персонал]
where PID='rst.fields[ID].value и иду сверху вниз и склеиваю значения, а потом возвращаю
и обновляю их в первом RecordSet и мне кажется, что слишком это тормознуто и можно
как то быстрее сделать это.

Наверное, через ADO.NET было бы быстрее, но мне кажется, что если БД Access будет
здоровой, то слишком жирно все в память грузить разом...

UPD:
Если все таки загрузить все в память и работать через ADO.NET, я получу значительный
выигрыш в скорости?
    

Ответы

Ответ 1

Можно написать VB функцию, которая будет склеивать строку.
Подробно можно прочитать тут:

http://hiprog.com/index.php?option=com_content&task=view&id=334&Itemid=35

Тело функции:

Public Function UnionStr1(ID, Fam)
Static IDOld, FamUnion
If IDOld <> ID Then
IDOld = ID
FamUnion = Null
End If

FamUnion = (FamUnion + ", ") & Fam
UnionStr1 = FamUnion
End Function 


Пример запроса:

Select ID, Last(UnionStr1(ID,Fam)) AS FamUnion FROM Tab1
GROUP BY ID;


В той же статье есть интересный способ. Только средствами ms-access sql, с промежуточной
таблицей. Хотя возможно в вашем случае её роль сыграет таблица Person(должностей).
Выглядит примерно так:

Метод 3. Заполнение таблицы при помощи запросов (по Митину).
http://c85.cemi.rssi.ru/Access/AnsPointDetail.idc?QID=14147

Оригинальный и неочевидный метод. 
Выполняется с помощью двух запросов.
1. Запись в Tab2 уникальных ID без фамилий.
Текст запроса WriteID:
INSERT INTO Tab2 (ID) Select DISTINCT ID FROM Tab1;

2. Запись списков фамилий.
Текст запроса TabUnion5:
UPDATE Tab2 INNER JOIN Tab1 On Tab2.ID = Tab1.ID 
Set Tab2.FamUnion = ([Tab2].[FamUnion]+", ") & [Tab1].[Fam];

Оценка скорости: по-видимому, работает быстрее метода 1 за счет отсутствия вызова
специальных функций.
Недостаток: "Однако в T-SQL это непрокатывает" (Митин).


Надо побаловаться, выглядит интересно. 

UPD:
Смысл в том, что из-за "глупости" ms-access SET будет выполнятся столько раз, сколько
записей в присоединённой таблице(не той, которая апдейтится). Из-за "умности" T-SQL,
в T-SQL этот способ работать не будет.

К минусам этого способа я бы ещё добавил потребность в сопровождении при переходе
на новую версию access. Если MS решат приблизить хоть чуть-чуть ms-access к стандарту
ANSI-SQL, этот метод может перестать работать.

С другой стороны, может они и для конкатенации строк что-нибудь сделают:) Хотя в
этом вопросе даже MS SQL Server обделён вниманием до сих пор. MySQL, PostgreSQL, Oracle
это давно умеют.


Ответ 2

Я бы делал так:

Допустим

в таблице персонал(Person) есть три поля: Person_ID, Person_FIO, Position_ID
в таблице должность(Position) есть три поля: Position_ID, Position_Name, Position_StaffList


Для начала пишем запрос для DataReader'a (если я правильно понимаю это C# аналог
Recordset из Basic):

SELECT pos.Position_ID, pos.Position_Name, prs.Person_FIO 
FROM Position AS pos
    LEFT OUTER JOIN Person AS prs ON pos.Position_ID = prs.Position_ID
ORDER BY pos.Position_ID, prs.Person_FIO;


И служебный класс

internal class Pos
{
    public Pos()
    {
        StaffList = new StringBuilder();
    }

    public int? Position_ID {get; set;}
    public string Position_Name {get; set;}
    public StringBuilder StaffList {get; set;}
}


Открываем ридер для нашего запроса. 

List positions = new List();
int previos_pos = -1;

if (reader.HasRows)
{
    int id;
    string position_name;
    string fio;
    Pos current;

    while (reader.Read())
    {
        id = reader.GetInt32(0);
        position_name = reader.GetString(1);
        fio = reader.GetString(2);   

        if (id != previos_pos)
        {
            current = new Pos();
            positions.Add(current);
            current.Position_ID = id;
            current.Position_Name = position_name;
            current.StaffList.Append(fio); 
        } 
        else
        {
            current.StaffList.AppendFormat("{0},{1}"
               , current.StaffList,
               , fio
               ); 
        }
    }
}


Закрываем наш ридер. Теперь у нас есть заполненный список positions и остается только
обновить из него вашу таблицу позиции. Так как мы не ддосим базу промежуточными запросами
(что может быть действительно долго если позиций много), то мы должны получить некоторое
ускорение.


Ответ 3

Как это сделать быстро? Используется БД Access.


Возможно ужасно знаю способности MS Access... но предполагаю, что сделать эту задачу
средствами БД Access невозможно, т.к. там слабый потенциал аналитических функций и
расширений SQL запросов.

В более способных СУБД в которых язык SQL расширяют доп.аналитическими функциями,
есть способы выполнить такую задачу, но даже они выглядят ужасно и криво - например
в Oracle с помощью компоновки аналитических функций в нескольких подзапросах.
Поэтому для вас самый идеальный вариант тот, что вы уже делаете - сформировать запрос
должностей, переходом по должностям, выполнять второй запрос для получения всех ФИО
из этой должности. Обрабатывать полученные списки.

Предложенный вами вариант действий:


  Допустим приходится на 1 запись приходится 2 строчки ФИО из Персонал,
  мы их склеиваем и записываем в родительскую запись Дожность



это ужасное и грубое нарушение структуры данных и логики (о нормальных формах БД
надо почитать). Делается вывод - у вас скорее всего неверная постановка задачи, либо
неверное принятие решения для ее выполнения.


PS: поясняю.. если вы все ФИО принадлежащие для 1 должности засунете в поле этой
должности, то выполняя запрос к этой должности и выводя информацию на клиенте - вы
ничем не сэкономите ОЗУ, т.к. действует математическое правило "от перемены мест слагаемых
сумма не меняется". Не важно получите вы эти ФИО и будете хранить в разных полях, либо
в одном поле, количество байт символов не изменится, во всяком случае разница будет
не заметна для ПК. Но зато когда вы будете получать список известных должностей - то
объемы передаваемой информации будут расти очень быстро, что увеличит нагрузку на трафик
и БД.

Лучше сформулируйте задачу поставленную перед вами, чтобы вам смогли подсказать оптимальное
решение, т.к. в данном вопросе вы просите помочь вам выполнить решение которое приняли
самостоятельно - но оно ошибочное.