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: Francisco on 12 May 2010 05:19 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
From: Uri Dimant on 12 May 2010 05:40 Francisco What does the execution plan say? For TOP clause the optimzer has to sort the data and if you do not have efficient index it can be performance hit "Francisco" <fjgonzalez(a)arion.es> wrote in message news:5ebb12a8-23a6-40f5-8c0b-7dfd179375e8(a)r11g2000yqa.googlegroups.com... 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
From: Dan Guzman on 12 May 2010 08:04 Take a look at the execution plans. That will show the reason for the performance differences. > CREATE NONCLUSTERED INDEX HPHPCREL_05 > ON dbo.HPHPCREL (CPNY_COD,PERS_COD,END_DAT) To optimize your queries with/without TOP, I think you'll need a clustered index since you are selecting all 236 columns. CREATE CLUSTERED INDEX HPHPCREL_CI ON dbo.HPHPCREL (CPNY_COD,PERS_COD,END_DAT DESC) -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Francisco" <fjgonzalez(a)arion.es> wrote in message news:5ebb12a8-23a6-40f5-8c0b-7dfd179375e8(a)r11g2000yqa.googlegroups.com... > 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
From: Gert-Jan Strik on 13 May 2010 08:42 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
From: Francisco on 14 May 2010 04:22
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 |