user6811593 user6811593 - 1 year ago 75
SQL Question

With or without WHERE clause in SQL performance

Sorry for this newbie question.

I want to query a table with 10 million rows. However, there are historical data that I might not need. In view of lessening the load on the server during data extraction process, would implementing a WHERE clause in the query help? For instance, I could pull only 2015 to 2016 data instead of 2007 to 2016 data.

Or should I just pull the entire data table without a WHERE clause?

Thank you!

Answer Source

WHERE would obviously be helpful, because it reduces the number of rows being returned from the database to the application. Instead of 10 million rows, you will get a smaller number. This is usually a performance win.

Given that you want to pull complete years of data, the WHERE clause may not use an index. Technically, this is because the selectivity of the condition is too low -- that is, too many rows are returned. One exception is if the date/year column is a clustered index.

However, not using an index is not relevant, because the volume of data being returned should be a fraction of the total data when your query has the WHERE clause.