Оптимизация запроса

BUTCH

помогите с запросом не охота ждать 70 дней declare @p1 intdeclare @i intset @i=0declare c1 cursor for select distinct id from #z order by id open c1 fetch c1 into @p1 while @@FETCH_STATUS=0 begin insert #c select id,@p1,((n*s)-(sx*sy))/(sqrt(((n*sx2)-sx*sx)*((n*sy2)-(sy*sy)))) from ( select t.id,SUM(t.dol2) sx,SUM(t.dol2*t.dol2)sx2,SUM(z2.dol) sy,SUM(z2.dol*z2.dol)sy2,SUM(t.dol2*z2.dol) s,COUNT(*) n from #te2 t left join (select classid,seg,dol from #z2 where id=@p1 ) z2 on z2.classid=t.classid and z2.seg=t.seg where z2.classid is not null or t.dol2 is not null group by t.id)a set @i=@i+1 print @i fetch next from c1 into @p1 end close c1 deallocate c1подготовка данныхselect m.id,l.classid,p.seg,COUNT(c.id) klinesinto #z----......select z.*,cast(z.klines as float)/z2.kl dolinto #z2from #z zjoin (select ID ,classid,SUM(klines) kl from #z group by id,classid) z2 on z2.id=z.id and z2.classid=z.classid ----- select * into #te from (select distinct ID from #z2) z1,(select distinct classid from #z2) z2,(select distinct seg from #z2) z3 order by k descselect t.*,z.dol dol2 into #te2 from #te t left join #z2 z on z.id=t.id and z.classid=t.classid and z.seg=t.segуже пару дней мучаюсь больше 1000 корреляций в секунду не получается (рассчитываю http://psystat.at.ua/publ/1-1-0-26 )
7 ответов

BUTCH

Оформите что ли по прапвилам...Скрипты на создание временных таблиц приведите.Ну и главное - просто откажитесь от использования курсора.


BUTCH

