Hello !
I'm using sqlite (trunk) for a database (see bellow) and for a final
database file of 22GB a "vacuum" was executed and doing so it made a
lot of I/O ( 134GB reads and 117GB writes in 2h:30min).
Can something be improved on sqlite to achieve a better performance ?
The data is public available just in case it can be useful to perform
tests.
Cheers !
After 12 hours inserting of:
934,135,285 records on bolsas_familia
22,711,259 records in favorecidos
5,570 records in municipios
...
All that on mac-mini with i5 cpu 4GB memory:
1GB read 26MB write before vacuum
time sqlite3 bolsa_familia3.db "vacuum;"
real 147m6.252s
user 10m53.790s
sys 3m43.663s
ls -l bolsa_familia3.db
-rw-r--r-- 1 xxxx staff 22772744192 Oct 1 14:58 bolsa_familia3.db
MemRegions: 5656 total, 74M resident, 8904K private, 43M shared.
PhysMem: 650M used (471M wired), 3444M unused.
VM: 167G vsize, 1063M framework vsize, 18421751(0) swapins, 19671240(0)
swapouts.
Disks: 414062/135G read, 369485/118G written.
time sqlite3_analyzer bolsa_familia3.db > bolsa_familia3.db.analyze.txt
real 5m7.607s
user 2m48.184s
sys 0m56.512s
filefrag bolsa_familia3.db
bolsa_familia3.db: 29 extents found
=======
year_month|records_inserted|start_time|end_time|minutes_spent
201101|12851338|2016-09-30 20:55:26|2016-09-30 20:59:50|4.4
201102|12946306|2016-09-30 20:59:51|2016-09-30 21:03:26|3.58
201103|12944677|2016-09-30 21:03:26|2016-09-30 21:06:55|3.48
201104|13058478|2016-09-30 21:06:55|2016-09-30 21:10:52|3.95
201105|12986870|2016-09-30 21:10:53|2016-09-30 21:14:49|3.93
201106|12999562|2016-09-30 21:14:49|2016-09-30 21:18:30|3.68
201107|12952040|2016-09-30 21:18:33|2016-09-30 21:22:26|3.88
201108|12805039|2016-09-30 21:22:29|2016-09-30 21:26:15|3.77
201109|13179472|2016-09-30 21:26:16|2016-09-30 21:30:15|3.98
201110|13171810|2016-09-30 21:30:15|2016-09-30 21:34:34|4.32
201111|13306920|2016-09-30 21:34:48|2016-09-30 21:40:26|5.63
201112|13352307|2016-09-30 21:40:36|2016-09-30 21:45:06|4.5
201201|13330714|2016-09-30 21:45:11|2016-09-30 21:58:05|12.9
201202|13407291|2016-09-30 21:58:06|2016-09-30 22:03:35|5.48
201203|13394893|2016-09-30 22:03:47|2016-09-30 22:08:52|5.08
201204|13462104|2016-09-30 22:09:05|2016-09-30 22:14:53|5.8
201205|13530036|2016-09-30 22:15:05|2016-09-30 22:25:13|10.13
201206|13462659|2016-09-30 22:25:38|2016-09-30 22:29:24|3.77
201207|13524123|2016-09-30 22:29:32|2016-09-30 22:35:55|6.38
201208|13770339|2016-09-30 22:36:11|2016-09-30 22:42:23|6.2
201209|13724590|2016-09-30 22:42:38|2016-09-30 22:46:39|4.02
201210|13758254|2016-09-30 22:46:54|2016-09-30 22:51:21|4.45
201211|13834007|2016-09-30 22:51:32|2016-09-30 22:56:25|4.88
201212|13672501|2016-09-30 22:56:35|2016-09-30 23:00:56|4.35
201301|13874422|2016-09-30 23:01:11|2016-09-30 23:05:21|4.17
201302|13602566|2016-09-30 23:05:21|2016-09-30 23:09:54|4.55
201303|13942944|2016-09-30 23:09:58|2016-09-30 23:18:32|8.57
201304|13722930|2016-09-30 23:18:56|2016-09-30 23:28:06|9.17
201305|13837042|2016-09-30 23:28:27|2016-09-30 23:45:38|17.18
201306|13717464|2016-09-30 23:46:00|2016-09-30 23:53:20|7.33
201307|13887105|2016-09-30 23:53:25|2016-10-01 00:00:37|7.2
201308|13893436|2016-10-01 00:00:55|2016-10-01 00:07:39|6.73
201309|13978918|2016-10-01 00:08:03|2016-10-01 00:13:59|5.93
201310|13964596|2016-10-01 00:14:07|2016-10-01 00:17:58|3.85
201311|13966149|2016-10-01 00:18:18|2016-10-01 00:24:28|6.17
201312|14211619|2016-10-01 00:24:47|2016-10-01 00:33:52|9.08
201401|14164022|2016-10-01 00:34:12|2016-10-01 00:44:48|10.6
201402|14228956|2016-10-01 00:44:49|2016-10-01 00:52:26|7.62
201403|14160545|2016-10-01 00:52:32|2016-10-01 01:05:17|12.75
201404|14270028|2016-10-01 01:05:40|2016-10-01 01:14:33|8.88
201405|14042255|2016-10-01 01:14:58|2016-10-01 01:21:38|6.67
201406|14134906|2016-10-01 01:22:04|2016-10-01 01:29:19|7.25
201407|14389582|2016-10-01 01:29:44|2016-10-01 01:45:08|15.4
201408|14131123|2016-10-01 01:45:32|2016-10-01 01:51:07|5.58
201409|14143630|2016-10-01 01:51:09|2016-10-01 01:55:50|4.68
201410|14076919|2016-10-01 01:56:04|2016-10-01 02:00:57|4.88
201411|14109947|2016-10-01 02:00:59|2016-10-01 02:07:17|6.3
201412|14054243|2016-10-01 02:07:29|2016-10-01 02:12:55|5.43
201501|14026988|2016-10-01 02:13:05|2016-10-01 02:17:41|4.6
201502|14042558|2016-10-01 02:17:42|2016-10-01 02:22:26|4.73
201503|14004026|2016-10-01 02:22:40|2016-10-01 02:27:15|4.58
201504|13787678|2016-10-01 02:27:20|2016-10-01 02:35:33|8.22
201505|13779988|2016-10-01 02:35:51|2016-10-01 02:40:51|5.0
201506|13753665|2016-10-01 02:40:58|2016-10-01 02:46:40|5.7
201507|13861879|2016-10-01 02:46:53|2016-10-01 02:53:12|6.32
201508|13823829|2016-10-01 02:53:35|2016-10-01 02:58:28|4.88
201509|13912767|2016-10-01 02:58:55|2016-10-01 03:11:22|12.45
201510|14002752|2016-10-01 03:11:49|2016-10-01 03:27:47|15.97
201511|13815096|2016-10-01 03:28:04|2016-10-01 03:37:37|9.55
201512|13980491|2016-10-01 03:38:03|2016-10-01 03:47:26|9.38
201601|14020581|2016-10-01 03:47:41|2016-10-01 04:37:41|50.0
201602|14008965|2016-10-01 04:37:44|2016-10-01 04:42:55|5.18
201603|13868458|2016-10-01 04:43:04|2016-10-01 04:53:35|10.52
201604|13932487|2016-10-01 04:54:02|2016-10-01 05:03:32|9.5
201605|13872465|2016-10-01 05:03:45|2016-10-01 05:08:17|4.53
201606|13849866|2016-10-01 05:08:32|2016-10-01 10:25:20|316.8
201607|13954468|2016-10-01 10:25:20|2016-10-01 11:47:09|81.82
201608|13904601|2016-10-01 11:47:15|2016-10-01 11:52:10|4.92
=======
/** Disk-Space Utilization Report For bolsa_familia3.db
Page size in bytes................................ 32768
Pages in the whole file (measured)................ 711362
Pages in the whole file (calculated).............. 711361
Pages that store data............................. 711252 99.985%
Pages on the freelist (per header)................ 0 0.0%
Pages on the freelist (calculated)................ 1 0.0%
Pages of auto-vacuum overhead..................... 109 0.015%
Number of tables in the database.................. 11
Number of indices................................. 2
Number of defined indices......................... 1
Number of implied indices......................... 1
Size of the file in bytes......................... 23309910016
Bytes of user payload stored...................... 15795879478 67.8%
*** Page counts for all tables with their indices
*****************************
BOLSAS_FAMILIA.................................... 663535 93.3%
FAVORECIDOS....................................... 31315 4.4%
FAVORECIDOS_NOMES................................. 16383 2.3%
MUNICIPIOS_FAVORECIDOS_COUNT...................... 6 0.0%
MUNICIPIOS........................................ 5 0.0%
FONTES_FINALIDADE................................. 2 0.0%
PROGRAMAS......................................... 2 0.0%
BOLSAS_FAMILIA_VALOR_GROUPED...................... 1 0.0%
FAVORECIDOS_COUNT_BY_ANO_MES...................... 1 0.0%
PROCESSED......................................... 1 0.0%
SQLITE_MASTER..................................... 1 0.0%
*** Page counts for all tables and indices separately
*************************
BOLSAS_FAMILIA.................................... 663535 93.3%
FAVORECIDOS....................................... 31315 4.4%
FAVORECIDOS_NOMES................................. 16383 2.3%
MUNICIPIOS_FAVORECIDOS_COUNT...................... 6 0.0%
MUNICIPIOS........................................ 5 0.0%
BOLSAS_FAMILIA_VALOR_GROUPED...................... 1 0.0%
FAVORECIDOS_COUNT_BY_ANO_MES...................... 1 0.0%
FONTES_FINALIDADE................................. 1 0.0%
PROCESSED......................................... 1 0.0%
PROGRAMAS......................................... 1 0.0%
PROGRAMAS_ALL_IDX................................. 1 0.0%
SQLITE_AUTOINDEX_FONTES_FINALIDADE_1.............. 1 0.0%
SQLITE_MASTER..................................... 1 0.0%
*** All tables and indices
****************************************************
Percentage of total database...................... 99.985%
Number of entries................................. 971858491
Bytes of storage consumed......................... 23306305536
Bytes of payload.................................. 15795881424 67.8%
Average payload per entry......................... 16.25
Average unused bytes per entry.................... 0.01
Average fanout.................................... 2624.00
Maximum payload per entry......................... 238
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 271
Primary pages used................................ 710981
Overflow pages used............................... 0
Total pages used.................................. 711252
Unused bytes on index pages....................... 1260048 14.2%
Unused bytes on primary pages..................... 8687950 0.037%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 9947998 0.043%
*** All tables
****************************************************************
Percentage of total database...................... 99.984%
Number of entries................................. 971858488
Bytes of storage consumed......................... 23306240000
Bytes of payload.................................. 15795881336 67.8%
Average payload per entry......................... 16.25
Average unused bytes per entry.................... 0.01
Average fanout.................................... 2624.00
Maximum payload per entry......................... 238
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 271
Primary pages used................................ 710979
Overflow pages used............................... 0
Total pages used.................................. 711250
Unused bytes on index pages....................... 1260048 14.2%
Unused bytes on primary pages..................... 8622527 0.037%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 9882575 0.042%
*** All indices
***************************************************************
Percentage of total database...................... 0.0%
Number of entries................................. 3
Bytes of storage consumed......................... 65536
Bytes of payload.................................. 88 0.13%
Average payload per entry......................... 29.33
Average unused bytes per entry.................... 21807.67
Maximum payload per entry......................... 41
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 2
Overflow pages used............................... 0
Total pages used.................................. 2
Unused bytes on primary pages..................... 65423 99.83%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 65423 99.83%
*** Table BOLSAS_FAMILIA
******************************************************
Percentage of total database...................... 93.3%
Number of entries................................. 934135285
Bytes of storage consumed......................... 21742714880
Bytes of payload.................................. 14519300225 66.8%
B-tree depth...................................... 3
Average payload per entry......................... 15.54
Average unused bytes per entry.................... 0.01
Average fanout.................................... 2664.00
Non-sequential pages.............................. 104 0.016%
Maximum payload per entry......................... 22
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 249
Primary pages used................................ 663286
Overflow pages used............................... 0
Total pages used.................................. 663535
Unused bytes on index pages....................... 1045285 12.8%
Unused bytes on primary pages..................... 7415971 0.034%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 8461256 0.039%
=====
local _sq_profile_calls_at, _sq_profile_calls, _sq_profile_total,
_sq_profile_this,
_sq_profile_start_time, _sq_profile_end_time;
local trget = table_rawget;
local trset = table_rawset;
local function profileReset()
{
_sq_profile_calls_at = {};
_sq_profile_calls = {};
_sq_profile_total = {};
_sq_profile_this = {};
_sq_profile_start_time = 0;
_sq_profile_end_time = 0;
}
profileReset();
local function profileDebughook(event_type,sourcefile,line,funcname)
{
//local fname = format("%s:%d", funcname ? funcname : "unknown", line);
local fname = funcname ? funcname : "unknown";
local srcfile=sourcefile ? sourcefile : "unknown";
local fname_at = format("%s:%d:%s", fname, line, srcfile);
//local fname_at = fname + ":" + line + ":" + srcfile;
switch (event_type) {
//case 'l': //called every line(that contains some code)
//::print("LINE line [" + line + "] func [" + fname + "]");
//::print("file [" + srcfile + "]\n");
//break;
case 'c': //called when a function has been called
//::print("LINE line [" + line + "] func [" + fname + "]");
//::print("file [" + srcfile + "]\n");
trset(_sq_profile_calls_at, fname_at,
trget(_sq_profile_calls_at, fname_at, 0) + 1);
trset(_sq_profile_this, fname, os.getmillicount());
break;
case 'r': //called when a function returns
//::print("LINE line [" + line + "] func [" + fname + "]");
//::print("file [" + srcfile + "]\n");
local time = os.getmillicount() - trget(_sq_profile_this,
fname, 0);
trset(_sq_profile_total, fname, trget(_sq_profile_total,
fname, 0) + time);
trset(_sq_profile_calls, fname, trget(_sq_profile_calls,
fname, 0) + 1);
break;
}
}
local function profileStart()
{
profileReset();
_sq_profile_start_time = os.getmillicount();
setdebughook(profileDebughook);
}
local function profileEnd()
{
setdebughook(null);
_sq_profile_end_time = os.getmillicount();
}
local function profileDump()
{
// print the results
auto function ignoreFuncName(fname)
{
return (fname == "profileStart" || fname == "profileEnd" ||
fname == "profileReset");
}
local total_time = (_sq_profile_end_time - _sq_profile_start_time)
/ 1000.0;
print(format("Profile info: %.3f seconds", total_time));
local info_ary = [];
foreach( fname, time in _sq_profile_total )
{
time /= 1000.0;
if(ignoreFuncName(fname)) continue;
local relative_time = time / (total_time / 100.0);
local rt_int = relative_time.tointeger();
local rt_frac = ((relative_time - rt_int) * 100).tointeger();
info_ary.append(format("%02d.%02d %% in %.3f seconds after %d
calls to %s", rt_int, rt_frac, time, table_rawget(_sq_profile_calls,
fname, 0), fname));
}
info_ary.sort(@(a,b) a<b ? 1 : (a>b ? -1 : 0));
foreach(line in info_ary)
{
print(line);
}
info_ary.clear();
foreach( fname, count in _sq_profile_calls_at )
{
if(ignoreFuncName(fname)) continue;
info_ary.append(format("%6d\tcalls to %s", count, fname));
}
info_ary.sort(@(a,b) a<b ? 1 : (a>b ? -1 : 0));
foreach(line in info_ary)
{
print(line);
}
}
local _sq_time_start = 0;
local function sqStartTimer()
{
_sq_time_start = os.getmillicount();
}
local function sqGetElapsedTimer()
{
return os.getmillicount() - _sq_time_start;
}
local function sqPrintElapsedTimer()
{
print(format("Elapsed time %.3f seconds", sqGetElapsedTimer()));
}
__max_print_stack_str_size <- 1000;
auto dbname = "bolsa_familia3.db";
auto db = SQLite3(dbname);
//auto db = SQLite3(":memory:");
//db.restore(dbname);
db.exec_dml("PRAGMA synchronous = 0;");
db.exec_dml("PRAGMA journal_mode = WAL");
//db.exec_dml("PRAGMA journal_mode = MEMORY;");
//db.exec_dml("PRAGMA journal_mode = OFF;");
//db.exec_dml("PRAGMA locking_mode = EXCLUSIVE;");
db.exec_dml("PRAGMA temp_store = MEMORY;");
//db.exec_dml("PRAGMA threads = 4;");
//db.exec_dml("PRAGMA mmap_size = 64000000;");
auto gigabyte = 1024*1024*1024;
db.exec_dml("PRAGMA mmap_size=" + (gigabyte*16));
//print("mmap_size", db.exec_get_one("PRAGMA mmap_size;"));
//db.exec_dml("PRAGMA cache_size = -64000");
//print("cache_size", db.exec_get_one("PRAGMA cache_size;"));
//db.exec_dml("analyze;");
db.exec_dml([==[
create table if not exists processed(
id integer primary key,
count integer,
start_time datetime default current_timestamp,
end_time datetime
);
create table if not exists municipios(
id integer primary key,
uf varchar collate nocase_slna,
nome varchar collate nocase_slna
);
create table if not exists programas(
id integer primary key,
codigo_funcao integer not null,
codigo_subfuncao integer not null,
codigo_programa integer not null,
codigo_acao integer not null
);
create unique index if not exists programas_all_idx on programas(
codigo_funcao, codigo_subfuncao, codigo_programa, codigo_acao);
create table if not exists fontes_finalidade(
id integer primary key,
nome varchar collate nocase_slna unique
);
create table if not exists favorecidos(
id integer primary key,
nome varchar collate nocase_slna,
municipio_id integer,
programa_id integer not null,
fonte_finalidade_id integer not null,
first_ano_mes integer
);
create table if not exists bolsas_familia(
id integer primary key,
favorecido_id integer not null,
valor_parcela numeric,
mes_competencia integer
);
--create index if not exists bolsas_familia_favorecidos_idx on
bolsas_familia(favorecido_id);
--insert or ignore into favorecidos(id,
nome,programa_id,fonte_finalidade_id) select nis, nome,1,1 from
adb.favorecidos order by nis;
]==]);
auto stmt_municipios = db.prepare("insert or ignore into municipios(id,
uf, nome) values(?,?,?);");
auto stmt_fontes_finalidade = db.prepare("insert or ignore into
fontes_finalidade(nome) values(?);");
auto stmt_fontes_programas = db.prepare([==[
insert or ignore into programas(
codigo_funcao, codigo_subfuncao, codigo_programa, codigo_acao
) values(?,?,?,?);
]==]);
auto stmt_favorecidos = db.prepare([==[
insert or ignore into favorecidos(
id, nome, municipio_id, programa_id, fonte_finalidade_id,
first_ano_mes
) values(
?,?,?,
(select id from programas
where
codigo_funcao=?
and codigo_subfuncao=?
and codigo_programa=?
and codigo_acao=?),
(select id from fontes_finalidade where nome=?),
?);
]==]);
auto stmt_update_favorecidos = db.prepare([==[
update favorecidos set municipio_id=?,
programa_id=(select id from programas
where
codigo_funcao=?
and codigo_subfuncao=?
and codigo_programa=?
and codigo_acao=?),
fonte_finalidade_id=(select id from fontes_finalidade where
nome=?),
first_ano_mes=?
where id=?;
]==]);
auto stmt_get_favorecido = db.prepare("select municipio_id from
favorecidos where id=?");
auto stmt_bolsas_familia = db.prepare([==[
insert into bolsas_familia(
favorecido_id,
valor_parcela,
mes_competencia
) values (?, ?, ?);
]==]);
db.exec_dml("begin;");
auto function updateProcessed(count, mes_competencia)
{
db.exec_one_dml("update processed set count=?,
end_time=current_timestamp where id=?", count, mes_competencia);
db.exec_dml("commit;begin;");
db.exec_dml("PRAGMA wal_checkpoint(FULL);");
}
auto function getCsvUtf8Fn(csv_fn)
{
return csv_fn + "-utf-8";
}
auto zip_fn_list = [];
foreach(zip_fn in sqfs.dir("zip-data"))
{
if(zip_fn.indexOf("BolsaFamiliaFolhaPagamento") >= 0)
{
zip_fn_list.append(zip_fn);
}
}
zip_fn_list.sort();
auto municipios_list = {};
auto stmt_tmp = db.prepare("select id from municipios;");
while(stmt_tmp.next_row()) {trset(municipios_list, stmt_tmp.col(0), true);}
foreach(zip_fn in zip_fn_list)
{
print(zip_fn);
auto mes_competencia = zip_fn.match("^%d+").tointeger();
auto last_processed = db.exec_get_one("select ifnull((select
max(id) from processed), 0)");
last_processed = last_processed.tointeger();
if(last_processed > mes_competencia) continue;
auto csv_fn = zip_fn.replace(".zip", ".csv");
//auto cmd = format("unzip -p zip-data/%s | iconv -f ISO_8859-1 -t
utf-8 | tr -d '\\0'", zip_fn);
//auto cmd = format("unzip -p zip-data/%s | make-UTF8 | tr -d '\\0'
", zip_fn);
//auto cmd = format("unzip -p zip-data/%s | tr -d '\\0' |
mess2utf8", zip_fn);
auto cmd = format("unzip -p zip-data/%s | filter-latin1toutf8",
zip_fn);
auto fd = popen(cmd, "r");
/*
if(!existsfile(getCsvUtf8Fn(csv_fn)))
{
cmd = "unzip zip-data/" + zip_fn;
print(cmd);
os.system(cmd);
cmd = "sh cvt2utf8.sh " + csv_fn;
print(cmd);
os.system(cmd);
cmd = "rm " + csv_fn;
print(cmd);
os.system(cmd);
}
csv_fn = getCsvUtf8Fn(csv_fn);
auto fd = file(csv_fn, "r");
*/
//auto atualizacao = fd.read_line();
auto header = fd.read_line();
auto csv_sep = '\t';
auto rec = header.split_csv(csv_sep);
auto field_count = rec.len();
print("Fields count:", field_count);
print(header);
db.exec_one_dml("insert or ignore into processed(id, count)
values(?,0)", mes_competencia);
auto last_count_processed = db.exec_get_one("select count from
processed where id=?", mes_competencia) || 0;
auto line, count = 0, count2=0, count3 = 0, last_count3= 0, count5
= 0, count_step = 5000, millitime_point=0;
//auto function readline(){return fd.read_line();}
while( (line = fd.read_line()) )
{
++count;
if(last_count_processed >= count) continue;
//if(!count2) profileStart();
//print("line",__LINE__);
//auto function splitline(){
rec = line.split_csv(csv_sep);
//}
//splitline();
//print("line",__LINE__);
if(rec.len() == field_count)
{
/*
foreach(idx, elm in rec)
{
rec[idx] = elm.gsub("\"([^\"]*)\"", "%1");
}
*/
/*
UF text,
Código_SIAFI_Município text,
Nome_Município text,
Código_Função text,
Código_Subfunção text,
Código_Programa text,
Código_Ação text,
NIS_Favorecido text,
Nome_Favorecido text,
Fonte_Finalidade text,
Valor_Parcela text,
Mês_Competência text
*/
auto uf = rec[0],
codigo_siafi = rec[1].tointeger(),
nome_municipio = rec[2],
codigo_funcao = rec[3],
codigo_subfuncao = rec[4],
codigo_programa = rec[5],
codigo_acao = rec[6],
nis_favorecido = rec[7].tointeger(),
nome_favorecido = rec[8],
fonte_finalidade = rec[9],
valor_parcela = rec[10];
//auto function func1(){
if(!trget(municipios_list,codigo_siafi, false))
{
stmt_municipios.bind_exec(codigo_siafi, uf,
nome_municipio);
trset(municipios_list,codigo_siafi, true);
}
//}
//func1();
//auto function func2(){
stmt_fontes_programas.bind_exec(codigo_funcao,
codigo_subfuncao,
codigo_programa, codigo_acao);
//}
//func2();
//auto function func4(){
stmt_fontes_finalidade.bind_exec(fonte_finalidade);
//}
//func4();
//auto function func3(){
stmt_get_favorecido.bind(1, nis_favorecido);
if(stmt_get_favorecido.next_row())
{
if(stmt_get_favorecido.col(0) == db.Null)
{
//print("updating favorecidos", count3);
stmt_update_favorecidos.bind_exec(
codigo_siafi,
codigo_funcao, codigo_subfuncao,
codigo_programa, codigo_acao,
fonte_finalidade,
mes_competencia,
nis_favorecido
);
}
}
else
{
//print("inserting favorecidos", count3);
stmt_favorecidos.bind_exec(
nis_favorecido,
nome_favorecido,
codigo_siafi,
codigo_funcao, codigo_subfuncao,
codigo_programa, codigo_acao,
fonte_finalidade,
mes_competencia
);
}
stmt_get_favorecido.reset();
count3 += db.changes();
//}
//func3();
//auto function func5(){
stmt_bolsas_familia.bind_exec(nis_favorecido,
valor_parcela, mes_competencia);
//count5 += db.changes();
//}
//func5();
//print("line",__LINE__);
if( (count % count_step) == 0)
{
auto millitime_now = os.getmillicount();
auto milltime_len = millitime_now - millitime_point;
millitime_point = millitime_now;
auto last_changes = count3 - last_count3;
print(count, last_changes, count3, count_step -
last_changes, milltime_len);
last_count3 = count3;
updateProcessed(count, mes_competencia);
//if(++count2 > 10){profileEnd(); break;}
}
}
}
fd.close();
//cmd = "rm " + csv_fn;
//print(cmd);
//os.system(cmd);
print(count, mes_competencia);
updateProcessed(count, mes_competencia);
//break;
//if(count2 > 10) break;
}
db.exec_dml("commit;");
//db.backup(dbname);
profileDump();
print("Done");
=====
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
