CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Zeyad
Zeyad

Reputation: 626

Does using WHERE clause with a SELECT statement increase or decrease performance?

I am working on C# WPF application that will interact with a database very often and I am concerned about the performance.

I have a question, when we add a WHERE clause to a SELECT statement, does this increase the performance because less records will be retrieved or it makes the performance worse because it will have to go through all the records anyways then additionally execute a filter?

Upvotes: 1

Views: 9316

Answers (3)

K M Dilshan Udara
K M Dilshan Udara

Reputation: 1195

It's depends on your requirement. The best solution is using JOINS. But some times you have to search by specific field like name='Dilshan', In that case you have to use WHERE clause.

However don't retrieve every column from select statement. Always SELECT what you requires. Try to avoid using select * statements. It will lower the performance.

Upvotes: 0

Shekar Kola
Shekar Kola

Reputation: 1297

when we add a WHERE clause to a SELECT statement, does this increase the performance because less records will be retrieved

As per Logical Processing Order that is correct, improvement could be always possible in rendering time, but not always in query processing time.

You can test that by enabling query STATISTICS, execute following command just before executing the query in SSMS, it would give you great insights along with query result (message tab):

SET STATISTICS IO, TIME ON;

P.S: There would be lot of areas to be considered when it comes to query-optimization, STATISTICS are essential among them.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.
  • Reducing the amount of data may make other operations faster.

There may be some edge cases where using a where clause might not improve performance. But in general, you should push processing to the database for tasks well-suited to the database.

Upvotes: 2

Related Questions