I am using a SQL Server database and have a table MYTABLE
which has data as shown below. I am trying to fetch the data from my stored procedure that accepts @AllowedUser
as parameter. The stored procedure is currently just simple select query
SELECT *
FROM MYTABLE
However it returns all three rows. I wanted to filter out if the returned data has any encrypted row (column: Encryption = 1
), only then filter out this row on the basis of @AllowedUser
. So for example, when @AllowedUser
parameter is 3, then I will get all three rows as shown
If @AllowedUser
is 2, then I will get first and second row as shown below (it will be same for @AllowedUser = 1
, @AllowedUser = 5
etc)
I had tried to to this by following way but I think it is not a good approach as I have to fetch the data twice from database
Step 1
SELECT COUNT(1)
FROM MYTABLE
WHERE Encryption = 1 -- (I fetched this result in a parameter)
If that parameter's value is greater than 1 (means there is an encrypted row), then I will filter out the result according to @AllowedUser
Else
I will simply execute the select statement.
Is there any better way to perform the above task without having to call the database twice? Can I use the CASE
statement?
1条答案
按热度按时间eh57zj3b1#
As best I can understand your requirements I think you just need a simple
OR
condition - but you still need to query the table twice to determine whether a user has an encryption row.However since you are concerned about performance I have show a way to do it which* appears to only need to access the table a single time.
BUT this is not how SQL performance works. SQL is a descriptive language, you are describing the results you want. You are not telling the engine how to obtain those results. It works that out is a very complex manner taking into account indexes, statistics and other magic.
So in this simple case, when I turn on display execution plan, the CTE solution is running much slower than the double table query.
The real lesson here is, don't try and pre-optimise your query, nor assume you know how a given query is going to perform (there are some basic rules of thumb of course). The general rule is, write the query in the most clear, logical manner you can, and if there are then performance issues, performance tune those queries.