June 23, 2011 at 10:04 am
Hi all,
Quick question here. Let's say I have a fairly large table, which contains an ID field that is a PRIMARY KEY IDENTITY field. I also have a dozen other fields, and lets say 100 million rows in the table.
If I have a query in which I am asking for all the fields in the table, as well as doing some WHERE clause filtering, would it be faster to take one of the WHERE clauses that reduces the row count considerably, use that clause with just the ID in the SELECT, and dump the results into a temporary table, then do the rest of the filtering in that small table? Or is the SQL Server engine smart enough to do that on its own?
EX:
CREATE TABLE #IDs
(
ID INT
)
INSERT INTO #IDs (ID)
SELECT ID
FROM TableA
WHERE Clause1 BETWEEN 500 AND 500000
SELECT
FIELDS
FROM TableA
JOIN #IDs ON TableA.ID = #IDs.ID
WHERE [OTHER CLAUSES]
VS
CREATE TABLE #IDs
(
ID INT,
FIELDS
)
INSERT INTO #IDs (ID, FIELDS)
SELECT ID, FIELDS
FROM TableA
WHERE Clause1 BETWEEN 500 AND 500000
SELECT
FIELDS
FROM #IDs
WHERE [OTHER CLAUSES]
VS
SELECT
FIELDS
FROM TableA
WHERE Clause1 BETWEEN 500 AND 500000
AND [OTHER CLAUSES]
June 23, 2011 at 10:08 am
Your best bet is to go with a single where clause with all your filters , the optimser will be able to filter the result sets on its own , and doesn't need help such as filtered result sets with fewer rows.
You can look at the execution plan for the above statement to see this in action.
June 23, 2011 at 10:58 am
The answer to this might change if joins are in the equation though. I don't know that the optimizer will always choose to apply the criteria before it attempts to join large tables together, which could in some cases lead to a plan that is far from optimal. Someone feel free to correct me on that, but that's my understanding.
June 23, 2011 at 1:02 pm
Your right ,the performance would vary depending on which type of join is used by the optimizer and available indexes, my previous response did not conider joins btw.
type of join hints
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply