Menu
I have a stored procedure that has a optional parameter, @UserID VARCHAR(50). The thing is, there are two ways to work with it:
NULL, the have an IF...ELSE clause, that performs two different SELECT queries, one with 'WHERE UserID = @UserID' and without the where.'%' and then just have the where clause use 'WHERE UserID LIKE @UserID'. In the calling code, the '%' wont be used, so only exact matches will be found.The question is: Which option is faster? Which option provides better performance as the table grows? Be aware that the UserID column is a foreign key and is not indexed.
EDIT: Something I want to add, based on some answers: The @UserID parameter is not (necessarily) the only optional parameter being passed. In some cases there are as many as 4 or 5 optional parameters.
334
"WHERE" is faster than "HAVING"! Save this answer. Show activity on this post. "Having" is slower if we compare with large amount of data because it works on group of records and "WHERE" works on number of rows..
A where clause will generally increase the performance of the database. Generally, it is more expensive to return data and filter in the application. The database can optimize the query, using indexes and partitions. The database may be running in parallel, executing the query in parallel.
Popular methods for SQL table optimization and query speed improvement include: Providing a limited range of dates for time series data. Limiting the dataset in a subquery. Avoiding duplicate data.
1 Answer. Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string. We can use LIKE to check a particular pattern like column values starting with 'abc' in this case.
What I typically do is something like
WHERE ( @UserID IS NULL OR UserID = @UserID )
And why isn't it indexed? It's generally good form to index FKs, since you often join on them...
If you're worried about query plan storage, simply do: CREATE PROCEDURE ... WITH RECOMPILE
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
