如何让多参数的SQL Server过滤程序更快

2022-08-19 00:00:00 sql database sql-server entity-framework dapper

目前我正在努力寻找在大表格中搜索的来源，我有一个很好的模型化表格1个主要表格供用户使用，还有几个表格用于性别、地址、上次购买、评论的产品等功能。

  • 合并和筛选时EF非常慢，因此我决定使用存储过程并使用Dapper进行调用。
  • 我想过滤这些几乎5.5 GB(47万行)的数据 转到更大，31列，7个表，每个表+15列)。
  • 我有5个不同的过滤器，它必须很快。因为这个程序 预计响应时间为1分钟

查询应使用动态参数

我必须这样做，我尝试了一些不同的方法，但仍然很慢。我有5个过滤器和1个日期声明。用户可以发送5个、4个或3个筛选器，也可以不发送筛选器。

  1. 我试过的方法，如果参数为空，它不会做任何事情，但参数不是空的，而不是过滤，但我从博客上读到使用‘or’确实会降低性能
    where (SaOr.InsertDate between ISNULL(@StartDate,'1900-01-01') and ISNULL(DATEADD(DAY, 1, @DueDate),@TOMORROW))
            and (@ProductName is null or  SaOrPr.Name like '%' + @ProductName + '%')
            and (@PaymentType is null or LEN(@PaymentType)> LEN(REPLACE(@PaymentType,PaymentMethodId,'')) )
            and (@Channel is null or LEN(@Channel)> LEN(REPLACE(@Channel,SaOr.ChannelId,'')))
            and (@SalesType is null or  LEN(@SalesType)> LEN(REPLACE(@SalesType,SalesOrderTypeId,'')) )
            and (@SalesStatus is null or  LEN(@SalesStatus)> LEN(REPLACE(@SalesStatus,StatusId,''))  )
  1. 我尝试过的方法，没有‘or’，但速度比%1慢。
    where (SaOr.InsertDate between ISNULL(@StartDate,'1920-01-01') and ISNULL(DATEADD(DAY, 1, @DueDate),@TOMORROW)) 
      
            AND (SELECT CHARINDEX(ISNULL(ISNULL(@ProductName,SaOrPr.[Name]),' '),ISNULL(SaOrPr.[Name],' '))) >0
            AND (SELECT CHARINDEX(ISNULL(CAST(PaymentMethodId AS VARCHAR(38)),' '),ISNULL(ISNULL(@PaymentType,PaymentMethodId),' '))) >0
             AND (SELECT CHARINDEX(ISNULL(CAST(SaOr.ChannelId AS VARCHAR(38)),' '),ISNULL(ISNULL(@Channel,SaOr.ChannelId),' '))) >0
              AND (SELECT CHARINDEX(ISNULL(CAST(SalesOrderTypeId AS VARCHAR(38)),' '),ISNULL(ISNULL(@SalesType,SalesOrderTypeId),' '))) >0
               AND (SELECT CHARINDEX(ISNULL(CAST(StatusId AS VARCHAR(38)),' '),ISNULL(ISNULL(@SalesStatus,StatusId),' '))) >0

  1. 方法正在使用动态查询

    DECLARE@QUERY varchar(Max)=‘INSERT INTO#TmpResult 选择
    一些字段 来自#tmpSales saor

     where ( FilteredCount between   @pagination  and   @pagination + @PageSize - 1) '

 + CASE WHEN @PaymentType IS NOT NULL THEN
 ' AND LEN(@PaymentType)> LEN(REPLACE(@PaymentType,CONVERT(varchar(38),SaOr.PaymentMethodId),''''))  ' ELSE '' END

 + CASE WHEN @Channel IS NOT NULL THEN
 ' AND LEN(@Channel)> LEN(REPLACE(@Channel,CONVERT(varchar(38),SaOr.ChannelId),''''))  ' ELSE '' END

 + CASE WHEN @SalesType IS NOT NULL THEN
 ' AND LEN(@SalesType)> LEN(REPLACE(@SalesType,CONVERT(varchar(38),SaOr.SalesOrderTypeId),''''))  ' ELSE '' END

 + CASE WHEN @SalesStatus IS NOT NULL THEN
 ' AND LEN(@SalesStatus)> LEN(REPLACE(@SalesStatus,CONVERT(varchar(38),SaOr.StatusId),''''))  ' ELSE '' END

 +    '  OPTION (RECOMPILE);';

尽管仍然不够快，但3.方法在大约15秒内就能响应。我应该如何在几秒钟内完成？

解决方案

您需要执行几个步骤。

  1. 将表划分为2个或3个部分，如筛选产品和付款，然后插入到#temptable中，并与Channel等其他表应用内部连接，并获得筛选的数据#Tempable2，然后将第二个temptable与Sales和SalesType连接
  2. 在WHERE中筛选列，在所有列上创建索引。
如果您一次从所有表中获取数据并应用筛选器，则会同时筛选数百万条记录。因此，如果划分表，则筛选将应用于较少的记录。

相关文章