|
Home > Archive > MS SQL Server > April 2005 > Top 100 takes longer than top 1000??
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Top 100 takes longer than top 1000??
|
|
| barryfz 2005-04-29, 11:23 am |
| I have a query that does several joins and outer joins. When I run it with
top 1000 it returns in about 10 seconds when I run it with top 100 it takes
minutes to return. What could be causing such a problem? BTW, on other
test servers the 100 does take longer than the 1000 but not minutes
difference?????
--
Barry
| |
| Alejandro Mesa 2005-04-29, 11:23 am |
| Did you compare the execution plans to be sure that sql server is using the
same one in both cases?.
AMB
"barryfz" wrote:
> I have a query that does several joins and outer joins. When I run it with
> top 1000 it returns in about 10 seconds when I run it with top 100 it takes
> minutes to return. What could be causing such a problem? BTW, on other
> test servers the 100 does take longer than the 1000 but not minutes
> difference?????
>
> --
> Barry
>
>
>
| |
| David Gugick 2005-04-29, 11:23 am |
| barryfz wrote:
> I have a query that does several joins and outer joins. When I run
> it with top 1000 it returns in about 10 seconds when I run it with
> top 100 it takes minutes to return. What could be causing such a
> problem? BTW, on other test servers the 100 does take longer than
> the 1000 but not minutes difference?????
Apparently, SQL Server has decided to use a different execution plan for
each. Time comparisons don't really tell much and can always be a result
of locking/blocking issues separate from the actual query. It's better
to performance tune by examining the execution plan and actual query
statistics like CPU. Profiler can help a lot here as can QA. You can
examine the execution plan from QA for both queries and report the
results along with the tables and indexes you have. For Profiler, have a
look at the SQL:StmtCompleted and RPC:Completed or SP:StmtCompleted if
this is within a SP. Look at the CPU, Duration, and Reads columns. You
can also examine execution plans from Profiler using the Execution Plan
or any of the Show Plan Events (these require BinaryData be added to the
trace).
You could also try updating the statistics on the tables.
--
David Gugick
Imceda Software
www.imceda.com
|
|
|
|
|