|
Преимущество временной таблицы от Firebird'a (Global Temporary Tables) перед собственной
#38782368
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
fedorov2012делаю простой примерГде он ? Полный его текст, плз: DDL, DML + результаты по трейсу.
По сабжу: DDL 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
recreate table tmp(id int primary key, x int, y int, z int);
commit;
create index tmp_x on tmp(x);
create index tmp_y on tmp(y);
create index tmp_z on tmp(z);
commit;
recreate global temporary table gtt(id int primary key, x int, y int, z int) on commit delete rows;
commit;
create index gtt_x on gtt(x);
create index gtt_y on gtt(y);
create index gtt_z on gtt(z);
commit;
DML-1 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
set term ^;
execute block as
declare n int = 500000;
declare i int = 0;
begin
while(i<n) do
insert into tmp values( :i, rand()*10, rand()*100, rand()*1000 )
returning :i+1 into i;
end
^
set term ;^
rollback;
DML-2 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
set term ^;
execute block as
declare n int = 500000;
declare i int = 0;
begin
while(i<n) do
insert into gtt values( :i, rand()*10, rand()*100, rand()*1000 )
returning :i+1 into i;
end
^
set term ;^
rollback;
Result-1: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
2014-10-21T01:49:19.5090 (14037:0x7f3fb28069e0) EXECUTE_STATEMENT_FINISH
oltp25 (ATT_1694, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:924
(TRA_158013, CONCURRENCY | WAIT | READ_WRITE)
Statement 39:
-------------------------------------------------------------------------------
execute block as
declare n int = 500000;
declare i int = 0;
begin
while(i<n) do
insert into tmp values( :i, rand()*10, rand()*100, rand()*1000 )
returning :i+1 into i;
end
0 records fetched
23473 ms, 6187 read(s), 5269 write(s), 19024610 fetch(es), 5511466 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
TMP 500000 500000
2014-10-21T01:49:19.5100 (14037:0x7f3fb28069e0) ROLLBACK_TRANSACTION
oltp25 (ATT_1694, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:924
(TRA_158013, CONCURRENCY | WAIT | READ_WRITE)
0 ms, 1 read(s), 2 write(s), 1 fetch(es), 1 mark(s)
Result-2 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
2014-10-21T01:47:29.7440 (14037:0x7f3fb28069e0) EXECUTE_STATEMENT_FINISH
oltp25 (ATT_1694, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:924
(TRA_158012, CONCURRENCY | WAIT | READ_WRITE)
Statement 38:
-------------------------------------------------------------------------------
execute block as
declare n int = 500000;
declare i int = 0;
begin
while(i<n) do
insert into gtt values( :i, rand()*10, rand()*100, rand()*1000 )
returning :i+1 into i;
end
0 records fetched
14265 ms, 5 read(s), 6360 write(s), 7537530 fetch(es), 2529897 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 16
GTT 500000
2014-10-21T01:47:29.7460 (14037:0x7f3fb28069e0) ROLLBACK_TRANSACTION
oltp25 (ATT_1694, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:924
(TRA_158012, CONCURRENCY | WAIT | READ_WRITE)
2 ms, 257 read(s), 3 write(s), 7577 fetch(es), 5685 mark(s)
ЗЫ.
fw = OFF, cache = 4096, 2.5.3 SC |
|
|
