Я видел, что производительность выбора в лучше, чем комбинация вставки/выбора, поскольку она не регистрируется. Но сегодня я столкнулся с другим сценарием. Я делаю выбор из физической таблицы во временную таблицу, и она вставляет 2,4 млн записей за 410 секунд, но когда я разделяю это на оператор выбора в, чтобы сначала создать таблицу, а затем вставить данные с помощью вставки/выбора тот же запрос щелкает 2,4 миллиона записей за 10,6 секунды.
Я в замешательстве, в чем может быть причина?
Query 1
=======
select a, b, c
into #A
from table1
where a = 'XYZ'
and c = 'ABC'
2,4 млн записей вставлено за 410 секунд
Query 2
=======
select a, b, c
into #A
from table1
where 1=2
insert #A
select a, b, c
from table1
where a = 'XYZ'
and c = 'ABC'
2,4 млн записей вставлено за 10,6 секунды
После комментария Жана
Теперь он ведет себя странно. Если я сначала выполню запрос 1, а затем запрос 2, то результаты будут такими, как указано выше. Но если я изменю порядок, запрос 1 будет выполняться через ~9 секунд, а запрос 2 — через ~7 секунд.
Plans
Запрос 1
-------
""
QUERY PLAN FOR STATEMENT 1 (at line 1).
""
""
STEP 1
The type of query is DECLARE.
""
""
QUERY PLAN FOR STATEMENT 2 (at line 2).
""
""
STEP 1
The type of query is SELECT.
""
1 operator(s) under root
""
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
""
""
""
QUERY PLAN FOR STATEMENT 3 (at line 3).
""
""
STEP 1
The type of query is CREATE TABLE.
""
STEP 2
The type of query is INSERT.
""
5 operator(s) under root
""
|ROOT:EMIT Operator (VA = 5)
|
| |INSERT Operator (VA = 4)
| | The update mode is direct.
| |
| | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM OR List
| | | | OR List has up to 3 rows of OR/IN values.
| | |
| | | |RESTRICT Operator (VA = 2)(0)(0)(0)(10)(0)
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | ABCD.dbo.ITR
| | | | | a
| | | | | Using Clustered Index.
| | | | | Index : ITR_typ_imnt_cl_optn
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | id_typ_imnt ASC
| | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | #A
| | Using I/O Size 16 Kbytes for data pages.
""
""
""
QUERY PLAN FOR STATEMENT 4 (at line 9).
""
""
STEP 1
The type of query is SELECT.
""
1 operator(s) under root
""
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
""
""
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Table: #A scan count 0, logical reads: (regular=2452707 apf=0 total=2452707), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: ABCD.dbo.ITR (a) scan count 3, logical reads: (regular=581980 apf=0 total=581980), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Execution Time 75.
Adaptive Server cpu time: 7500 ms. Adaptive Server elapsed time: 7546 ms.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Запрос 2
-------
""
QUERY PLAN FOR STATEMENT 1 (at line 1).
""
""
STEP 1
The type of query is DECLARE.
""
""
QUERY PLAN FOR STATEMENT 2 (at line 2).
""
""
STEP 1
The type of query is SELECT.
""
1 operator(s) under root
""
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
""
""
""
QUERY PLAN FOR STATEMENT 3 (at line 3).
""
""
STEP 1
The type of query is CREATE TABLE.
""
STEP 2
The type of query is INSERT.
""
3 operator(s) under root
""
|ROOT:EMIT Operator (VA = 3)
|
| |INSERT Operator (VA = 2)
| | The update mode is direct.
| |
| | |RESTRICT Operator (VA = 1)(4)(0)(0)(0)(0)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | CERD_CORPORATE..INSTRUMENT
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | #A
| | Using I/O Size 16 Kbytes for data pages.
""
""
""
QUERY PLAN FOR STATEMENT 4 (at line 5).
""
""
STEP 1
The type of query is INSERT.
""
5 operator(s) under root
""
|ROOT:EMIT Operator (VA = 5)
|
| |INSERT Operator (VA = 4)
| | The update mode is direct.
| |
| | |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM OR List
| | | | OR List has up to 3 rows of OR/IN values.
| | |
| | | |RESTRICT Operator (VA = 2)(0)(0)(0)(10)(0)
| | | |
| | | | |SCAN Operator (VA = 1)
| | | | | FROM TABLE
| | | | | CERD_CORPORATE.dbo.INSTRUMENT
| | | | | a
| | | | | Using Clustered Index.
| | | | | Index : INSTRUMENT_typ_imnt_cl_optn
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | id_typ_imnt ASC
| | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | TO TABLE
| | #A
| | Using I/O Size 2 Kbytes for data pages.
""
""
""
QUERY PLAN FOR STATEMENT 5 (at line 11).
""
""
STEP 1
The type of query is SELECT.
""
1 operator(s) under root
""
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
""
""
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Table: #A scan count 0, logical reads: (regular=1 apf=0 total=1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CERD_CORPORATE..INSTRUMENT scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
Table: #A scan count 0, logical reads: (regular=2493221 apf=0 total=2493221), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: CERD_CORPORATE.dbo.INSTRUMENT (a) scan count 3, logical reads: (regular=581980 apf=0 total=581980), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Execution Time 96.
Adaptive Server cpu time: 9600 ms. Adaptive Server elapsed time: 9580 ms.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
