Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 optimization sqlquery  [new]
framework
Member

Откуда:
Сообщений: 16
Подскажите как можно оптимизировать данный запрос?
План выполнения: https://drive.google.com/open?id=1-MUz-GvpsEpzCLGsIeGzOyL_QNwZLM0J

+
declare @date datetime = '20180405';
With 
CTEGoodsLogistic as
(
select gl.WarehouseId, gl.GoodsId, gl.QuantityMin
from GoodsLogistic gl with(NoLock) 
where  gl.GoodsId in (400318,400319)
and gl.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and gl.QuantityMin<>0
),
CTEPromotion as
(
select pwg.WarehouseId, pwg.GoodsId, pwg.RestQuantity
from PromotionWarehouseGoods pwg with(NoLock)
left join PromotionGroup p with(NoLock) on pwg.PromotionGroupId=p.PromotionGroupId
left join PromotionGoods pg with(NoLock) on pwg.PromotionGroupId=p.PromotionGroupId
where pwg.GoodsId in (400318,400319) --:Edit Товары
and pwg.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and p.PromotionGroupTimeBegin <= @date and p.PromotionGroupTimeEnd >= @date
and pg.PromotionGoodsTimeBegin<=@date and pg.PromotionGoodsTimeEnd>= @date
group by pwg.WarehouseId, pwg.GoodsId, pwg.RestQuantity
),
CTECredit as
(
select c.WarehouseId, c.GoodsId, SUM(c.Quantity) CreditQuantity
from CreditCurrentViewCompact c with(NoLock) 
where c.Datex between dateadd(day,-7, @date) and @date
and c.GoodsId in (400318,400319) --:Edit Товары
and c.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and c.WhatBase in (11,21,22)
and c.Quantity<>0
group by c.WarehouseId, c.GoodsId
),
CTERest as
(
select r.WarehouseId, r.GoodsId, SUM(r.Quantity) Quantity
from RestView r with(NoLock) 
where @date between r.Datex and r.DateEnd
and r.GoodsId in (400318,400319) 
and r.Quantity<>0
and r.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
group by r.WarehouseId, r.GoodsId
),
CTERestTo as
(
select r.WarehouseId, r.GoodsId, sum(r.Quantity) OrderQuantityTo
from OrderSaleGoodsView r with(NoLock) 
where 1=1
and r.GoodsId in (400318,400319) 
and r.Quantity<>0
and r.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
group by r.WarehouseId, r.GoodsId
),
CTERestToWay as
(
select w.WarehouseIdParent WarehouseId, r.GoodsId, sum(r.Quantity) RestQuantityToWay
from Rest r with(NoLock), Warehouse w with(NoLock)
where  r.GoodsId in (400318,400319) 
and r.WarehouseId=w.WarehouseId
and w.WarehouseIdParent in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095) --:Edit Склады
and w.WarehouseTypeId in (301)
and @date between r.Datex and r.DateEnd
and r.Quantity<>0
group by w.WarehouseIdParent, r.GoodsId
),
CTERestFrom as
(
select r.WarehouseId, r.GoodsId, sum(r.Quantity) OrderQuantityFrom
from OrderSaleGoodsView r with(NoLock) 
where r.GoodsId in (400318,400319)
and r.WarehouseId in (1500)
and r.Quantity<>0
group by r.WarehouseId, r.GoodsId
),
CTERestMain as
(
select r.GoodsId, SUM(r.Quantity) RestQuantityMain, SUM(OrderQuantityFrom) OrderQuantityFromMain
from RestView r with(NoLock) 
left join CteRestFrom rf on rf.GoodsId=r.GoodsId and rf.WarehouseId=r.WarehouseId
where r.GoodsId in (400318,400319) 
and r.WarehouseId in (1500)
and @date between r.Datex and r.DateEnd
and r.Quantity<>0
group by r.WarehouseId, r.GoodsId
),
CTEPlan as
(
select                               
x.WarehouseId, x.GoodsId, x.GoodsPlanType, x.GoodsPlanStatus,
sum(x.QuantityMin) over (partition by x.GoodsId) SumLogisticAll,
sum(max(x.RestQuantity)) over (partition by x.GoodsId) SumAMDAll,
count (x.WarehouseId) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) CountWarehouse,
sum(x.QuantityMin) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumLogistic,
sum(max(x.RestQuantity)) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumAMD,
sum(x.CreditQuantity) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumCredit,
sum(x.Quantity) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestBegin,
sum(x.OrderQuantityTo) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestQuantityTo,
sum(x.RestQuantityToWay) over (partition by x.GoodsId,x.GoodsPlanType, x.GoodsPlanStatus) SumRestQuantityToWay,
sum(x.Quantity) over (partition by x.GoodsId) SumRestBeginAll,
sum(x.OrderQuantityTo) over (partition by x.GoodsId) SumRestQuantityToAll,
sum(x.RestQuantityToWay) over (partition by x.GoodsId) SumRestQuantityToWayAll
from 
(
select wf.WarehouseId, gpr.GoodsId, IsNull(glc.QuantityMin,0) QuantityMin, IsNull(pc.RestQuantity,0) RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id Wfgg2GoodsGroup2Id
from WarehouseFormat wf With(NoLock)
inner join GoodsPlanRunGroup gprg With(NoLock) on gprg.GoodsPlanType=wf.GoodsPlanType and gprg.GoodsPlanStatus=wf.GoodsPlanStatus
inner join GoodsPlanRun gpr With(NoLock) on gpr.GoodsPlanRunGroupId=gprg.GoodsPlanRunGroupId
inner join WarehouseFormatGoodsGroup2 wfgg2 With(NoLock) on wfgg2.WarehouseFormatId=wf.WarehouseFormatId
left join CTEGoodsLogistic glc With(NoLock) on gpr.GoodsId=glc.GoodsId and wf.WarehouseId=glc.WarehouseId
left join CTEPromotion pc  With(NoLock) on gpr.GoodsId=pc.GoodsId and wf.WarehouseId=pc.WarehouseId
left join CTECredit cc With(NoLock) on gpr.GoodsId=cc.GoodsId and wf.WarehouseId=cc.WarehouseId
left join CTERest rc With(NoLock) on gpr.GoodsId=rc.GoodsId and wf.WarehouseId=rc.WarehouseId
left join CTERestTo rtc With(NoLock) on gpr.GoodsId=rtc.GoodsId and wf.WarehouseId=rtc.WarehouseId
left join CTERestToWay rtwc With(NoLock) on gpr.GoodsId=rtwc.GoodsId and wf.WarehouseId=rtwc.WarehouseId
left join CTERestMain rmc With(NoLock) on gpr.GoodsId=rmc.GoodsId 
where 1=1      
  and wfgg2.GoodsGroup2Id=-1
  and @date between gprg.DateStart and gprg.DateEnd
  and gpr.GoodsId in (400318,400319)
  and wf.WarehouseId in (281,312,314,328,334,356,372,388,401,450,452,456,476,479,486,523,539,577,710,714,726,730,736,740,806,818,826,874,884,985) 
group by wf.WarehouseId, gpr.GoodsId, glc.QuantityMin, pc.RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id  
union All
select 
wf.WarehouseId, gpr.GoodsId, IsNull(glc.QuantityMin,0) QuantityMin, IsNull(pc.RestQuantity,0) RestQuantity, cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id Wfgg2GoodsGroup2Id
from WarehouseFormat wf With(NoLock)
inner join GoodsPlanRunGroup gprg With(NoLock) on gprg.GoodsPlanType=wf.GoodsPlanType and gprg.GoodsPlanStatus=wf.GoodsPlanStatus
inner join GoodsPlanRun gpr With(NoLock) on gpr.GoodsPlanRunGroupId=gprg.GoodsPlanRunGroupId
inner join WarehouseFormatGoodsGroup2 wfgg2 With(NoLock) on wfgg2.WarehouseFormatId=wf.WarehouseFormatId
left join CTEGoodsLogistic glc With(NoLock) on gpr.GoodsId=glc.GoodsId and wf.WarehouseId=glc.WarehouseId
left join CTEPromotion pc  With(NoLock) on gpr.GoodsId=pc.GoodsId and wf.WarehouseId=pc.WarehouseId
left join CTECredit cc With(NoLock) on gpr.GoodsId=cc.GoodsId and wf.WarehouseId=cc.WarehouseId
left join CTERest rc With(NoLock) on gpr.GoodsId=rc.GoodsId and wf.WarehouseId=rc.WarehouseId
left join CTERestTo rtc With(NoLock) on gpr.GoodsId=rtc.GoodsId and wf.WarehouseId=rtc.WarehouseId
left join CTERestToWay rtwc With(NoLock) on gpr.GoodsId=rtwc.GoodsId and wf.WarehouseId=rtwc.WarehouseId
left join CTERestMain rmc With(NoLock) on gpr.GoodsId=rmc.GoodsId
where 1=1
  and gprg.GoodsGroup2Id=wfgg2.GoodsGroup2Id
  and @date between gprg.DateStart and gprg.DateEnd
  and gpr.GoodsId in (400318,400319) 
  and wf.WarehouseId in (34,35,37,132,143,144,157,167,168,173,174,175,176,188,190,198,208,209,219,228,229,230,266,269,270,272,273,274,276,279,291,292,293,294,295,296,297,298,299,300,301,302,303,304,306,308,309,310,311,312,315,317,321,322,323,324,325,331,332,337,338,339,341,342,343,344,347,348,349,350,351,352,354,358,359,361,362,363,364,366,368,369,370,373,375,376,379,380,382,386,387,389,391,392,393,394,397,399,402,403,404,405,408,412,413,414,415,416,417,418,427,428,432,433,434,435,437,438,439,440,442,444,445,448,453,454,455,456,457,458,459,460,462,463,464,465,466,467,468,472,474,475,476,477,478,479,480,481,482,484,485,486,487,488,489,491,495,496,497,498,499,503,507,508,509,511,512,515,518,519,522,525,527,528,529,531,532,534,547,548,549,550,552,554,555,556,557,558,560,561,563,564,566,567,570,571,572,573,574,575,580,581,582,584,585,586,587,589,591,592,594,595,596,598,599,605,607,609,610,613,616,618,622,624,626,627,629,635,636,637,638,639,647,655,657,659,661,662,664,667,668,675,677,678,681,687,689,691,693,700,710,712,714,716,718,720,722,724,726,728,730,732,734,736,738,740,750,760,770,775,777,780,790,804,805,808,814,815,816,818,819,820,821,823,824,833,834,840,841,847,850,852,854,856,858,859,860,861,862,863,864,868,871,872,874,878,882,886,890,891,892,894,895,896,902,910,911,913,915,918,919,922,923,927,929,933,934,935,946,948,949,950,952,955,956,958,959,962,963,964,966,977,978,983,984,986,999,6004,6009,6014,6015,6018,6023,6024,6025,6026,6027,6030,6031,6033,6037,6038,6039,6040,6041,6047,6048,6050,6052,6057,6060,6061,6062,6063,6064,6070,6071,6072,6074,6077,6078,6079,6086,6088,6090,6093,6095)
group by wf.WarehouseId, gpr.GoodsId, glc.QuantityMin, pc.RestQuantity,cc.CreditQuantity,
rc.Quantity, rtc.OrderQuantityTo, rtwc.RestQuantityToWay, gprg.GoodsGroup2Id, gprg.GoodsPlanType, gprg.GoodsPlanStatus, wfgg2.GoodsGroup2Id 
) x
group by x.WarehouseId, x.GoodsId, x.GoodsPlanType, x.GoodsPlanStatus, x.QuantityMin, x.Quantity, x.OrderQuantityTo, x.RestQuantityToWay,x.RestQuantity, x.CreditQuantity
)

