December 21, 2015 at 3:25 am
I have a table [Customer] with few columns BranchId. If I create a simple query with Branch filter than.
SELECT [col1], [col2] from Customer where BranchId = @BranchId
another case is I need to pull all customers if the @BranchId parameter is NULL, in that case I can use two options,
OPTION: 1
If @BranchId is NOT null then
select [col1], [col2] from Customer where BranchId = @BranchId
else
select [col1], [col2] from Customer
OPTION: 2
select [col1], [col2] from Customer where BranchId = CASE when @BranchId IS NULL THEN BranchId else @BranchId end
Let me know which option should we follow as a standards or which one is a best practice? The reason I am asking is because of simplicity and gain performance.
There are several possibilities in which we can choose either the first option or the second one. when there are very few rows in customer then chose second option, when there are thousands of rows in that case go for option one? Let me know your thoughts for better understanding...
Shamshad Ali
December 21, 2015 at 3:34 am
They've both got inherent performance problems. Take a read through https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2015 at 6:18 am
why not
create procedure GetCustomerByBranchID(@BranchId int)
AS
BEGIN
SELECT [col1], [col2] from Customer where BranchId = @BranchId
END
GO
create procedure GetAllCustomers
AS
BEGIN
SELECT [col1], [col2] from Customer
END
GO
if @BranchId is not null
Begin
exec GetCustomerByBranchID @BranchId
END
ELSE
BEGIN
EXEC GetAllCustomers
END
December 21, 2015 at 12:41 pm
Instead of:
select [col1], [col2] from Customer
where BranchId = CASE when @BranchId IS NULL THEN BranchId else @BranchId end
I usually go with
select [col1], [col2] from Customer
where (@BranchID is NULL OR BranchId = @BranchId)
I doubt that there is a major difference in execution plan between these.
--
JimFive
December 21, 2015 at 1:07 pm
James Goodwin (12/21/2015)
Instead of:
select [col1], [col2] from Customerwhere BranchId = CASE when @BranchId IS NULL THEN BranchId else @BranchId end
I usually go with
select [col1], [col2] from Customerwhere (@BranchID is NULL OR BranchId = @BranchId)
I doubt that there is a major difference in execution plan between these.
--
JimFive
You win a table scan.
December 22, 2015 at 12:50 am
James Goodwin (12/21/2015)
I doubt that there is a major difference in execution plan between these.
You would be wrong.
The first is a guaranteed table scan every time. The second is far worse.
Please read through https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply