Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2003-12-17 : 11:11:11
|
| [code]select top 1000 * FROM dbo.tblAanleveringVerzuim av JOIN AAB.dbo.humres h ON h.Freefield2 = av.PersoneelsnummerJOIN AAB.dbo.absences a ON a.EmpId = h.res_id AND a.Type = 100 and a.FreeTextField_01 = av.Referentie [/code] I'm a dumbfounded.If I run this code on my SQL it runs in less than a second.If I use TOP 10 * instead of TOP 1000 than it takes 11 seconds.(If I put an Index on a.FreeTextField_01 it's fast in both cases)Can anyone explain to me this big difference??Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-12-17 : 11:20:07
|
| First guess would be that the TOP 10 is using a loop join that's turning out -- against the optimizer's expectation -- to be more expensive than a merge or hash join used by the TOP 1000 query.Do the query plans differ in the join physical operator being used? |
 |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2003-12-17 : 11:30:47
|
| Jee,Thx. Indeed the execution plan is different. There's a cost of 100% (with a thick marker) and 2 nested loops in the TOP 10. In the top 100 there's only one, but with parallelisms before the 'merge'I used the top 10 just to see fast results and testing. In the original code there's no TOP x in the query. Would that be good performance wise? Or will probably the TOP 1000 execution plan be used?Thx, for your response...Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-17 : 12:02:22
|
quote:
Originally posted by Arnold Fribble
First guess would be that the TOP 10 is using a loop join that's turning out -- against the optimizer's expectation -- to be more expensive than a merge or hash join used by the TOP 1000 query.Do the query plans differ in the join physical operator being used?
Impressive Arnold...But how come your doing TOP without an ORDER BY?I bet the plans become the same...Brett8-) |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2003-12-17 : 12:18:00
|
| Brett, you r right about using an ORDER BY when using a TOP. Because of the testing I didn't bother to use an ORDER BY.However, the execution plans differ. The TOP 10 is now the faster one, and fast (1 sec). The TOP 1000 is slow and cost me 20 seconds. The execution plans are quiet different.Interesting stuff...Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
|
|
|
|
SELECT TOP 1000 faster than TOP 10