本文介紹了如何讓多參數(shù)的SQL Server過濾程序更快的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
目前我正在努力尋找在大表格中搜索的來源，我有一個很好的模型化表格1個主要表格供用戶使用，還有幾個表格用于性別、地址、上次購買、評論的產(chǎn)品等功能。
合并和篩選時EF非常慢，因此我決定使用存儲過程并使用Dapper進(jìn)行調(diào)用。
我想過濾這些幾乎5.5 GB(47萬行)的數(shù)據(jù)
轉(zhuǎn)到更大，31列，7個表，每個表+15列)。
我有5個不同的過濾器，它必須很快。因為這個程序
預(yù)計響應(yīng)時間為1分鐘
查詢應(yīng)使用動態(tài)參數(shù)
我必須這樣做，我嘗試了一些不同的方法，但仍然很慢。我有5個過濾器和1個日期聲明。用戶可以發(fā)送5個、4個或3個篩選器，也可以不發(fā)送篩選器。
-
我試過的方法，如果參數(shù)為空，它不會做任何事情，但參數(shù)不是空的，而不是過濾，但我從博客上讀到使用‘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
方法正在使用動態(tài)查詢
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秒內(nèi)就能響應(yīng)。我應(yīng)該如何在幾秒鐘內(nèi)完成？
推薦答案
您需要執(zhí)行幾個步驟。
-
將表劃分為2個或3個部分，如篩選產(chǎn)品和付款，然后插入到#temptable中，并與Channel等其他表應(yīng)用內(nèi)部連接，并獲得篩選的數(shù)據(jù)#Tempable2，然后將第二個temptable與Sales和SalesType連接
在WHERE中篩選列，在所有列上創(chuàng)建索引。
如果您一次從所有表中獲取數(shù)據(jù)并應(yīng)用篩選器，則會同時篩選數(shù)百萬條記錄。因此，如果劃分表，則篩選將應(yīng)用于較少的記錄。
這篇關(guān)于如何讓多參數(shù)的SQL Server過濾程序更快的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
