当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Best SELECT syntax performance with 5 parameter

问题描述:

i trying to select a table which have a million records using a Stored Proc.

The Stored Proc have a 5 parameter.

Which is has the best performance

Query 1:

SELECT * FROM Table1

WHERE Column1 like '%'+ @param1 +'%' AND

Column2 like '%'+ @param2 +'%' AND

Column3 like '%'+ @param3 +'%' AND

Column4 like '%'+ @param4 +'%' AND

Column5 like '%'+ @param5 +'%' AND

Note: I am using this because the result of LIKE with empty ('') param is the same as not using the param.

SELECT * FROM Table1

WHERE Column1 like '%'+ @param1 +'%'

is have the same result as

SELECT * FROM Table1

is the value of @param1 is empty ('')

Query 2:

IF @param1 = '' SELECT @param1 = null

IF @param2 = '' SELECT @param2 = null

IF @param3 = '' SELECT @param3 = null

IF @param4 = '' SELECT @param4 = null

IF @param5 = '' SELECT @param5 = null

IF @param1 is not null and @param2 is null and @param3 is null @param4 is null and @param5 is null

BEGIN

SELECT * FROM Table1

WHERE Column1 like '%'+ @param1 +'%'

END

and continue till all possibilities is covered.

Which is has the best performance?

The meaning of best is, has a efficient I/O and procession time

Thank you in advance


UPDATE

I've tried RADAR solution and i compared it with my first query using Actual Execution Plan

so it's the result:

Result from Query 1 (me)

Result from Query 2 (RADAR)

I don't know how to read it, please help me

Thanks again

网友答案:

Second approach is better, you can simplify it with or condition based on parameter value

 SELECT * FROM Table1
 WHERE (@param1 ='' or Column1 like '%'+ @param1 +'%' )
 AND 
 (@param2 ='' or Column2 like '%'+ @param2 +'%' )
 AND...
分享给朋友:
您可能感兴趣的文章:
随机阅读: