Richard,
I did some tests on my Windows 7 64b 4 CPU core with SQL Server 2012, with Genero BDL 2.50.16.
I wrote a little test program, using a table with 10,000 rows, with a integer primary key and a varchar(200).
The program basically does a similar task as yours, (fetch large rows and insert into another table):
main
define tm datetime hour to fraction(3)
define x,i int, v varchar(200),
rec record
pk int,
v1 varchar(200),
v2 varchar(200),
v3 varchar(200),
v4 varchar(200),
v5 varchar(200),
v6 varchar(200),
v7 varchar(200),
v8 varchar(200),
v9 varchar(200),
v10 varchar(200)
end record
connect to "msvtest1+driver='dbmsnc'" user "msvuser" using "fourjs"
set lock mode to wait 5
{
whenever error continue
drop table t1
drop table t2
whenever error stop
create table t1 ( pk int primary key, name varchar(200), addr varchar(200) )
for i=1 to 10000
let v = "aaaaaaaaaaaaaaaaaaaaaaaa"||i
insert into t1 values (i, v, v)
end for
create table t2 ( pk int, name varchar(200) )
exit program
}
declare c1 cursor for select pk, name, name, name, name, name, name, name, name, name, name from t1 order by pk
for x=1 to arg_val(1)
foreach c1 into rec.*
insert into t2 values (rec.pk, rec.v1)
end foreach
end for
end main
I watched the CPU usage in the Resource Monitor.
When I run the program alone (fglrun prog 4), fetching 4 x 10,000 rows and inserting 40,000 new rows in the second table, it takes about 23 secs.
The 4 CPUs are used equally.
When I run 4 individual programs (4 x fglrun prog 1), fetching 10,000 rows and inserting 10,000 new rows each, every single program takes about 8 secs.
I can of course execute the 4 programs in parallel, and the time to process all the 40,000 rows is then about 8 secs.
The 4 CPUs are used equaly.
So as expected, it takes more time to run a unique program processing (sequentially) the same number of rows as 4 individual programs running in parallel.
If it's not to difficult to define the SQL filters to split rows to process over several programs, maybe this is a good option for you.
But if you want to use a unique program to process all rows, we may continue to investigate to reduce the execution time...
I suggest that you set FGLSQLDEBUG and redirect (stderr) the SQL debug log to a file, in fact you can see the execution time of SQL statements:
SQL: INSERT INTO t1 VALUES (?,?,?)
| 4gl source : 4procs.4gl line=31
| esm.c:00328(3) : Nat stmt1 = insert into t1 VALUES (?,?,?)
| sqlcode : 0
| curr driver : ident='dbmesm'
| curr connection : ident='esm_msvtest1_ida_utf8+driver='dbmesm'' (dbspec=[esm_msvtest1_ida_utf8+driver='dbmesm'])
| using: 3
| t: INTEGER f:01 v:'3245'
| t: VARCHAR(200) f:01 v:'aaaaaaaaaaaaaaaaaaaaaaaa3245'
| t: VARCHAR(200) f:01 v:'aaaaaaaaaaaaaaaaaaaaaaaa3245'
| Execution time : 0 00:00:00.00151
(last line)
Maybe some SQL statements take much more time as expected (for ex, 2-3 seconds for an INSERT is not normal), because SQL Server takes some time for perform a given statement, to write TX logs or so?
Run both solutions (the single program and the 4 programs), and analyse the FGLSQLDEBUG output (grep for "Execution time")
Seb
Sign in for full access
Help
