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

How should make faster SQL Server filtering procedure with many parameters(如何让多参数的SQL Server过滤程序更快)
本文介绍了如何让多参数的SQL Server过滤程序更快的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

目前我正在努力寻找在大表格中搜索的来源,我有一个很好的模型化表格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中筛选列,在所有列上创建索引。
如果您一次从所有表中获取数据并应用筛选器,则会同时筛选数百万条记录。因此,如果划分表,则筛选将应用于较少的记录。

这篇关于如何让多参数的SQL Server过滤程序更快的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)