спасибо за рекомендации тестовые данные :create table #z (id int,classid int,seg int,k int)insert into #z select 4032257 , 101 , 2 , 2insert into #z select 4039829 , 478 , 3 , 3insert into #z select 1441184 , 359 , 1 , 85insert into #z select 813429 , 482 , 5 , 2insert into #z select 2813529 , 242 , 3 , 1insert into #z select 4517647 , 477 , 1 , 3insert into #z select 3060979 , 252 , 2 , 16insert into #z select 4814807 , 352 , 2 , 1insert into #z select 4848235 , 45 , 2 , 1insert into #z select 3056144 , 393 , 4 , 2insert into #z select 3658233 , 53 , 1 , 2insert into #z select 2960923 , 1591 , 3 , 5insert into #z select 730817 , 103 , 2 , 2insert into #z select 804030 , 26 , 2 , 9insert into #z select 3082858 , 298 , 2 , 4insert into #z select 2160208 , 429 , 1 , 11insert into #z select 4507103 , 214 , 2 , 2insert into #z select 1426733 , 85 , 2 , 5insert into #z select 4275486 , 85 , 3 , 2insert into #z select 565532 , 321 , 5 , 2insert into #z select 2214184 , 182 , 2 , 1insert into #z select 4534980 , 69 , 5 , 6insert into #z select 3579352 , 252 , 5 , 1insert into #z select 823148 , 321 , 3 , 3insert into #z select 57574 , 393 , 3 , 2insert into #z select 4646771 , 92 , 2 , 1insert into #z select 1919455 , 49 , 1 , 5insert into #z select 2490026 , 254 , 2 , 12insert into #z select 1098610 , 85 , 4 , 7insert into #z select 586462 , 242 , 4 , 1insert into #z select 2360544 , 303 , 2 , 3insert into #z select 1324163 , 218 , 1 , 2insert into #z select 983282 , 280 , 2 , 2insert into #z select 1220148 , 264 , 1 , 4insert into #z select 1842170 , 175 , 2 , 1insert into #z select 3220467 , 85 , 1 , 1insert into #z select 3830956 , 173 , 2 , 1insert into #z select 3643346 , 370 , 2 , 1insert into #z select 2841922 , 154 , 4 , 1insert into #z select 2474410 , 87 , 1 , 4insert into #z select 1377726 , 44 , 2 , 6insert into #z select 1411616 , 154 , 1 , 1******* into #z select 1707735 , 467 , 3 , 2insert into #z select 5250564 , 215 , 2 , 3insert into #z select 1373122 , 147 , 2 , 1insert into #z select 527361 , 112 , 1 , 2insert into #z select 3106810 , 469 , 2 , 1insert into #z select 3856941 , 103 , 5 , 1insert into #z select 3909001 , 181 , 3 , 4insert into #z select 3794676 , 57 , 1 , 4insert into #z select 1130219 , 422 , 3 , 2insert into #z select 3319626 , 138 , 3 , 2insert into #z select 674947 , 246 , 4 , 1insert into #z select 3410546 , 393 , 3 , 1insert into #z select 1234698 , 450 , 1 , 3insert into #z select 3910793 , 429 , 3 , 4insert into #z select 2521240 , 450 , 1 , 1insert into #z select 141041 , 154 , 2 , 4insert into #z select 2756601 , 45 , 2 , 6insert into #z select 2906465 , 218 , 2 , 2insert into #z select 1491603 , 478 , 3 , 1insert into #z select 1612146 , 184 , 4 , 4insert into #z select 5439093 , 16 , 2 , 2insert into #z select 543672 , 196 , 4 , 1insert into #z select 629138 , 489 , 1 , 13insert into #z select 1615319 , 63 , 3 , 4insert into #z select 4527784 , 176 , 1 , 4insert into #z select 4018568 , 154 , 2 , 4insert into #z select 2544915 , 462 , 2 , 2insert into #z select 2794029 , 205 , 4 , 1insert into #z select 513119 , 44 , 1 , 1insert into #z select 1123630 , 242 , 2 , 7insert into #z select 1414605 , 106 , 2 , 4insert into #z select 1398175 , 16 , 2 , 1insert into #z select 3595999 , 87 , 4 , 2insert into #z select 4893308 , 1459 , 3 , 1insert into #z select 324462 , 112 , 5 , 1insert into #z select 5720248 , 9 , 2 , 2insert into #z select 3765899 , 343 , 2 , 1insert into #z select 2998078 , 468 , 3 , 5insert into #z select 3645454 , 298 , 2 , 1insert into #z select 1672553 , 85 , 5 , 1insert into #z select 2905946 , 321 , 3 , 1insert into #z select 3787677 , 385 , 2 , 1insert into #z select 681525 , 173 , 3 , 2insert into #z select 997440 , 92 , 2 , 14insert into #z select 3211112 , 57 , 2 , 3insert into #z select 724884 , 365 , 2 , 1insert into #z select 4136335 , 270 , 3 , 2insert into #z select 2193154 , 215 , 1 , 1insert into #z select 4313777 , 351 , 2 , 1insert into #z select 5274302 , 351 , 1 , 1insert into #z select 3646534 , 478 , 1 , 1insert into #z select 2921945 , 350 , 4 , 1insert into #z select 1743606 , 231 , 2 , 2insert into #z select 5642094 , 479 , 2 , 2insert into #z select 3854832 , 39 , 4 , 2insert into #z select 1311916 , 364 , 3 , 1insert into #z select 3434770 , 412 , 2 , 8insert into #z select 2525527 , 287 , 1 , 6select z.*,cast(z.k as float)/z2.kl dolinto #z2from #z zjoin (select ID ,classid,SUM(k) kl from #z group by id,classid) z2 on z2.id=z.id and z2.classid=z.classid select * into #te from (select distinct ID from #z2) z1,(select distinct classid from #z2) z2,(select distinct seg from #z2) z3 select t.*,z.dol dol2 into #te2 from #te t left join #z2 z on z.id=t.id and z.classid=t.classid and z.seg=t.segеще забыл создать #сcreate table #c (id1 int,id2 int ,k float)


BUTCH

м курсор используется только как переменная !!!!


BUTCH

1. Нафиг там курсор?
insert #c
select id, [@p1],((n*s)-(sx*sy))/(sqrt(((n*sx2)-sx*sx)*((n*sy2)-(sy*sy))))
from (
select t.id, z2.id as [@p1], SUM(t.dol2) sx,SUM(t.dol2*t.dol2)sx2,SUM(z2.dol) sy,SUM(z2.dol*z2.dol)sy2,SUM(t.dol2*z2.dol) s,COUNT(*) n
from #te2 t 
left join #z2 z2 on z2.classid=t.classid and z2.seg=t.seg 
where z2.classid is not null or t.dol2 is not null -- ??? весьма странный фильтр
group by t.id, z2.id)a
2. where z2.classid is not null or t.dol2 is not null очень странно смотрится на фоне вычислений с z2.dol...


BUTCH

необходимо рассчитать отношение каждого ид к каждому is not null необходимо для count и для суммы произведений так как необходимо учитывать null,0


BUTCH

aleks2,так у вас увеличится количество строк в count(distinct id) раз


BUTCH

aleks2,так у вас увеличится количество строк в count(distinct id) раз
Шо вы говорите?!