Я вижу две принципиальные возможности фильтрации данных:
-
На стороне C#
-
На стороне БД
Фильтрация на стороне C#
В первом случае если Вы используете DataAdapter, то простое фильтрование можно организовать c помощью BindingSource:
пример из MSDN
private void InitializeSortedFilteredBindingSource()
{
// Create the connection string, data adapter and data table.
SqlConnection connectionString =
new SqlConnection("Initial Catalog=Northwind;" +
"Data Source=localhost;Integrated Security=SSPI;");
SqlDataAdapter customersTableAdapter =
new SqlDataAdapter("Select * from Customers", connectionString);
DataTable customerTable = new DataTable();
// Fill the the adapter with the contents of the customer table.
customersTableAdapter.Fill(customerTable);
// Set data source for BindingSource1.
BindingSource1.DataSource = customerTable;
// Filter the items to show contacts who are owners.
BindingSource1.Filter = "ContactTitle='Owner'";
// Sort the items on the company name in descending order.
BindingSource1.Sort = "Country DESC, Address ASC";
// Set the data source for dataGridView1 to BindingSource1.
dataGridView1.DataSource = BindingSource1;
}
Для отмены фильтра достаточно вызвать RemoveFilter () этот метод устанавливает значение свойства Filter в null.
Но организовать таким образом более сложные фильтры будет сложно или вовсе невозможно.
Фильтрация на стороне БД
Во втором случае, т.е. на стороне БД появляется намного больше возможностей для фильтрации данных.
Самый простой способ:
CREATE PROCEDURE GetFiltered
(
@Field1 AS VARCHAR(50),
@Field2 AS VARCHAR(50),
@Field3 AS VARCHAR(50),
@Field4 AS VARCHAR(50)
)
AS
SELECT
*
FROM
t_table as t
WHERE
t.field1 LIKE %@Field1%
AND t.field2 LIKE %@Field2%
AND t.field3 LIKE %@Field3%
AND t.field4 LIKE %@Field4%
Все замечательно работает, но как только приходится фильтровать данные которые хранятся в разных таблицах появляются проблемы с производительностью. Ведь, если необходимо применить фильтр только по одному полю, все равно приходится делать JOIN’ы на все необходимые для конечного набора таблицы, а это могут быть сотни тысяч записей.
пример с INNER JOIN
CREATE PROCEDURE GetFiltered
(
@Field1 AS VARCHAR(50),
@Field2 AS VARCHAR(50),
@Field3 AS VARCHAR(50),
@Field4 AS VARCHAR(50)
)
AS
SELECT
*
FROM
t_table as t
INNER JOIN t_table2 AS t2 ON t2.t_id = t.id
INNER JOIN t_table3 AS t3 ON t3.t_id = t.id
INNER JOIN t_table4 AS t4 ON t4.t_id = t.id
WHERE
t.field1 LIKE %@Field1%
AND t2.field2 LIKE %@Field2%
AND t3.field3 LIKE %@Field3%
AND t4.field4 LIKE %@Field4%
А используются ли все параметры фильтра каждый раз ? Если нет, то повысить производительность сложных фильтров можно, если не фильтровать весь набор данных целиком, а каким-то образом разделить запрос и применять фильтры только для тех параметров которые заполнены, а уже потом получить на основании этого отфильтрованного набора, допустим идентификаторов, все данные.
Улучшенная фильтрация на стороне БД
Следующий пример позволяет повысить скорость выполнения запросов для фильтрации данных по большому количеству параметров в случаях, когда чаще всего используются не все значения.
CREATE PROCEDURE GetFilteredEx
(
@Field1 AS VARCHAR(50),
@Field2 AS VARCHAR(50),
@Field3 AS VARCHAR(50),
@Field4 AS VARCHAR(50),
@Filter AS INT -- Параметр указывающий какие поля заполнены
)
AS
SELECT
tTmp.Id,
t2.field2,
t3.field3,
t4.field4
FROM
(SELECT
t.id,
1 as Filter -- Битовый флаг для идентификации множества
FROM
t_table as t
WHERE
(@Filter & 1 > 0) -- Благодаря этому условию весь фильтр не будет выполнятся до тех пор пока во входной параметр не придет заполненное значение @Field1
AND t.field1 LIKE %@Field1%
UNION
SELECT
t.id,
2 as Filter -- Битовый флаг для идентификации множества
FROM
t_table as t
INNER JOIN t_table2 AS t2 ON t2.t_id = t.id
WHERE
(@Filter & 2 > 0) -- Благодаря этому условию весь фильтр не будет выполнятся до тех пор пока во входной параметр не придет заполненное значение @Field2
AND t2.field2 LIKE %@Field2%
UNION
SELECT
t.id,
4 as Filter -- Битовый флаг для идентификации множества
FROM
t_table as t
INNER JOIN t_table3 AS t3 ON t3.t_id = t.id
WHERE
(@Filter & 4 > 0) -- Благодаря этому условию весь фильтр не будет выполнятся до тех пор пока во входной параметр не придет заполненное значение @Field3
AND t3.field3 LIKE %@Field3%
UNION
SELECT
t.id,
8 as Filter -- Битовый флаг для идентификации множества
FROM
t_table as t
INNER JOIN t_table4 AS t4 ON t4.t_id = t.id
WHERE
(@Filter & 8 > 0) -- Благодаря этому условию весь фильтр не будет выполнятся до тех пор пока во входной параметр не придет заполненное значение @Field4
AND t4.field4 LIKE %@Field4%
) AS tTmp
-- теперь имея отфильтрованные идентификаторы главной таблицы получаем остальные поля
INNER JOIN t_table2 AS t2 ON t2.t_id = tTmp.id
INNER JOIN t_table3 AS t3 ON t3.t_id = tTmp.id
INNER JOIN t_table4 AS t4 ON t4.t_id = tTmp.id
-- таким образом мы получим набор множеств маркированных полем Filter
-- для того что бы получить, например, только пересечение этих множеств необходимо вы полнить функцию SUM()
GROUP BY
tTmp.Id,
t2.field2,
t3.field3,
t4.field4
HAVING
SUM(filter) = @Filter
Благодаря тому, что мы получили множества (набор записей с одинаковым значением поля Filter) маркированные битовым флагом Filter, становится доступным выполнение арифметических операций над множествами. В данном примере было продемонстрировано, с помощью
HAVING
SUM(filter) = @Filter
получение пересечений множеств, если убрать функцию SUM() – получится объединение множеств, применив функцию SUB() получим только отличающиеся записи.
В результате описанных выше действий нам удалось ускорить фильтрацию, если заполнены не все параметры фильтра и реализовать мощный инструмент для управления множествами.
P.S.
Оставляйте в комментариях ваши предложения по ускорению фильтрации данных.