У меня такая проблема, если кто знает, помогите пожалуйста. У меня большая процедура, в результате выполнения одной части которой получается временная таблица размером 800Mb, так и было задумано, в результирующей таблице 1,7 млн. записей. А потом из этой временной таблицы делается SELECT, и MSSQL начинает долго работать с таблицей на диске, время мсполнения около 2 дней. Что можно сделать, чтобы это дело было побыстрей, процедуру и так уже оптимизировали-оптимизировали. У меня MSSQL Server 7.0, tempdb in ram там не работает, а в машине 512MB мозгов, правда операционка Win98. Если поможет, могу привести и текст процедуры. Заранее спасибо.

уверен, что оптимизировать еще можно, проста сам я в этом не дока. Да, MSSQL а Win98, переставить систему просто не хватает времени. Если переставить, то примерно какой прирост производительности возможен, если машина PIII 700Mhz,
512 Ram?

Я приведу полный текст процедуры, на Ваш взгляд это может показаться полным бредом - извините, я еще новичок в этом.



/* --- NB !!! Procedure for all warehouses except Nr1. */

Alter Procedure TG_Wh3_GD_Daily_Stock /* change number */
(
@NrWarehouse int = 3 /* change number */

)

As

/* ------------- NB !!! - SmallDateTime format : mm.dd.yy */

CREATE TABLE #TempTable_CODES_Union
(
CODE_GD varchar(10)

)

/* ---------------------------------------------------------- */

INSERT INTO #TempTable_CODES_Union
SELECT DISTINCT AF_Accounts.CODE_GD
FROM AF_Accounts
WHERE AF_Accounts.NS = @NrWarehouse AND (YEAR(AF_Accounts.DATACN)=2000
OR YEAR(AF_Accounts.DATE_DELIV) = 2000)

UNION

SELECT DISTINCT AF_Sales00.CODE_GD
FROM AF_Sales00
WHERE AF_Sales00.NS = @NrWarehouse AND YEAR(AF_Sales00.DATDOC)=2000

UNION

SELECT DISTINCT AF_Transfer.CODE_GD
FROM AF_Transfer
WHERE (AF_Transfer.NS_S = @NrWarehouse OR
AF_Transfer.NS_R = @NrWarehouse) AND YEAR(AF_Transfer.DATDOC)=2000

UNION

SELECT DISTINCT AF_Stock010100.CODE_GD
FROM AF_Stock010100
WHERE AF_Stock010100.NS = @NrWarehouse


/* ---------------------------------------------------------- */

CREATE TABLE #TempTable_Distinct_CODES
(
CODE_GD varchar(10)
PRIMARY KEY CLUSTERED (CODE_GD)
)


/* ---------------------------------------------------------- */

INSERT INTO #TempTable_Distinct_CODES
SELECT DISTINCT CODE_GD
FROM #TempTable_CODES_Union

/* ---------------------------------------------------------- */


CREATE TABLE #TempTable_StockDelta
(
DATDOC smalldatetime,
CODE_GD varchar(10),
QntDelta float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)


/* ---------------------------------------------------------- */


INSERT INTO #TempTable_StockDelta

SELECT d.Date,
w.CODE_GD,
(
(
ISNULL(
(
SELECT SUM(s.[COUNT])
FROM AF_Stock010100 as s
WHERE s.CODE_GD=w.CODE_GD AND NS=@NrWarehouse AND d.Date='01.01.00'
GROUP BY s.CODE_GD
), 0.00)

)
-
(
ISNULL(
(
SELECT SUM(sl.[COUNT])
FROM AF_Sales00 as sl
WHERE sl.CODE_GD=w.CODE_GD AND sl.DATDOC=d.Date AND NS=@NrWarehouse
GROUP BY sl.CODE_GD
), 0.00)
)
+
(
ISNULL(
(
SELECT SUM(tr.[COUNT])
FROM AF_Transfer as tr
WHERE tr.CODE_GD=w.CODE_GD AND tr.DATDOC=d.Date AND NS_R=@NrWarehouse
GROUP BY tr.CODE_GD
), 0.00)
)
-
(
ISNULL(
(
SELECT SUM(ts.[COUNT])
FROM AF_Transfer as ts
WHERE ts.CODE_GD=w.CODE_GD AND ts.DATDOC=d.Date AND NS_S=@NrWarehouse
GROUP BY ts.CODE_GD
), 0.00)
)


) AS QntDelta

FROM #TempTable_Distinct_CODES AS w, ID_Dates2000 AS d


