I am well aware of the JOIN, and the sarcasm isn't appreciated. The
tools included with Enterprise Manager haven't helped identify or
resolve this issue.
There are several columns identified in certain tables that cause the
query time to go from an acceptable 10 seconds to 40 seconds when added
to the SELECT statement. Each column added increases the time until
the query time is over 8 minutes which is unacceptable.
The columns are from a table that is already included in the query. We are not adding another table/join when selecting these columns. The column
data types are NVARCHAR(40) and are not included in the WHERE clause.
The only solution found so far is adding the columns as nested SELECT
statements (all 15 of them) as shown in my original post. This is a
real mess, but the client doesn't care as long as he gets the
Last edited by James Aiello; 11-25-04 at 06:16.
Reason: Critical text left out of original response
OK, I've run out of sarcasm, so here is a suggestion:
If you have what is known as a "covered index" on table A that includes both ColumnA and ID, then your first query is never actually accessing the data in Table A, because it can get all the info it needs just from the index alone. Adding in another field requires the server to actually look up that value in table A based upon the index value, and that takes additional time.
So if this is the case, then its not that your second query is abnormally slow, but rather that SQL Server is able to process your first query extremely efficiently.
If it's not practically useful, then it's practically useless.