select g.GoodsId, g.GoodsName, wf.GoodsPlanType, wf.GoodsPlanStatus, cp.CountWarehouse, cp.SumLogistic, cp.SumAMD, IsNull(cp.SumCredit,0) SumCredit,
IsNull(cp.SumRestBegin,0) SumRestBegin, IsNull(cp.SumRestQuantityTo,0) SumRestQuantityTo, IsNull(cp.SumRestQuantityToWay,0) SumRestQuantityToWay,
(IsNull(cp.SumRestBegin,0)+IsNull(cp.SumRestQuantityTo,0)+IsNull(cp.SumRestQuantityToWay,0)) RestAll1,
(rmc.RestQuantityMain+rmc.OrderQuantityFromMain) RestMain,
(IsNull(cp.SumRestBeginAll,0)+IsNull(cp.SumRestQuantityToAll,0)+IsNull(cp.SumRestQuantityToWayAll,0)+IsNull(rmc.RestQuantityMain,0)+IsNull(rmc.OrderQuantityFromMain,0)) RestAll2, 
cp.SumLogisticAll, cp.SumAMDAll
from WarehouseFormat wf With(NoLock), Goods g With(NoLock) 
left join CTEPlan cp on g.GoodsId=cp.GoodsId
left join CTERestMain rmc on g.GoodsId=rmc.GoodsId
where 1=1
and cp.GoodsPlanType=wf.GoodsPlanType and cp.GoodsPlanStatus=wf.GoodsPlanStatus
and g.GoodsId in (400318,400319)
group by g.GoodsId, g.GoodsName, wf.GoodsPlanType, wf.GoodsPlanStatus, cp.CountWarehouse, cp.SumLogistic, cp.SumCredit, cp.SumAMD, cp.SumRestBegin, cp.SumRestQuantityTo, 
cp.SumRestQuantityToWay, cp.SumLogisticAll, rmc.RestQuantityMain, rmc.OrderQuantityFromMain,cp.SumRestBeginAll,cp.SumRestQuantityToAll,cp.SumRestQuantityToWayAll,cp.SumAMDAll
order by g.GoodsId, wf.GoodsPlanType, wf.GoodsPlanStatus


Сообщение было отредактировано: 26 июл 18, 13:28
26 июл 18, 13:26    [21604182]     Ответить | Цитировать Сообщить модератору
 Re: optimization sqlquery  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
framework,

Пепепишите с использованием временных таблиц.
CTE - синтаксический сахар, так что попробуйте всё написанное развернуть и прикинуть, какой ужас приходится обрабатывать серверу. Иногда CTE очень полезная штука, но без фанатизма.
30 июл 18, 22:28    [21616285]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить