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

Откуда:
Сообщений: 55
Есть запрос используемый 1С для обновления формы списка документов.
Не понимаю с какой целью в нем используется оператор Table Spool?

Запрос:

SELECT TOP 43
T15._Number,
T15._Fld891,
T15._Fld3765,
T15._Fld8910RRef,
T15._Fld893,
T15._Fld924RRef,
T15._Fld3347,
T15._Fld3066,
T15._Fld12059RRef,
T15._Fld5104RRef,
T15._Fld909RRef,
T15._Fld8997,
T15._Fld7116,
T15._Fld901RRef,
T15._Fld917RRef,
T15._Fld933RRef,
T15._Fld7117,
T15._Fld897RRef,
T15._Fld4291RRef,
T15._Fld912,
T15._Fld8793,
T15._Fld8790RRef,
T15._Fld889,
T15._Fld9112RRef,
T15._Fld906RRef,
T15._Fld885RRef,
T15._Fld10127RRef,
T15._Fld7406,
T15._Fld6486RRef,
T15._Fld935RRef,
T15._IDRRef,
T15._Date_Time,
T15._Marked,
T15._Posted,
T16._Description,
T17._Description,
T18._Description,
T19._Description,
T20._Description,
T21._Code,
T22._Code,
T23._Code,
T24._Description,
T25._Description,
T26._Description,
T27._Description,
T28._Description
FROM _Document77 T15 WITH(NOLOCK)
LEFT OUTER JOIN _Reference22 T16 WITH(NOLOCK)
ON T15._Fld8910RRef = T16._IDRRef
LEFT OUTER JOIN _Reference49 T17 WITH(NOLOCK)
ON T15._Fld909RRef = T17._IDRRef
LEFT OUTER JOIN _Reference22 T18 WITH(NOLOCK)
ON T15._Fld901RRef = T18._IDRRef
LEFT OUTER JOIN _Reference45 T19 WITH(NOLOCK)
ON T15._Fld917RRef = T19._IDRRef
LEFT OUTER JOIN _Reference22 T20 WITH(NOLOCK)
ON T15._Fld897RRef = T20._IDRRef
LEFT OUTER JOIN _Reference38 T21 WITH(NOLOCK)
ON T15._Fld4291RRef = T21._IDRRef
LEFT OUTER JOIN _Reference38 T22 WITH(NOLOCK)
ON T15._Fld8790RRef = T22._IDRRef
LEFT OUTER JOIN _Reference38 T23 WITH(NOLOCK)
ON T15._Fld9112RRef = T23._IDRRef
LEFT OUTER JOIN _Reference27 T24 WITH(NOLOCK)
ON T15._Fld906RRef = T24._IDRRef
LEFT OUTER JOIN _Reference8 T25 WITH(NOLOCK)
ON T15._Fld885RRef = T25._IDRRef
LEFT OUTER JOIN _Reference10119 T26 WITH(NOLOCK)
ON T15._Fld10127RRef = T26._IDRRef
LEFT OUTER JOIN _Reference49 T27 WITH(NOLOCK)
ON T15._Fld6486RRef = T27._IDRRef
LEFT OUTER JOIN _Reference37 T28 WITH(NOLOCK)
ON T15._Fld935RRef = T28._IDRRef
WHERE ((((T15._Fld908RRef IN (?, ?))) AND (T15._Fld917RRef = ?)) AND (T15._Fld924RRef = ?)) AND T15._Date_Time < ?
ORDER BY (T15._Date_Time) DESC, (T15._IDRRef) DESC


