|
su.dbms.sql
- SU.DBMS.SQL ------------------------------------------------------------------
From : Tolik Tentser 2:5020/400 03 Apr 2001 14:35:07
To : All
Subject : Re: Дремина хитрость 2
--------------------------------------------------------------------------------
Hi !
> >Аксиома - JOIN с дополнительным справочником любого объема по его
> >(справочника) первичному ключу, особливо, если он CLUSTERED - для MSSQL -
> >практически никак не влияет на время исполнения запроса.
>
> Приведи пример твоего SELECTа на миллион записей который, плиз.
Hу, допустим:
SELECT T.*,
- DATEDIFF(day, CASE WHEN @StartDate > T.MustPayDate THEN @StartDate
ELSE T.MustPayDate
END,
CASE WHEN @StartDate > T.PayDate THEN @StartDate
ELSE T.PayDate
END) * T.Amount * @PayPercent * T.[Index] / 100 AS Income
FROM (
SELECT H.ObjectId AS Id,
H.ItemDate AS PayDate,
H2.ItemDate AS DocDate,
M.Amount,
D.DocNum,
OT.ItemName AS DocType,
H1.Itemdate AS MustPayDate,
OC.Itemname AS Contractor,
OM.ItemName AS Manager,
D2.DocNum AS PayDocNum,
OT2.ItemName AS PayDocType,
CASE WHEN @ForManager IS NULL THEN PInd.value ELSE 2 - 2 *
PInd.value END AS [Index]
FROM History H
INNER JOIN Links L ON L.ChildId = H.ObjectId AND L.TypeId =
@Contractor
INNER JOIN Links LM ON LM.ChildId = L.ParentId AND LM.TypeId =
@Manager AND ISNULL(@ManagerId, LM.parentId) = LM.parentId
INNER JOIN Movement M WITH(INDEX(IN_Movement_GroupIdContentId)) ON
M.GroupId = H.ObjectId AND M.ContentId = @Roubles
INNER JOIN Objects O1 ON O1.Id = M.ContainerId AND O1.TypeId IN
(@InvoiceOut, @InvoiceTr)
INNER JOIN History H2 ON H2.ObjectId = M.ContainerId AND H2.ItemDate <
@EndDate + 1
INNER JOIN Status S2 ON S2.Id = H2.StatusId AND S2.Code = 'DATE'
INNER LOOP JOIN Objects O2 ON O2.Id = H.ObjectId
INNER LOOP JOIN Doc D2 ON D2.Id = O2.Id
INNER LOOP JOIN ObjType OT2 ON OT2.Id = O2.TypeId
INNER LOOP JOIN Doc D ON D.Id = O1.Id
INNER LOOP JOIN ObjType OT ON OT.Id = O1.TypeId
INNER JOIN History H1 ON H1.ObjectId = M.ContainerId
INNER JOIN Status S ON S.Id = H1.StatusId AND S.Code = 'PAYDATE'
INNER JOIN Objects OM ON OM.Id = LM.parentId
INNER JOIN Objects OC ON OC.Id = L.parentId
LEFT LOOP JOIN Properties PInd ON PInd.ObjectId = OC.Id AND
PInd.TypeId = @Index
INNER JOIN Objects O ON O.Id = H.ObjectId
WHERE H.StatusId IN (@CODate, @DIDate) AND H.Itemdate >= @StartDate AND
H.ItemDate < @EndDate + 1
AND (H.StatusId = @CODate OR EXISTS(SELECT TOP 1 * FROM MoveLink ML
INNER JOIN History HL ON
HL.ObjectId = ML.ObjectId
INNER JOIN Status SL ON SL.Id =
HL.StatusId AND SL.GroupId = @AccType AND SL.Code IN ('BANK', 'VEXELMANY')
WHERE ML.MoveId = M.Id AND
ML.TypeId = @Account))
AND NOT EXISTS (SELECT TOP 1 * FROM History WHERE ObjectId = H.ObjectId
AND StatusId = @Tender)
UNION --ALL
SELECT M.GroupId,
H2.ItemDate,
H.Itemdate,
M.Amount,
D.DocNum,
OT.ItemName,
H1.Itemdate,
OC.Itemname,
OM.ItemName,
D2.DocNum AS PayDocNum,
OT2.ItemName AS PayDocType,
CASE WHEN @ForManager IS NULL THEN PInd.value ELSE 2 - 2 *
PInd.value END AS [Index]
FROM History H
INNER JOIN Movement M WITH(INDEX(IN_Movement_ContainerId)) ON
M.ContainerId = H.ObjectId AND M.ContentId = @Roubles
INNER LOOP JOIN Objects O1 ON O1.Id = M.GroupId AND O1.TypeId IN
(@CashOrder, @DraftIn)
INNER JOIN Links L ON L.ChildId = M.GroupId AND L.TypeId = @Contractor
INNER JOIN Links LM ON LM.ChildId = L.ParentId AND LM.TypeId =
@Manager AND ISNULL(@ManagerId, LM.parentId) = LM.parentId
INNER LOOP JOIN Doc D ON D.Id = H.ObjectId
INNER LOOP JOIN Objects O ON O.Id = H.ObjectId
INNER LOOP JOIN ObjType OT ON OT.Id = O.TypeId
INNER JOIN History H2 ON H2.ObjectId = M.GroupId AND H2.ItemDate <
@EndDate + 1
INNER LOOP JOIN Objects O2 ON O2.Id = M.GroupId
INNER LOOP JOIN Doc D2 ON D2.Id = O2.Id
INNER LOOP JOIN ObjType OT2 ON OT2.Id = O2.TypeId
INNER JOIN Status S2 ON S2.Id = H2.StatusId AND S2.Code = 'DATE'
INNER JOIN History H1 ON H1.ObjectId = M.ContainerId
INNER JOIN Status S ON S.Id = H1.StatusId AND S.Code = 'PAYDATE'
INNER LOOP JOIN Objects OM ON OM.Id = LM.parentId
INNER LOOP JOIN Objects OC ON OC.Id = L.parentId
LEFT LOOP JOIN Properties PInd ON PInd.ObjectId = OC.Id AND
PInd.TypeId = @Index
WHERE H.StatusId IN (@IODate, @ITDate) AND H.Itemdate >= @StartDate AND
H.ItemDate < @EndDate + 1
AND (H2.StatusId = @CODate OR EXISTS(SELECT TOP 1 * FROM MoveLink ML
INNER JOIN History HL ON
HL.ObjectId = ML.ObjectId
INNER JOIN Status SL ON SL.Id =
HL.StatusId AND SL.GroupId = @AccType AND SL.Code IN ('BANK', 'VEXELMANY')
WHERE ML.MoveId = M.Id AND
ML.TypeId = @Account))
AND NOT EXISTS (SELECT TOP 1 * FROM History WHERE ObjectId =
H2.ObjectId AND StatusId = @Tender)
) AS T
ORDER BY T.Manager, T.PayDate, T.PayDocNum
Пойдет ?
P.S.
SELECT
(SELECT COUNT(*) FROM History) AS History
,(SELECT COUNT(*) FROM Objects) AS Objects
,(SELECT COUNT(*) FROM Movement) AS Movement
,(SELECT COUNT(*) FROM Links) AS Links
,(SELECT COUNT(*) FROM Strings) AS Strings
,(SELECT COUNT(*) FROM Properties) AS Properties
,(SELECT COUNT(*) FROM Doc) AS Doc
History Objects Movement Links Strings Properties Doc
----------- ----------- ----------- ----------- ----------- ----------- ----
-------
10181181 3225063 12697674 6594693 998845 3686049
1930949
--
Bye ...
Тенцер А.Л.
tolik@katren.ru
ICQ 15925834
--- ifmail v.2.15dev5
* Origin: Katren (2:5020/400)
Вернуться к списку тем, сортированных по: возрастание даты уменьшение даты тема автор
|
