Select TOP on SQL 2005 slower than select full
in [Server Configuration]
|
Prev: Can we access SQL Server 2008 R2 Database Engine with other ManagementStudio ?
Next: Can we access SQL Server 2008 R2 Database Engine with other Management Studio ?
From: Gert-Jan Strik on 14 May 2010 04:50 Francisco wrote: > > On 13 mayo, 14:42, Gert-Jan Strik <sorrytoomuchspamalre...(a)xs4all.nl> > wrote: > > Well, obviously the query plans are different. Most likely the 1s and 8s > > versions use a nonclustered index. The 29s version probably scans the > > clustered index. > > > > Make sure your statistics are up to date. When in doubt, run UPDATE > > STATISTICS .. WITH FULLSCAN. If the statistics are off, the optimizer > > could underestimate the cost of scanning the entire table, causing > > suboptimal query plans. > > > > You mention that changing the sorting from "CPNY_COD,PERS_COD,END_DAT > > DESC" to "CPNY_COD,PERS_COD,END_DAT ASC" did not change the performance. > > I was somewhat surprised about that. For my curiousity, what happens if > > you add the following index? Is it used in the query (instead of the > > current use of INDEX HPHPCREL_05)? > > > > CREATE NONCLUSTERED INDEX HPHPCREL_07 > > ON dbo.HPHPCREL (CPNY_COD) > > > > -- > > Gert-Jan > > > > > > > > Francisco wrote: > > > > > Hello, > > > > > �Why narrowing results over 63 rows with top is far slower than a > > > full select? > > > My problem can be synthesized in 3 querys. > > > > > This select runs in 29s: > > > > > SELECT TOP 63 * FROM HPHPCREL where ( CPNY_COD = 'ATL') ORDER BY > > > CPNY_COD,PERS_COD,END_DAT DESC > > > > > The same select without TOP runs in 8s (returning 66642 rows): > > > > > SELECT * FROM HPHPCREL where ( CPNY_COD = 'ATL') ORDER BY > > > CPNY_COD,PERS_COD,END_DAT DESC > > > > > Again the first select for 62 rows runs in less than a second: > > > > > SELECT TOP 62 * FROM HPHPCREL where ( CPNY_COD = 'ATL') ORDER BY > > > CPNY_COD,PERS_COD,END_DAT DESC > > > > > HPHPCREL has 126623 rows with 236 columns each (4643 row size > > > estimated, 7470 row size max). > > > Indexes: > > > > > CREATE NONCLUSTERED INDEX HPHPCREL_02 > > > ON dbo.HPHPCREL (PERS_COD,BEG_DAT,END_DAT) > > > > > CREATE NONCLUSTERED INDEX HPHPCREL_03 > > > ON dbo.HPHPCREL (CPNY_COD,EMPLOY_COD,REFREL_COD,BEG_DAT,END_DAT) > > > > > CREATE NONCLUSTERED INDEX HPHPCREL_05 > > > ON dbo.HPHPCREL (CPNY_COD,PERS_COD,END_DAT) > > > > > CREATE NONCLUSTERED INDEX HPHPCREL_06 > > > ON dbo.HPHPCREL (FISCAL_ID,CPNY_COD,BEG_DAT,END_DAT) > > > > > Changing the sort to asc, doesn't change anything. > > > > > Thanks in advance- Ocultar texto de la cita - > > > > - Mostrar texto de la cita - > > Gert-Jan Strik, rebuilding those three indexes doesn't help, your > proposed index gives me an estimated improvement of 5%. > The original execution plans: see > http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3c6ae39e-4dd3-4fc4-83ed-474756887204 You could try adding the query hint OPTION (FAST 60). As example I put in 60, but you can play around with it. I saw the tip of just selecting the row identifiers of the TOP 63 rows in a derived table. If that works, that is a possibility as well, but has the risk that the next developer will not understand why you put this in. If OPTION (FAST x) doesn't work, then I would seriously consider adding an index hint in de FROM clause, such as WITH (INDEX=HPHPCREL_05). Or look at the possibility to use the USE PLAN query hint to force a prerecorded xml_plan. -- Gert-Jan
From: Gert-Jan Strik on 14 May 2010 04:54
Francisco wrote: > Gert-Jan Strik, rebuilding those three indexes doesn't help, your > proposed index gives me an estimated improvement of 5%. > The original execution plans: see > http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3c6ae39e-4dd3-4fc4-83ed-474756887204 FYI, the way the execution plan is 'published' there doesn't work for me. If you think it is important, then please attach it (which this newsgroup will allow), or simply post the text plan that you get if you use SET SHOWPLAN_TEXT ON GO --run my query GO SET SHOWPLAN_TEXT OFF -- Gert-Jan |