逗号分隔值(CSV)参数过滤
需要有关如何改进我的SQL脚本以获得更好性能的帮助。 dbo.Products
表有百万行。我很犹豫要用动态SQL重写它。谢谢!逗号分隔值(CSV)参数过滤
DECLARE
@Brand varchar(MAX) = 'Brand 1, Brand 2, Brand 3',
@ItemCategory varchar(MAX) = 'IC1, IC2, IC3, IC4, IC5'
--will return all records if params where set to @Brand = NULL, @ItemCategory = NULL
SELECT
[Brand],
SUM([Amount]) AS [Amount]
FROM dbo.Products (NOLOCK)
LEFT JOIN [dbo].[Split](@Brand, ',') FilterBrand ON Brand = [FilterBrand].[Items]
LEFT JOIN [dbo].[Split](@ItemCategory, ',') FilterItemCategory ON ItemCategory = [FilterItemCategory].[Items]
WHERE
(@Brand IS NULL OR (@Brand IS NOT NULL AND [FilterBrand].[Items] IS NOT NULL)) AND
(@ItemCategory IS NULL OR (@ItemCategory IS NOT NULL AND [FilterItemCategory].[Items] IS NOT NULL))
GROUP BY
[Brand]
下面是拆分表值函数,我在网上找到:
CREATE function [dbo].[Split]
(
@String varchar(8000),
@Delimiter char(1)
)
RETURNS @Results TABLE (Items varchar(4000))
AS
BEGIN
IF (@String IS NULL OR @String = '') RETURN
DECLARE @i int, @j int
SELECT @i = 1
WHILE @i <= LEN(@String)
BEGIN
SELECT @j = CHARINDEX(@Delimiter, @String, @i)
IF @j = 0
BEGIN
SELECT @j = len(@String) + 1
END
INSERT @Results SELECT RTRIM(SUBSTRING(@String, @i, @j - @i))
SELECT @i = @j + LEN(@Delimiter)
END
RETURN
END
以下解决方案与使用进行功能
Declare @IDs Varchar(100)
SET @IDs = '2,4,6'
Select IsNull(STUFF((Select ', '+ CAST([Name] As Varchar(100)) From [TableName]
Where CharIndex(','+Convert(Varchar,[ID])+',', ','[email protected]+',')> 0
For XML Path('')),1,1,''),'') As [ColumnName]
这里是我使用的功能。我也有另一个包装这个返回数值,我也觉得很有帮助。
编辑:对不起,至于如何提高查询性能,我平时值分成表变量,并执行我加入到但这可能不会改变你的表现,只是你的可读性。我在表现方面唯一能看到的就是你双重检查你的连接是否产生任何东西。在两个表上有两个有条件的左连接,你实际上无法获得更好的性能。它基本上归结为当时的索引。
(@Brand IS NULL OR [FilterBrand].[Items] IS NOT NULL)
功能:
ALTER FUNCTION [dbo].[fn_SplitDelimittedList]
(
@DelimittedList varchar(8000),
@Delimitter varchar(20)
)
RETURNS
@List TABLE
(
Item varchar(100)
)
AS
BEGIN
DECLARE @DelimitterLength INT
SET @DelimitterLength = LEN(@Delimitter)
-- Tack on another delimitter so we get the last item properly
set @DelimittedList = @DelimittedList + @Delimitter
declare @Position int
declare @Item varchar(500)
set @Position = patindex('%' + @Delimitter + '%' , @DelimittedList)
while (@Position <> 0)
begin
set @Position = @Position - 1
set @Item = LTRIM(RTRIM(left(@DelimittedList, @Position)))
INSERT INTO @List (Item) VALUES (@Item)
set @DelimittedList = stuff(@DelimittedList, 1, @Position + @DelimitterLength, '')
set @Position = patindex('%' + @Delimitter + '%' , @DelimittedList)
end
RETURN
END
嘿只是尝试我都没有使用任何while循环here.And只是用这个来代替你的分割功能,并使用关口,以匹配LEFT JOIN创建的分割功能。
ALTER function dbo.SplitString(@inputStr varchar(1000),@del varchar(5))
RETURNS @table TABLE(col varchar(100))
As
BEGIN
DECLARE @t table(col1 varchar(100))
INSERT INTO @t
select @inputStr
if CHARINDEX(@del,@inputStr,1) > 0
BEGIN
;WITH CTE as(select ROW_NUMBER() over (order by (select 0)) as id,* from @t)
,CTE1 as (
select id,ltrim(rtrim(LEFT(col1,CHARINDEX(@del,col1,1)-1))) as col,RIGHT(col1,LEN(col1)-CHARINDEX(@del,col1,1)) as rem from CTE
union all
select c.id,ltrim(rtrim(LEFT(rem,CHARINDEX(@del,rem,1)-1))) as col,RIGHT(rem,LEN(rem)-CHARINDEX(@del,rem,1))
from CTE1 c
where CHARINDEX(@del,rem,1)>0
)
INSERT INTO @table
select col from CTE1
union all
select rem from CTE1 where CHARINDEX(@del,rem,1)=0
END
ELSE
BEGIN
INSERT INTO @table
select col1 from @t
END
RETURN
END
DECLARE @Brand varchar(MAX) = 'Brand 1,Brand 2,Brand 3',
@ItemCategory varchar(MAX) = ' IC1 A ,IC2 B , IC3 C, IC4 D' --'IC1, IC2, IC3, IC4, IC5'
select * from dbo.SplitString(@ItemCategory,',')
我试过你的分裂功能,它比我使用的更快。问题是,如果我有这个PARAM值: DECLARE @ItemCategory VARCHAR(MAX)= 'IC1 A,IC2 B,IC3 C,IC4 d' SELECT * FROM dbo.SplitString(@ItemCategory, '') OUTPUT: IC1一个 IC3ç IC2乙 IC2 B,IC3 C,IC4 d – peng 2012-08-09 23:00:43
嘿检查最新更新function.I是做小的失误在前面,而返回output.So这将很好地工作。 – AnandPhadke 2012-08-10 03:58:07
谢谢,现在好了。 – peng 2012-08-10 11:56:12
几件事情:(1)你真** ** **需要ALL **从'Products'列?如果不是,请不要使用SELECT *,而是明确指定列的列表。这*可能会为性能调整打开一个机会; (2)确保'JOIN'中涉及的所有列都被正确编入索引; – 2012-08-09 16:30:43
我最近记录了一些更有效的方法来分割字符串:http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings&http://www.sqlperformance.com/2012/ 08/t-sql-queries/splitting-strings-follow-up – 2012-08-09 16:45:38
@marc_s我修改了我的示例脚本。我在现实生活中不使用“SELECT *”...谢谢。 – peng 2012-08-09 16:56:42