CREATE TABLE AAP ([ID] int PRIMARY KEY, [Code] varchar( 255 ) UNIQUE, [Description] varchar( 7500 ))
INSERT INTO AAP ([ID], [Code], [Description])
SELECT [ID], [Code], [Description] FROM MMK2004.dbo.[AAP.Collection]

SELECT [ID], [Code], [Description] FROM AAP 
-- Table 'AAP'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 15 ms,  elapsed time = 86 ms.
SELECT [ID], [Code], [Description] FROM MMK2004.dbo.[AAP.Collection]
-- Table 'NoteA'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0.
-- Table 'Object'. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 0.
-- Table 'StringA'. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 32 ms,  elapsed time = 81 ms.


CREATE TABLE Prov ([ID] int PRIMARY KEY, [DebetID] int, [SumDef] decimal( 28 ,  10 ), [Description] varchar( 7500 ))
INSERT INTO Prov
SELECT [ID], [DebetID], [SumDef], [Description] FROM MMK2004.dbo.[JHProv.Collection]

SELECT * FROM Prov
-- Table 'Prov'. Scan count 1, logical reads 149, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 62 ms,  elapsed time = 465 ms.
SELECT a.ObjectID AS [ID], a.[Data] AS [DebetID], s.[Data] AS [SumDef], d.Data AS [Description]
FROM MMK2004.dbo.[JHProv.DebetID] a
INNER MERGE JOIN MMK2004.dbo.[JHProv.SumDef] s ON (s.[ObjectID] = a.[ObjectID])
LEFT MERGE JOIN MMK2004.dbo.[JHProv.Description] d ON (d.[ObjectID] = a.[ObjectID])
-- Table 'NoteA'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0.
-- Table 'Numbers'. Scan count 1, logical reads 148, physical reads 0, read-ahead reads 0.
-- Table 'Reference'. Scan count 1, logical reads 106, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 188 ms,  elapsed time = 465 ms.

CREATE VIEW [AAP.Sums1] AS
SELECT
DebetID
, SUM([SumDef]) AS [Sum]
, (SELECT [Description] FROM [AAP] a WHERE a.[ID] = DebetID) AS [Description]
FROM [Prov]
GROUP BY DebetID
GO

CREATE VIEW [AAP.Sums2] AS
SELECT
a.[Data] AS DebetID
, SUM(s.Data) AS [Sum]
, (SELECT [Data] FROM MMK2004.dbo.[AAP.Description] d WHERE d.[ObjectID] = a.[Data]) AS [Description]
FROM MMK2004.dbo.[JHProv.DebetID] a
INNER MERGE JOIN MMK2004.dbo.[JHProv.SumDef] s ON (s.[ObjectID] = a.[ObjectID])
GROUP BY a.[Data]
GO


SELECT * FROM [AAP.Sums1]
-- Table 'AAP'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0.
-- Table 'Prov'. Scan count 1, logical reads 149, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 109 ms,  elapsed time = 145 ms.
SELECT * FROM [AAP.Sums2]
-- Table 'NoteA'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0.
-- Table 'Numbers'. Scan count 1, logical reads 148, physical reads 0, read-ahead reads 0.
-- Table 'Reference'. Scan count 1, logical reads 106, physical reads 0, read-ahead reads 0.
-- 
-- SQL Server Execution Times:
--    CPU time = 235 ms,  elapsed time = 266 ms.
GO

DROP TABLE AAP
DROP TABLE Prov

Таблица    Строк

Object       598 053
Reference   876 646
StringA      301 483
NoteA        298 425
Numbers     1 111 414