Query Performance for 1 million records

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
WHERE T1.FilterField = @pi_Filter
--Query 2
SELECT * FROM (SELECT * FROM T1 WHERE FilterField = @pi_Filter) T1

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.

1 Like

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:

INNER JOIN T2 ON T2.ID = T1.ID AND T1.FilterField = @pi_Filter

Thanks Scott

Also, it is the vendor's database and it is read only. I cannot put an index on it. :slight_smile:

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).

1 Like

Then it's up to the vendor to tune it, because adding/adjusting indexes is the only real way to tune queries.

1 Like

I have two temp tables.

"Temp Table 1" has about 6000 records, takes 10 seconds to load into it
"Temp Table 2" has about 10 records, takes 0 seconds to load into it

But when I joinned them (with one field), it takes about 90 seconds. Why would it take so long?

Please see tech docs on join what it does

I think for each column row value it loops through all the other column rows