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

本文介绍了如何让多参数的SQL Server过滤程序更快的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

查询应使用动态参数

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

我试过的方法，如果参数为空，它不会做任何事情，但参数不是空的，而不是过滤，但我从博客上读到使用‘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,''))  )

我尝试过的方法，没有‘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

方法正在使用动态查询

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秒内就能响应。我应该如何在几秒钟内完成？

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

问题描述

推荐答案

相关文档推荐