Получение уникальных значений поля списка

Сегодня пост об использовании не совсем правильного подхода при решении очень часто встречающейся проблемы при разработке решений на базе SharePoint: получение набора уникальных значений поля списка. Описанный мною метод относится к спискам/библиотекам, содержащим более 5000 элементов.

Принцип решения

При получении данных из списка SharePoint есть одно досадное ограничение: он не поддерживает получение уникальных значений, т.к. в любом случае выбирает такие уникальные поля как ID, UniqueId и прочие. Поэтому для решения описанной выше задачи приходится обходить объектную модель SharePoint и выбирать данные напрямую из базы данных содержимого.

База данных содержимого

Все данные списков/библиотек документов в SharePoint хранятся в одной единственной таблице dbo.AllUserData базы данных содержимого. Для начала, вот так выглядит в SharePoint иерархия содержимого сверху-вниз:

Иерархия содержимого SharePoint

У приложения (SPWebApplication) может быть одна или более база содержимого. И при этом у коллекции сайтов (SPSite) и всех нижестоящих элементов иерархии база содержимого одна. Но мы будем "подниматься" по этой иерархии снизу и выбирать нам не придется.

  1. // Путь к строке подключения от поля списка
  2. SPFieldObject.ParentList.ParentWeb.Site.ContentDatabase.DatabaseConnectionString

SchemaXml

Фильтровать данные в таблице dbo.AlllUserData мы будем по следующим полям

  • tp_ListId - Id списка, из которого мы будем выбирать данные;
  • tp_IsCurrent - флаг, указывающий на текущую версию;
  • tp_RowOrdinal - номер строки. Он используется если полей в таблице не хватает для хранения значений полей. По умолчанию, максимальное кол-во строк, для одного элемента списка/библиотеки документов равно 6;
  • tp_DeleteTransactionId - Id транзакции удаления. Если элемент не удален, то значение этого поля будет равно 0x

И последний параметр - само поле, где хранится значение. Все эти параметры мы будем брать из схемы поля, представленного свойством SchemaXml:

  1. <Field ID="{0eb872f2-94a8-455b-9c0f-529dd0688a41}" Name="AccessLevel"
  2.     DisplayName="AccessLevel" Type="Integer"
  3.     ColName="int4"
  4.     StaticName="AccessLevel"
  5.     SourceID="{7D206E84-E96D-4D2D-A503-88E4702D7409}"
  6.     Indexed="TRUE" Version="1" RowOrdinal="0">
  7. </Field>

И SQL-запрос будет выглядеть примерно так:

  1. select top 10
  2.     int4
  3. from
  4.     dbo.AllUserData (nolock)
  5. where
  6.     tp_ListId = '7D206E84-E96D-4D2D-A503-88E4702D7409'
  7.     and 
  8.     tp_IsCurrent = 1
  9.     and                    
  10.     tp_RowOrdinal = 0
  11.     and
  12.     tp_DeleteTransactionId = 0x
  13. order by
  14.     1 asc

Решение

Решение будет представлять из себя метод-расширитель для объектов типа SPField. Для начала "болванка" для построение SQL-запроса:

  1. public static string FieldDistinctValues =
  2.         @"select distinct
  3.             %SqlColName%
  4.         from
  5.             dbo.AllUserData
  6.         where
  7.             tp_ListId = @ListId
  8.             and tp_IsCurrent = 1
  9.             and
  10.             tp_RowOrdinal = @RowOrdinal
  11.             and
  12.             tp_DeleteTransactionId = 0x
  13.         order by
  14.             1 asc";

Здесь %SqlColName% мы будем подменять названием колонки из атрибута ColName. Остальные @Параметры будем подставлять, используя стандартный функционал:

  1. /// <summary>
  2. /// Получение уникальных значений поля списка
  3. /// </summary>
  4. /// <typeparam name="TValue">Тип поля</typeparam>
  5. /// <param name="field">Поле списка</param>
  6. /// <returns></returns>
  7. public static IEnumerable<TValue> DistinctValues<TValue>(this SPField field)
  8. {
  9.     var connectionString = string.Empty;
  10.     var res = new List<TValue>();
  11.     SPSecurity.RunWithElevatedPrivileges(
  12.         () =>
  13.         {
  14.             //Получаем ID сайта
  15.             var siteId = field.ParentList.ParentWeb.Site.ID;
  16.             // Инициализируем новый сайт с правами учетной записи пула приложения
  17.             using (var site = new SPSite(siteId))
  18.             {
  19.                 connectionString = site.ContentDatabase.DatabaseConnectionString;
  20.             }
  21.             // Получаем значение атрибута ColName
  22.             var colName = field.AttributeValue("ColName");
  23.             var ordinal = field.AttributeValueAsInteger("RowOrdinal");
  24.             var listId = field.ParentList.ID;
  25.             // Создаем комманду
  26.             using (var cmd = new SqlCommand(
  27.                     FieldDistinctValues.Replace("%SqlColName%", colName))
  28.                     {
  29.                         CommandType = CommandType.Text
  30.                     })
  31.             {
  32.                 cmd.Parameters.Add(new SqlParameter("@ListId", listId));
  33.                 cmd.Parameters.Add(new SqlParameter("@RowOrdinal", ordinal));
  34.                 // Инициализируем подключение к базе данных
  35.                 using (var con = new SqlConnection(connectionString))
  36.                 {
  37.                     cmd.Connection = con;
  38.                     con.Open();
  39.                     var reader = cmd.ExecuteReader();
  40.                     while (reader != null && reader.Read())
  41.                     {
  42.                         // Заполняем полученные значения
  43.                         res.Add(reader[0] is TValue 
  44.                             ? (TValue) reader[0] 
  45.                             : default(TValue));
  46.                     }
  47.                 }
  48.                 if (cmd.Connection.State != ConnectionState.Closed)
  49.                 {
  50.                     cmd.Connection.Close();
  51.                 }
  52.             }
  53.         });
  54.     return res;
  55. }

Выбирать данные из базы данных, минуя объектную модель как минимум некрасиво, т.к. её структура может измениться, но во-первых эта структура (таблица dbo.AllUserData) остается со времен Windows SharePoint Service 2.0, а во-вторых, получаемый прирост производительности оправдывает такое решение.

Производительность

Производительность я измерял, используя список с 10.000 элементами и производил выборку уникальных значений трех полей. Самих уникальных значений в этих полях было 2, 5 и 2045. Каждый запрос я выполнял десять раз и измерял среднее время исполнения. Вот, что у меня получилось:

Сравнение производительности прямого SQL-запроса и объектной модели

Теперь я добавил ещё один момент к выборке данных: подсчет количества элементов в списке. В случае с SQL-запросом надо просто добавить group by. А в случае с объектной моделью группировку придется выполнять в памяти.

Сравнение производительности прямого SQL-запроса и объектной модели при использовании группировки

Результат мало чем отличается от предыдущего. Разница в производительности здесь объясняется очень просто: использую объектную модель SharePoint все равно приходится выбирать все данные из списка. В случае, когда уникальных значений в списке всего два, а самих элементов 10000, придется "вытянуть" 10000 значений из списка SharePoint и только потом в памяти искать уникальные значения.

Можно еще усложнить запрос, добавив к нему предикат для предварительной фильтрации данных, например для реализации быстрого автокомплита в поле.

As is

В заключение еще раз напомню, что выбирать данные напрямую из базы данных, миную объектную модель - это плохо и очень плохо. Поэтому используйте описанный выше метод на свой страх и риск.


Поделиться

Коментарии