I have a table that is about a 1 million records. Which one of the two queries would run faster? Is there anything else so that I can increase the performance of the choosen query?
DECLARE @pi_Filter = 'BlaBla'
--Query 1
SELECT * FROM T1
INNER JOIN T2 ON T2.ID = T1.ID
WHERE T1.FilterField = @pi_Filter
--------------------------------
--Query 2
SELECT * FROM (SELECT * FROM T1 WHERE FilterField = @pi_Filter) T1
INNER JOIN T2 ON T2.ID = T1.ID
In general, which on of these would I use to check the performance of the query?
--Display Estimated Execution Plan
--Analyze Query In Database Engine Tuning Advisor
It is the vendor's database so they did not give us permission to use the above but I am still curious.
Does a temp table slows down the performance since it does not have indexes?
SQL should generate the same query plan for either query.
You'll want some type of index to contain "FilterField" assuming that it is generally selective. That is, that only a limited number of rows will generally match the value you search on. If, for example, 10% or more of rows commonly match the filter, then SQL will just likely scan the full table anyway.
A temp table without an index does not automatically slow down a query, but it can. It depends.
Hi Scott, can I add an index to a temp table because I tend to use them a lot since I use the data in a couple of places. When the session drops, it will remove the temp table but will it automatically remove the index?
Also, if I limit the number of fields, would that help on the performance? This table has over 150 fields and a million records.
Also, should I use "no lock"?
Last, this query actually ran faster than the above two:
SELECT * FROM T1
INNER JOIN T2 ON T2.ID = T1.ID AND T1.FilterField = @pi_Filter
If you put an index on a table, esp. a temp table, that doesn't already have an index, be sure to use a clustered index. There's almost never any point in putting a non-clus index on a heap (a table w/o a clus index).