План запроса:
+------+----------+--------------+------------+-------------+------------+------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Rows | Executes | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | EstimateExecutions | StmtText                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+------+----------+--------------+------------+-------------+------------+------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 8    | 1        | 24.2         | 0          | 2.42E-006   | 1329       | 55.3             | 1                  |   |--Top(TOP EXPRESSION:((43)))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 8    | 1        | 24.2         | 0          | 0.0291      | 1329       | 55.3             | 1                  |        |--Parallelism(Gather Streams, ORDER BY:([T15].[_Date_Time] DESC, [T15].[_IDRRef] DESC))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 1329       | 55.2             | 1                  |             |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld935RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 1177       | 55.2             | 1                  |                  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld6486RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 8    | 12       | 24.2         | 0          | 0.000456    | 1125       | 55.2             | 1                  |                  |    |--Nested Loops(Left Outer Join, WHERE:([av_db].[dbo].[_Document77].[_Fld10127RRef] as [T15].[_Fld10127RRef]=[av_db].[dbo].[_Reference10119].[_IDRRef] as [T26].[_IDRRef]))                                                                                                                                                                                                                                                                                                                                                                                    |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 1063       | 55.2             | 1                  |                  |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld885RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 8    | 12       | 24.2         | 0          | 0.0201      | 1036       | 55.2             | 1                  |                  |    |    |    |--Nested Loops(Left Outer Join, WHERE:([av_db].[dbo].[_Document77].[_Fld906RRef] as [T15].[_Fld906RRef]=[av_db].[dbo].[_Reference27].[_IDRRef] as [T24].[_IDRRef]))                                                                                                                                                                                                                                                                                                                                                                                 |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 984        | 55.1             | 1                  |                  |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld9112RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 948        | 55.1             | 1                  |                  |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld8790RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 911        | 55               | 1                  |                  |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld4291RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 875        | 55               | 1                  |                  |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld897RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 773        | 54.9             | 1                  |                  |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 671        | 54.9             | 1                  |                  |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld901RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 569        | 54.8             | 1                  |                  |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld909RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 8    | 12       | 24.2         | 0          | 1.69E-005   | 517        | 54.7             | 1                  |                  |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T15].[_Fld8910RRef]))                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 8    | 12       | 24.2         | 0.00188    | 4.57E-005   | 415        | 54.7             | 1                  |                  |    |    |    |    |    |    |    |    |    |    |    |    |--Sort(ORDER BY:([T15].[_Date_Time] DESC, [T15].[_IDRRef] DESC))                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| 8    | 12       | 24.2         | 54.5       | 0.0625      | 431        | 54.5             | 1                  |                  |    |    |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Scan(OBJECT:([av_db].[dbo].[_Document77].[PK___Documen__AC8ED0C453673394] AS [T15]), WHERE:([av_db].[dbo].[_Document77].[_Fld917RRef] as [T15].[_Fld917RRef]=[@P3] AND [av_db].[dbo].[_Document77].[_Fld924RRef] as [T15].[_Fld924RRef]=[@P4] AND [av_db].[dbo].[_Document77].[_Date_Time] as [T15].[_Date_Time]<[@P5] AND ([av_db].[dbo].[_Document77].[_Fld908RRef] as [T15].[_Fld908RRef]=[@P2] OR [av_db].[dbo].[_Document77].[_Fld908RRef] as [T15].[_Fld908RRef]=[@P1]))) |
| 0    | 8        | 1            | 0.00313    | 0.000158    | 111        | 0.069            | 24.2               |                  |    |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference22].[PK___Referen__AC8ED0C45DC591CD] AS [T16]), SEEK:([T16].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld8910RRef] as [T15].[_Fld8910RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                     |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 61         | 0.0727           | 24.2               |                  |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference49].[PK___Referen__AC8ED0C408BA1173] AS [T17]), SEEK:([T17].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld909RRef] as [T15].[_Fld909RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                            |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 111        | 0.0764           | 24.2               |                  |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference22].[PK___Referen__AC8ED0C45DC591CD] AS [T18]), SEEK:([T18].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld901RRef] as [T15].[_Fld901RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                 |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 111        | 0.00696          | 24.2               |                  |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference45].[PK___Referen__AC8ED0C449D3A7B9] AS [T19]), SEEK:([T19].[_IDRRef]=[@P3]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                                                                 |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 111        | 0.0764           | 24.2               |                  |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference22].[PK___Referen__AC8ED0C45DC591CD] AS [T20]), SEEK:([T20].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld897RRef] as [T15].[_Fld897RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                           |
| 0    | 8        | 1            | 0.00313    | 0.000158    | 43         | 0.066            | 24.2               |                  |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference38].[PK___Referen__AC8ED0C475C73890] AS [T21]), SEEK:([T21].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld4291RRef] as [T15].[_Fld4291RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                              |
| 5    | 8        | 1            | 0.00313    | 0.000158    | 43         | 0.0654           | 24.2               |                  |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference38].[PK___Referen__AC8ED0C475C73890] AS [T22]), SEEK:([T22].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld8790RRef] as [T15].[_Fld8790RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                   |
| 0    | 8        | 1            | 0.00313    | 0.000158    | 43         | 0.0128           | 24.2               |                  |    |    |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference38].[PK___Referen__AC8ED0C475C73890] AS [T23]), SEEK:([T23].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld9112RRef] as [T15].[_Fld9112RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                        |
| 9528 | 8        | 1.19E+003    | 0.01       | 0.000315    | 77         | 0.0267           | 24.2               |                  |    |    |    |    |--Table Spool                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 7146 | 6        | 1.19E+003    | 0.00757    | 0.00147     | 77         | 0.00904          | 1                  |                  |    |    |    |         |--Index Scan(OBJECT:([av_db].[dbo].[_Reference27].[_Referenc27_Descr_SR] AS [T24]))                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 36         | 0.00696          | 24.2               |                  |    |    |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference8].[PK___Referen__AC8ED0C4034B50E0] AS [T25]), SEEK:([T25].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld885RRef] as [T15].[_Fld885RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                                     |
| 216  | 8        | 27           | 0.0032     | 0.000108    | 87         | 0.00583          | 24.2               |                  |    |    |--Clustered Index Scan(OBJECT:([av_db].[dbo].[_Reference10119].[PK___Reference10119N__273D01E5] AS [T26]))                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 2    | 8        | 1            | 0.00313    | 0.000158    | 61         | 0.0683           | 24.2               |                  |    |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference49].[PK___Referen__AC8ED0C408BA1173] AS [T27]), SEEK:([T27].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld6486RRef] as [T15].[_Fld6486RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                                            |
| 8    | 8        | 1            | 0.00313    | 0.000158    | 161        | 0.0102           | 24.2               |                  |--Clustered Index Seek(OBJECT:([av_db].[dbo].[_Reference37].[PK___Referen__AC8ED0C40EFCFAAE] AS [T28]), SEEK:([T28].[_IDRRef]=[av_db].[dbo].[_Document77].[_Fld935RRef] as [T15].[_Fld935RRef]) ORDERED FORWARD)                                                                                                                                                                                                                                                                                                                                                   |
+------+----------+--------------+------------+-------------+------------+------------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 янв 13, 15:59    [13747928]     Ответить | Цитировать Сообщить модератору
 Re: Зачем в данном запросе используется TableSpool?  [new]
Crimean
Member

Откуда:
Сообщений: 13148
ПК на ту табличку верните
10 янв 13, 16:46    [13748440]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить