December 10, 2018 at 9:53 am
is it possible to use CASE statement or any other option in below where conditions should be execute
when the variables(@LAN,@SUBFORMAT,@CASNUM) contains values,otherwise where condition should not execute.(when it contains null).
DECLARE @LAN VARCHAR(10) = 'EN'
DECLARE @SUBFORMAT VARCHAR(50) = 'MTR'
DECLARE @CASNUM VARCHAR(20)
SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
PDF TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
END
December 10, 2018 at 10:03 am
jkramprakash - Monday, December 10, 2018 9:53 AMis it possible to use CASE statement or any other option in below where conditions should be execute
when the variables(@LAN,@SUBFORMAT,@CASNUM) contains values,otherwise where condition should not execute.(when it contains null).
DECLARE @LAN VARCHAR(10) = 'EN'
DECLARE @SUBFORMAT VARCHAR(50) = 'MTR'
DECLARE @CASNUM VARCHAR(20)SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
PDF TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
END
Is this for performance? You might find adding this hint :
OPTION (OPTIMIZE FOR UNKNOWN) to the end of the query improves the performance.
Failing that dynamic SQL might be the way to go. So you only include the WHERE for the variables that are not null.
December 10, 2018 at 10:28 am
Yes i am improving the performance, i am using the hint Option(recompile) in the end of query.is it better choice compare to OPTION (OPTIMIZE FOR UNKNOWN)?.suggest please because i am going to use union all operator..i am not able to implement dynamic query now.so i am trying for case statement....
December 10, 2018 at 10:33 am
jkramprakash - Monday, December 10, 2018 10:28 AMYes i am improving the performance, i am using the hint Option(recompile) in the end of query.is it better choice compare to OPTION (OPTIMIZE FOR UNKNOWN)?.suggest please because i am going to use union all operator..i am not able to implement dynamic query now.so i am trying for case statement....
You need to test it by running the query with different hints to see which one is faster.
December 10, 2018 at 9:58 pm
For union all operator shall i use CTE for using the hints OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) at the end of query in my sp?.CTE Is right choice for performance?
Example
;with cte
{
QUERY 1
UNION ALL
QUERY 2
}
select * from cte OPTION (RECOMPILE);
December 11, 2018 at 2:17 am
jkramprakash - Monday, December 10, 2018 9:58 PMFor union all operator shall i use CTE for using the hints OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) at the end of query in my sp?.CTE Is right choice for performance?
Example;with cte
{
QUERY 1
UNION ALL
QUERY 2
}select * from cte OPTION (RECOMPILE);
Yes, that's how you add that hint.
But I'm not sure how you are going to use UNION ALL from the initial query you have?
December 11, 2018 at 3:24 am
Orginal Query
[DECLARE @LAN VARCHAR(10) = 'EN'
DECLARE @SUBFORMAT VARCHAR(50) = 'MTR'
DECLARE @CASNUM VARCHAR(20)
SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
PDF TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
UNION ALL
SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
HTML TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
END
Rewriting Query
DECLARE @LAN VARCHAR(10) = 'EN'
DECLARE @SUBFORMAT VARCHAR(50) = 'MTR'
DECLARE @CASNUM VARCHAR(20)
;WITH CTE AS(
SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
PDF TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
UNION ALL
SELECT DISTINCT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME]
FROM
HTML TP
WHERE
(@LAN IS NULL OR TP.F_LAN = @LAN OR @LAN = '-1')
AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM SUBFORMATTEMP))
AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
)
SELECT * FROM CTE(OPTION(RECOMPILE))
END
is it correct?
December 11, 2018 at 5:29 am
SELECT * FROM CTE OPTION(RECOMPILE)
You have too many brackets and you don't need to put it in a common table expression.
December 11, 2018 at 6:18 am
ok.thanks.i hope cte will improve performance.
December 12, 2018 at 2:45 am
Jonathan AC Roberts - Tuesday, December 11, 2018 5:29 AMSELECT * FROM CTE OPTION(RECOMPILE)
You have too many brackets and you don't need to put it in a common table expression.
If i Add the option(recompile) hint in the query end, the query cost is 93% if i Remove the option(recompile) hint in my query the query cost is 57%.
whether we have to check the query cost in ctrl+m execution plan OR SET STATISTICS TIME ON;
SET STATISTICS IO ON(CPU TIME) for query optimization.which method is correct one?
December 12, 2018 at 4:16 am
jkramprakash - Wednesday, December 12, 2018 2:45 AMJonathan AC Roberts - Tuesday, December 11, 2018 5:29 AMSELECT * FROM CTE OPTION(RECOMPILE)
You have too many brackets and you don't need to put it in a common table expression.If i Add the option(recompile) hint in the query end, the query cost is 93% if i Remove the option(recompile) hint in my query the query cost is 57%.
whether we have to check the query cost in ctrl+m execution plan OR SET STATISTICS TIME ON;
SET STATISTICS IO ON(CPU TIME) for query optimization.which method is correct one?
SET STATISTICS IO, TIME ON
You need to try different hints with different inputs and see which one is fastest.
You can issue a DBCC FREEPROCCACHE after each query is run so it won't store the plan from the last input you tried.
The most important figure to look at from the set statistics statement is the elapsed time, but also look at the read counts.
The cost of queries in the execution plan isn't always accurate so don't rely on that.
December 14, 2018 at 5:39 am
Thank You.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply