Menu
Consider a query like:
select *
from t
where a = 5 and (a = 5 and b = 7)
Does the redundant predicate a = 5 affect the performance of the query. I believe not, but I am being told otherwise (yes, I know that it depends on the DBMS but just assume it's some enterprise-y DBMS like SQL Server, DB2 or Oracle).
341
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.
Table size: If your query hits one or more tables with millions of rows or more, it could affect performance. Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow. There's an example of this in the subqueries lesson.
Although the where clause has a huge impact on performance, it is often phrased carelessly so that the database has to scan a large part of the index. The result: a poorly written where clause is the first ingredient of a slow query.
Summary. The SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query. The WHERE condition in SQL can be used in conjunction with logical operators such as AND and OR, comparison operators such as ,= etc.
The answer would normally be no, the redundant clause does not affect performance. Of course being a programmer you can use evidence to prove it one way or the other (unlike the person who will repeat their myths as fact).
Just open up your database of choice, enter your query with and without the redundent clause, then ask your database to explain both queries.
For example in SQL Server Management Studio (MS SQL Server) open a query window, enter your query and then right click and select "Display estimated executation plan" (or you can select an option to display the actual plan after the query is run).
64
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
