i have found the speed of sort is very slow in my sql (because sql is very complicated, i can't paste it on this page), how can i improve the speed of sort ?
are there better methods?
thks
Look at the execution plan if it shows slow retrieval speed on the ordered columns. If so, you should think about using indexes on the appropiate columns. But you should be able to see this within the execution plan which is a accessible through the Query editor.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||i have saw the execution plan, and i have build index for the table, but i must query result from sub query, the sub query spent most of time. i don't know how to let the sub query more quick?|||The best thing would be *eventually *to use correlated queries, but its hard to tell without knowing the query. Is it not possible to post it in here to help you ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||--Comment: the last aggregate query, it spents 4.44 minutes
--Start for ObjectAgg:LocalNetID,BSCID,SiteID,CellID,StrTime
select
LocalNetID,BSCID,SiteID,CellID,
StrTime,
sum([11601]) as '11601', sum([11602]) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', sum([11606]) as '11606', sum([116060]) as '116060', sum([116061]) as '116061', sum([11607]) as '11607', sum([11608]) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', sum([116131]) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', sum([11616]) as '11616', sum([116160]) as '116160', sum([116161]) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', sum([11627]) as '11627', sum([11628]) as '11628', sum([11629]) as '11629', sum([11630]) as '11630', sum([11631]) as '11631', sum([11632]) as '11632', sum([11633]) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', sum([11670]) as '11670', sum([11671]) as '11671', sum([11672]) as '11672', sum([11673]) as '11673', sum([11674]) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', sum([11681]) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Comment:the second aggreage query, it spends 2 seconds
--Start for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,12)
select
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,12) as StrTime,
avg([11601] * 1.0) as '11601', avg([11602] * 1.0) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', avg([11606] * 1.0) as '11606', sum([116060]) as '116060', avg([116061] * 1.0) as '116061', avg([11607] * 1.0) as '11607', avg([11608] * 1.0) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', avg([116131] * 1.0) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', avg([11616] * 1.0) as '11616', sum([116160]) as '116160', avg([116161] * 1.0) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', max([11627]) as '11627', avg([11628] * 1.0) as '11628', sum([11629]) as '11629', max([11630]) as '11630', avg([11631] * 1.0) as '11631', sum([11632]) as '11632', avg([11633] * 1.0) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', avg([11670] * 1.0) as '11670', avg([11671] * 1.0) as '11671', avg([11672] * 1.0) as '11672', avg([11673] * 1.0) as '11673', avg([11674] * 1.0) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', avg([11681] * 1.0) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Comment:first aggregate query, the speed is very quick and it spends no time
--Start for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,14)
select
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,14) as StrTime,
avg([11601] * 1.0) as '11601', avg([11602] * 1.0) as '11602', sum([11603]) as '11603', sum([11604]) as '11604', sum([11605]) as '11605', sum([11606]) as '11606', sum([116060]) as '116060', avg([116061] * 1.0) as '116061', avg([11607] * 1.0) as '11607', avg([11608] * 1.0) as '11608', sum([11609]) as '11609', sum([11610]) as '11610', sum([116100]) as '116100', sum([116101]) as '116101', sum([116102]) as '116102', sum([116103]) as '116103', sum([116104]) as '116104', sum([116105]) as '116105', sum([116106]) as '116106', sum([116107]) as '116107', sum([116108]) as '116108', sum([116109]) as '116109', sum([11611]) as '11611', sum([116110]) as '116110', sum([116111]) as '116111', sum([116112]) as '116112', sum([116113]) as '116113', sum([116114]) as '116114', sum([116115]) as '116115', sum([116116]) as '116116', sum([116117]) as '116117', sum([116118]) as '116118', sum([116119]) as '116119', sum([11612]) as '11612', sum([116120]) as '116120', sum([116121]) as '116121', sum([116122]) as '116122', sum([116123]) as '116123', sum([116124]) as '116124', sum([116125]) as '116125', sum([116126]) as '116126', sum([116127]) as '116127', sum([116128]) as '116128', sum([116129]) as '116129', sum([11613]) as '11613', sum([116130]) as '116130', sum([116131]) as '116131', sum([116132]) as '116132', sum([116133]) as '116133', sum([116134]) as '116134', sum([116135]) as '116135', sum([116136]) as '116136', sum([11614]) as '11614', sum([11615]) as '11615', sum([11616]) as '11616', sum([116160]) as '116160', avg([116161] * 1.0) as '116161', sum([11617]) as '11617', sum([11618]) as '11618', sum([11619]) as '11619', sum([11620]) as '11620', sum([11621]) as '11621', sum([11622]) as '11622', sum([11623]) as '11623', sum([11624]) as '11624', sum([11625]) as '11625', sum([11626]) as '11626', max([11627]) as '11627', avg([11628] * 1.0) as '11628', sum([11629]) as '11629', max([11630]) as '11630', avg([11631] * 1.0) as '11631', sum([11632]) as '11632', avg([11633] * 1.0) as '11633', sum([11634]) as '11634', sum([11635]) as '11635', sum([11636]) as '11636', sum([11637]) as '11637', sum([11638]) as '11638', sum([11639]) as '11639', sum([11640]) as '11640', sum([11641]) as '11641', sum([11642]) as '11642', sum([11643]) as '11643', sum([11644]) as '11644', sum([11645]) as '11645', sum([11646]) as '11646', sum([11647]) as '11647', sum([11648]) as '11648', sum([11649]) as '11649', sum([11650]) as '11650', sum([11651]) as '11651', sum([11652]) as '11652', sum([11653]) as '11653', sum([11654]) as '11654', sum([11655]) as '11655', sum([11656]) as '11656', sum([11657]) as '11657', sum([11658]) as '11658', sum([11659]) as '11659', sum([11660]) as '11660', sum([11661]) as '11661', sum([11662]) as '11662', sum([11663]) as '11663', sum([11664]) as '11664', sum([11665]) as '11665', sum([11666]) as '11666', sum([11667]) as '11667', sum([11668]) as '11668', sum([11669]) as '11669', avg([11670] * 1.0) as '11670', avg([11671] * 1.0) as '11671', avg([11672] * 1.0) as '11672', avg([11673] * 1.0) as '11673', avg([11674] * 1.0) as '11674', sum([11675]) as '11675', sum([11676]) as '11676', sum([11677]) as '11677', sum([11678]) as '11678', sum([11679]) as '11679', sum([11680]) as '11680', avg([11681] * 1.0) as '11681', sum([11682]) as '11682', sum([11683]) as '11683', sum([11684]) as '11684', sum([11685]) as '11685', sum([11686]) as '11686', sum([11687]) as '11687', sum([11688]) as '11688', sum([11689]) as '11689', sum([11690]) as '11690', sum([11691]) as '11691', sum([11692]) as '11692', sum([11693]) as '11693', sum([11694]) as '11694', sum([11695]) as '11695', sum([11696]) as '11696', sum([11697]) as '11697', sum([11698]) as '11698', sum([11699]) as '11699'
from
(
--Start for first join--
select
coalesce(T1.LocalNetID,NULL)as LocalNetID,coalesce(T1.BSCID,NULL)as BSCID,coalesce(T1.SiteID,NULL)as SiteID,coalesce(T1.CellID,NULL)as CellID,
coalesce(T1.StrTime,NULL) as StrTime,
C11601 as '11601',C11602 as '11602',C11603 as '11603',C11604 as '11604',C11605 as '11605',C11606 as '11606',I116061 as '116060',C11606 as '116061',C11607 as '11607',C11608 as '11608',C11609 as '11609',C11610 as '11610',C116100 as '116100',C116101 as '116101',C116102 as '116102',C116103 as '116103',C116104 as '116104',C116105 as '116105',C116106 as '116106',C116107 as '116107',C116108 as '116108',C116109 as '116109',C11611 as '11611',C116110 as '116110',C116111 as '116111',C116112 as '116112',C116113 as '116113',C116114 as '116114',C116115 as '116115',C116116 as '116116',C116117 as '116117',C116118 as '116118',C116119 as '116119',C11612 as '11612',C116120 as '116120',C116121 as '116121',C116122 as '116122',C116123 as '116123',C116124 as '116124',C116125 as '116125',C116126 as '116126',C116127 as '116127',C116128 as '116128',C116129 as '116129',C11613 as '11613',C116130 as '116130',C116131 as '116131',C116132 as '116132',C116133 as '116133',C116134 as '116134',C116135 as '116135',C116136 as '116136',C11614 as '11614',C11615 as '11615',C11616 as '11616',I116161 as '116160',C11616 as '116161',C11617 as '11617',C11618 as '11618',C11619 as '11619',C11620 as '11620',C11621 as '11621',C11622 as '11622',C11623 as '11623',C11624 as '11624',C11625 as '11625',C11626 as '11626',C11627 as '11627',C11628 as '11628',C11629 as '11629',C11630 as '11630',C11631 as '11631',C11632 as '11632',C11633 as '11633',C11634 as '11634',C11635 as '11635',C11636 as '11636',C11637 as '11637',C11638 as '11638',C11639 as '11639',C11640 as '11640',C11641 as '11641',C11642 as '11642',C11643 as '11643',C11644 as '11644',C11645 as '11645',C11646 as '11646',C11647 as '11647',C11648 as '11648',C11649 as '11649',C11650 as '11650',C11651 as '11651',C11652 as '11652',C11653 as '11653',C11654 as '11654',C11655 as '11655',C11656 as '11656',C11657 as '11657',C11658 as '11658',C11659 as '11659',C11660 as '11660',C11661 as '11661',C11662 as '11662',C11663 as '11663',C11664 as '11664',C11665 as '11665',C11666 as '11666',C11667 as '11667',C11668 as '11668',C11669 as '11669',C11670 as '11670',C11671 as '11671',C11672 as '11672',C11673 as '11673',C11674 as '11674',C11675 as '11675',C11676 as '11676',C11677 as '11677',C11678 as '11678',C11679 as '11679',C11680 as '11680',C11681 as '11681',C11682 as '11682',C11683 as '11683',C11684 as '11684',C11685 as '11685',C11686 as '11686',C11687 as '11687',C11688 as '11688',C11689 as '11689',C11690 as '11690',C11691 as '11691',C11692 as '11692',C11693 as '11693',C11694 as '11694',C11695 as '11695',C11696 as '11696',C11697 as '11697',C11698 as '11698',C11699 as '11699'from
--Start for Opt_Cell_pBasicM of inner table--
(
select
LocalNetID,BSCID,SiteID,CellID,
StrTime,RecordDataTime,
C11601,C11602,C11603,C11604,C11605,C11606,I116061,C11607,C11608,C11609,C11610,C116100,C116101,C116102,C116103,C116104,C116105,C116106,C116107,C116108,C116109,C11611,C116110,C116111,C116112,C116113,C116114,C116115,C116116,C116117,C116118,C116119,C11612,C116120,C116121,C116122,C116123,C116124,C116125,C116126,C116127,C116128,C116129,C11613,C116130,C116131,C116132,C116133,C116134,C116135,C116136,C11614,C11615,C11616,I116161,C11617,C11618,C11619,C11620,C11621,C11622,C11623,C11624,C11625,C11626,C11627,C11628,C11629,C11630,C11631,C11632,C11633,C11634,C11635,C11636,C11637,C11638,C11639,C11640,C11641,C11642,C11643,C11644,C11645,C11646,C11647,C11648,C11649,C11650,C11651,C11652,C11653,C11654,C11655,C11656,C11657,C11658,C11659,C11660,C11661,C11662,C11663,C11664,C11665,C11666,C11667,C11668,C11669,C11670,C11671,C11672,C11673,C11674,C11675,C11676,C11677,C11678,C11679,C11680,C11681,C11682,C11683,C11684,C11685,C11686,C11687,C11688,C11689,C11690,C11691,C11692,C11693,C11694,C11695,C11696,C11697,C11698,C11699
from
Opt_Cell_pBasicM
where
((LocalNetID = 1 and BSCID = 5))
and
((RecordDataTime between '2006-11-16 00:01:00' and '2006-11-16 13:00:00'))
and
(GranularityPeriod <= 12)
)
--End for Opt_Cell_pBasicM of inner table--
as T1
--End for first join--
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,14)
--End for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,14)
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
left(StrTime,12)
--End for TimeAgg:LocalNetID,BSCID,SiteID,CellID,left(StrTime,12)
) as T
group by
LocalNetID,BSCID,SiteID,CellID,
StrTime
--the above sql's execution time is got when there is no data in table Opt_Cell_pBasicM
--because of no data in table, so i am very curious why the last aggregate query need so
--long time, i have saw the execution plan and found the time is spent on sort, but there is
--no data, why to sort?
No comments:
Post a Comment