안녕하세요 다름이 아니라 쿼리 튜닝 관련하여 질문 올립니다
-- 테이블 모양 -------------------------------------------------------
CREATE TABLE [dbo].[i_Delivery2] (
[dBillNo] [char] (6) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[dProdID] [char] (8) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[dDatecode] [char] (6) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[dSerialcode] [char] (5) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[dSaleUnitCost] [int] NULL ,
[dTransCharge] [int] NULL ,
[dEtcCharge] [int] NULL ,
[dMargin] [int] NULL ,
[dDivision] [char] (1) COLLATE Korean_Wansung_CI_AS NULL ,
[dHistory] [varchar] (255) COLLATE Korean_Wansung_CI_AS NULL ,
[dIsOK] [char] (1) COLLATE Korean_Wansung_CI_AS NULL ,
[dOKManNo] [smallint] NULL ,
[dMemo] [varchar] (255) COLLATE Korean_Wansung_CI_AS NULL
) ON [PRIMARY]
----------------------------------------------------------------------
아래 쿼리에서 한줄 일어 오는데 약 14초~15 초 걸립니다 대략 난감 ㅡㅡ;;
방법 아시는 분들 알려주세요~~
SELECT
CONVERT(char(10),dd.dDate,102) AS 출고일자
, c.cName AS 거래처
, b.cTmpCode AS 기존코드
, p.pID AS 제품코드
, p.pName AS 제품명
, COUNT(p.pID) AS 출고수량
, s.sBuyUnitCost AS 입고단가
, d.dSaleUnitCost AS 출고단가
, (d.dTransCharge + d.dEtcCharge) AS 출고비용
, (d.dSaleUnitCost - s.sBuyUnitCost ) AS 이익금
, CAST(
CAST(d.dSaleUnitCost - s.sBuyUnitCost as real ) *100
/ (case s.sBuyUnitCost when 0 then 1 else s.sBuyUnitCost end)
AS decimal(20)) AS [이익율(%)]
FROM i_Delivery2 AS d LEFT JOIN i_Delivery AS dd ON d.dBillNo = dd.dBillNo
LEFT JOIN i_Customer AS c ON c.cID = dd.dCustomerID
LEFT JOIN i_Product AS p ON p.pID = d.dProdID
LEFT JOIN i_Stock2 AS s ON (s.sProdID) = d.dProdID
LEFT JOIN i_Code2 AS b ON b.cNewcode = p.pID
GROUP BY dd.dDate
, c.cName
, p.pID
, p.pName
, s.sBuyUnitCost
, d.dSaleUnitCost
, d.dTransCharge ,d.dEtcCharge
, b.cTmpCode |