Which of the following is ( are ) help to improve query performance 1. Top 2. Where 3. Column Name
asked Jun 15, 2012 at 05:06 AM in Default
Checklist for fast queries
As Kev said, it depends on the query, and also what you mean by "improve performance".
A TOP clause will often reduce query time by limiting the number of rows returned. Of course, reducing the time the query takes to complete will not "improve performance" if it means you get incorrect results (asking for all instances of some condition and then limiting it to the top 2 will give you the wrong answer if that condition happens more than twice). And TOP will not speed things up if the limit set in the top clause is greater than what would have been returned anyway.
A where condition, like a TOP clause, will often reduce query time by reducing the number of rows returned. But a complex where condition can take time to evaluate and so increase the overall query time (especially if the query winds up returning most of the rows anyway.) In terms of performance, that again is meaningless if you are not getting the results that actually answer the question you need to ask.
Now, you can have two different where clauses that return the same results but take dramatically different execution times. This frequently comes up when a join is involved. In that case, it is fully meaningful to talk about improving performance, but that is a very deep topic.
As for using the column names in your query, it can definitely help if by listing the columns (instead of using
Listing out the column names can still be useful even if you list every single one though. It helps future readers know what your actual intent was and can help make the code more robust against columns added to the table in the future.
So, in short the answer to all 3 is the infamous "It depends." I have a fairly general article about improving database performance at [An Informal Look at Database Performance] that might help.: http://www.sqlservercentral.com/articles/Performance/71001/