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