How fast to glue dependent records in one line.



  • Let's say there are two tables:

    (1) Due

    (2) Personal, which refers to the post.

    One record could account for several staff records.

    The task is to update the [all staff] post in a single line of dependent records from staff.

    Let's say one record is made up of two lines of FIO from the staff, we glue them and record them in the parentage.

    How do you make it quick? BDE Access is used.

    Now I'm using the Interop, opening RecordSet from the post, and I'm going down, and I'm opening the second RecordSet from the request for "Select PID,FIO From [Personal] where PID='t.fields[ID].value and going upstairs and glue, and then I'm gonna go back and update them to the first RecordSet and I think it's too fast.

    Probably ADO. NET would be faster, but I think that if BDE Access is healthy, it's too fat to pay off once. ♪

    UPD:If it's the way to put everything in memory and work through ADO. NET, will I get a lot of speed gain?



  • We can write a VB function that will glue the line. Details can be read:

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

    Body of function:

    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

    Example of request:

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

    There's an interesting way in the same article. Only ms-access sql with an intermediate table. Although it is possible in your case that her role will be played by the Person(s).
    It looks like,

    Метод 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 это непрокатывает" (Митин).

    We need to balance, it looks interesting.

    UPD:The point is, it's about "fucking." ms-access SET will be performed as many times as the records in the attached table (not the one that will adapt). Because of "smart" T-SQLT-SQL This way will not work.

    By the minus of this way, I would add the need for escorts to the new version. access♪ If MS They're going to get a little closer. ms-access to standard ANSI-SQLthat method might stop working.

    On the other hand, maybe they'll do something to conquer the lines: Although that is even the case MS SQL Server I'm still paying attention. MySQLPostgreSQLOracle It's a long time ago.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2