Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

select top n runs faster then select * on large table

Avatar of alexk23
alexk23 asked on
Microsoft SQL Server 2005
14 Comments1 Solution657 ViewsLast Modified:
We have query that joins 4 tables. Each table is a multi million row table.

sample query to give you an idea (real tables names and column were replaced with generics).

SELECT
   *
FROM Table1 t1 with (NOLOCK)
INNER JOIN Table2 t2 with (NOLOCK)
       t1.pk = t2.fk and (t2.col = 'W' or (t2.col3 = 'S' and t2.col4 = 5))
INNER JOIN Table3 t3 with (NOLOCK)
       on t3.fk = t1.pk and t3.col6 = 1
       and (t3.col3 is null or (t1.pk = t3.col5 and t3.col2 = 1))
       and (t3.col7 = 'Y' or (7 > 0 and t3.col9 <= getDate() + 7))
INNER JOIN Table4 t4 with (NOLOCK)
        t1.pk = t4.fk
WHERE t1.col9 = 'E'
  and t1.col10 <> 1
  and t1.col11 IS NOT NULL;

there is an index on every field that is used in every table in this query.
the 3rd join is very complex and creates internally some tables and results in some table scans (probably b/c of the OR conditions).
this query on average returns from 5-100 rows of data.

to optimize the query without changing anything all I did was add Top 1000 after the select keyword. Performance improvement is 500% to the original. Query plan does not show any table scans, only index seeks.

I cannot explain this behavior. Can someone help me understand why there is such a dramatic improvement? Isn't the top clause supposed to operate as the last step of the process, so it wouldn't effect performance at all?
ASKER CERTIFIED SOLUTION
Avatar of alexk23
alexk23

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 14 Comments.
See Answers