/* ---------------------------------------------------------- */

/* next fragment creates a new table abd inserts quantity of each
item on stock every day of the year 2000 */


CREATE TABLE TG_Wh3_CurQnt /* change number */
(
DATDOC smalldatetime,
CODE_GD varchar(10),
CurQnt float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)

/* ---------------------------------------------------------- */


INSERT INTO TG_Wh3_CurQnt /* change number */

SELECT b.DATDOC,
b.CODE_GD,
(
SELECT SUM(a.QntDelta)
FROM #TempTable_StockDelta AS a
WHERE b.CODE_GD=a.CODE_GD AND a.DATDOC<=b.DATDOC
)
AS CurQnt

FROM #TempTable_StockDelta AS b


return


Если нужны более точные пояснения, что где за какие таблицы, то я дам. Таблицы с кластерным индексом. Заранее спасибо.

/* --- NB !!! Procedure for all warehouses except Nr1. */

Alter Procedure TG_Wh2_GD_Daily_Stock /* change number */
(
@NrWarehouse int = 2 /* change number */

)

As

/* ------------- NB !!! - SmallDateTime format : mm.dd.yy */


CREATE TABLE #CODES_Union
(
CODE_GD varchar(10)

)

/* ---------------------------------------------------------- */

INSERT INTO #CODES_Union
SELECT DISTINCT AF_Accounts.CODE_GD
FROM AF_Accounts
WHERE AF_Accounts.NS = @NrWarehouse AND (YEAR(AF_Accounts.DATACN)=2000
OR YEAR(AF_Accounts.DATE_DELIV) = 2000)


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Sales00.CODE_GD
FROM AF_Sales00
WHERE AF_Sales00.NS = @NrWarehouse AND YEAR(AF_Sales00.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Transfer.CODE_GD
FROM AF_Transfer
WHERE (AF_Transfer.NS_S = @NrWarehouse OR
AF_Transfer.NS_R = @NrWarehouse) AND YEAR(AF_Transfer.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Stock010100.CODE_GD
FROM AF_Stock010100
WHERE AF_Stock010100.NS = @NrWarehouse


/* ---------------------------------------------------------- */

CREATE TABLE #CODES
(
CODE_GD varchar(10)
PRIMARY KEY CLUSTERED (CODE_GD)
)


/* ---------------------------------------------------------- */

INSERT INTO #CODES
SELECT DISTINCT CODE_GD
FROM #CODES_Union

/* ---------------------------------------------------------- */


CREATE TABLE #StockDelta
(
DATDOC smalldatetime,
CODE_GD varchar(10),
QntDelta float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)


/* ---------------------------------------------------------- */


INSERT INTO #StockDelta

SELECT d.Date,
w.CODE_GD,
(
(
ISNULL(
(
SELECT SUM(s.[COUNT])
FROM AF_Stock010100 as s
WHERE s.CODE_GD=w.CODE_GD AND NS=@NrWarehouse AND d.Date='01.01.00'
GROUP BY s.CODE_GD
), 0.00)

)
-
(
ISNULL(
(
SELECT SUM(sl.[COUNT])
FROM AF_Sales00 as sl
WHERE sl.CODE_GD=w.CODE_GD AND sl.DATDOC=d.Date AND NS=@NrWarehouse
GROUP BY sl.CODE_GD
), 0.00)
)
+
(
ISNULL(
(
SELECT SUM(tr.[COUNT])
FROM AF_Transfer as tr
WHERE tr.CODE_GD=w.CODE_GD AND tr.DATDOC=d.Date AND NS_R=@NrWarehouse
GROUP BY tr.CODE_GD
), 0.00)
)
-
(
ISNULL(
(
SELECT SUM(ts.[COUNT])
FROM AF_Transfer as ts
WHERE ts.CODE_GD=w.CODE_GD AND ts.DATDOC=d.Date AND NS_S=@NrWarehouse
GROUP BY ts.CODE_GD
), 0.00)
)


) AS QntDelta

FROM #CODES AS w, ID_Dates2000 AS d


/* ---------------------------------------------------------- */

/* next fragment creates a new table abd inserts quantity of each
item on stock every day of the year 2000 */


CREATE TABLE TG_Wh2_CurQnt /* change number */
(
DATDOC smalldatetime,
CODE_GD varchar(10),
CurQnt float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)

/* ---------------------------------------------------------- */


INSERT INTO TG_Wh2_CurQnt /* change number */

SELECT d.[Date] AS DATDOC,
c.CODE_GD,
(
SELECT SUM(a.QntDelta) AS CurQnt
FROM
(
SELECT *
FROM #StockDelta AS s
WHERE c.CODE_GD=s.CODE_GD
) AS a
WHERE a.DATDOC<=d.[Date]
)
AS CurQnt

FROM #Codes AS c, ID_Dates2000 AS d



return

/* --- NB !!! Procedure for all warehouses except Nr1. */

Alter Procedure TG_Wh2_GD_Daily_Stock /* change number */
(
@NrWarehouse int = 2 /* change number */

)

As

/* ------------- NB !!! - SmallDateTime format : mm.dd.yy */


CREATE TABLE #CODES_Union
(
CODE_GD varchar(10)

)

/* ---------------------------------------------------------- */

INSERT INTO #CODES_Union
SELECT DISTINCT AF_Accounts.CODE_GD
FROM AF_Accounts
WHERE AF_Accounts.NS = @NrWarehouse AND (YEAR(AF_Accounts.DATACN)=2000
OR YEAR(AF_Accounts.DATE_DELIV) = 2000)


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Sales00.CODE_GD
FROM AF_Sales00
WHERE AF_Sales00.NS = @NrWarehouse AND YEAR(AF_Sales00.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Transfer.CODE_GD
FROM AF_Transfer
WHERE (AF_Transfer.NS_S = @NrWarehouse OR
AF_Transfer.NS_R = @NrWarehouse) AND YEAR(AF_Transfer.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Stock010100.CODE_GD
FROM AF_Stock010100
WHERE AF_Stock010100.NS = @NrWarehouse


/* ---------------------------------------------------------- */

CREATE TABLE #CODES
(
CODE_GD varchar(10)
PRIMARY KEY CLUSTERED (CODE_GD)
)


/* ---------------------------------------------------------- */

INSERT INTO #CODES
SELECT DISTINCT CODE_GD
FROM #CODES_Union

/* ---------------------------------------------------------- */


CREATE TABLE #StockDelta
(
DATDOC smalldatetime,
CODE_GD varchar(10),
QntDelta float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)


/* ---------------------------------------------------------- */


INSERT INTO #StockDelta

SELECT d.Date,
w.CODE_GD,
(
(
ISNULL(
(
SELECT SUM(s.[COUNT])
FROM AF_Stock010100 as s
WHERE s.CODE_GD=w.CODE_GD AND NS=@NrWarehouse AND d.Date='01.01.00'
GROUP BY s.CODE_GD
), 0.00)

)
-
(
ISNULL(
(
SELECT SUM(sl.[COUNT])
FROM AF_Sales00 as sl
WHERE sl.CODE_GD=w.CODE_GD AND sl.DATDOC=d.Date AND NS=@NrWarehouse
GROUP BY sl.CODE_GD
), 0.00)
)
+
(
ISNULL(
(
SELECT SUM(tr.[COUNT])
FROM AF_Transfer as tr
WHERE tr.CODE_GD=w.CODE_GD AND tr.DATDOC=d.Date AND NS_R=@NrWarehouse
GROUP BY tr.CODE_GD
), 0.00)
)
-
(
ISNULL(
(
SELECT SUM(ts.[COUNT])
FROM AF_Transfer as ts
WHERE ts.CODE_GD=w.CODE_GD AND ts.DATDOC=d.Date AND NS_S=@NrWarehouse
GROUP BY ts.CODE_GD
), 0.00)
)


) AS QntDelta

FROM #CODES AS w, ID_Dates2000 AS d


/* ---------------------------------------------------------- */

/* next fragment creates a new table abd inserts quantity of each
item on stock every day of the year 2000 */


CREATE TABLE TG_Wh2_CurQnt /* change number */
(
DATDOC smalldatetime,
CODE_GD varchar(10),
CurQnt float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)

/* ---------------------------------------------------------- */


INSERT INTO TG_Wh2_CurQnt /* change number */

SELECT d.[Date] AS DATDOC,
c.CODE_GD,
(
SELECT SUM(a.QntDelta) AS CurQnt
FROM
(
SELECT *
FROM #StockDelta AS s
WHERE c.CODE_GD=s.CODE_GD
) AS a
WHERE a.DATDOC<=d.[Date]
)
AS CurQnt

FROM #Codes AS c, ID_Dates2000 AS d



return

Большое спасибо всем за помощь!
В результате я использовал такой код (он выполнялся около 20 минут):


/* --- NB !!! Procedure for all warehouses except Nr1. */

Alter Procedure TG_Wh2_GD_Daily_Stock /* change number */
(
@NrWarehouse int = 2 /* change number */

)

As

/* ------------- NB !!! - SmallDateTime format : mm.dd.yy */


CREATE TABLE #CODES_Union
(
CODE_GD varchar(10)

)

/* ---------------------------------------------------------- */

INSERT INTO #CODES_Union
SELECT DISTINCT AF_Accounts.CODE_GD
FROM AF_Accounts
WHERE AF_Accounts.NS = @NrWarehouse AND (YEAR(AF_Accounts.DATACN)=2000
OR YEAR(AF_Accounts.DATE_DELIV) = 2000)


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Sales00.CODE_GD
FROM AF_Sales00
WHERE AF_Sales00.NS = @NrWarehouse AND YEAR(AF_Sales00.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Transfer.CODE_GD
FROM AF_Transfer
WHERE (AF_Transfer.NS_S = @NrWarehouse OR
AF_Transfer.NS_R = @NrWarehouse) AND YEAR(AF_Transfer.DATDOC)=2000


INSERT INTO #CODES_Union
SELECT DISTINCT AF_Stock010100.CODE_GD
FROM AF_Stock010100
WHERE AF_Stock010100.NS = @NrWarehouse


/* ---------------------------------------------------------- */

CREATE TABLE #CODES
(
CODE_GD varchar(10)
PRIMARY KEY CLUSTERED (CODE_GD)
)


/* ---------------------------------------------------------- */

INSERT INTO #CODES
SELECT DISTINCT CODE_GD
FROM #CODES_Union

/* ---------------------------------------------------------- */


CREATE TABLE #StockDelta
(
DATDOC smalldatetime,
CODE_GD varchar(10),
QntDelta float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)


/* ---------------------------------------------------------- */


INSERT INTO #StockDelta

SELECT d.Date,
w.CODE_GD,
(
(
ISNULL(
(
SELECT SUM(s.[COUNT])
FROM AF_Stock010100 as s
WHERE s.CODE_GD=w.CODE_GD AND NS=@NrWarehouse AND d.Date='01.01.00'
GROUP BY s.CODE_GD
), 0.00)

)
-
(
ISNULL(
(
SELECT SUM(sl.[COUNT])
FROM AF_Sales00 as sl
WHERE sl.CODE_GD=w.CODE_GD AND sl.DATDOC=d.Date AND NS=@NrWarehouse
GROUP BY sl.CODE_GD
), 0.00)
)
+
(
ISNULL(
(
SELECT SUM(tr.[COUNT])
FROM AF_Transfer as tr
WHERE tr.CODE_GD=w.CODE_GD AND tr.DATDOC=d.Date AND NS_R=@NrWarehouse
GROUP BY tr.CODE_GD
), 0.00)
)
-
(
ISNULL(
(
SELECT SUM(ts.[COUNT])
FROM AF_Transfer as ts
WHERE ts.CODE_GD=w.CODE_GD AND ts.DATDOC=d.Date AND NS_S=@NrWarehouse
GROUP BY ts.CODE_GD
), 0.00)
)


) AS QntDelta

FROM #CODES AS w, ID_Dates2000 AS d


/* ---------------------------------------------------------- */

/* next fragment creates a new table abd inserts quantity of each
item on stock every day of the year 2000 */


CREATE TABLE TG_Wh2_CurQnt /* change number */
(
DATDOC smalldatetime,
CODE_GD varchar(10),
CurQnt float

PRIMARY KEY CLUSTERED (DATDOC, CODE_GD)
)

/* ---------------------------------------------------------- */


INSERT INTO TG_Wh2_CurQnt /* change number */

SELECT d.[Date] AS DATDOC,
c.CODE_GD,
(
SELECT SUM(a.QntDelta) AS CurQnt
FROM
(
SELECT *
FROM #StockDelta AS s
WHERE c.CODE_GD=s.CODE_GD
) AS a
WHERE a.DATDOC<=d.[Date]
)
AS CurQnt

FROM #Codes AS c, ID_Dates2000 AS d



return

С кодами проблем нету. Они выбираются и так довольно быстро.
Вся фигня была в последнем фрагменте процедуры.
Там грубо говоря для каждой записи просматривались все.
Если учесть, что в таблицах до 4 млн. записей - получалось 4*4 млн....
Его я и изменил.
А до двух минут думаю довести врядли получится.
Да и стоит ли, что бы по одному разу несколько табличек прогнать?..