Script processing

I'm getting extremely bad performance with large SQL scripts.

I did a test with 100000 insert statements and executed the script inside an
explicit transaction. We tend to be rather patient in my age, but 390
seconds to process the script pumped my adrenalin production up to
industrial levels. Inserting the same number of records with a navigational
routine is 23 seconds on my system.

Since a singleton statement updating all the 100000 records ( 9 fields) is
very efficient - 7 seconds - I believe something must be wrong with how
scripts are processed ?

Ole Willy Tuv

Ole,

> I'm getting extremely bad performance with large SQL scripts.
>
> I did a test with 100000 insert statements and executed the script inside
an
> explicit transaction. We tend to be rather patient in my age, but 390
> seconds to process the script pumped my adrenalin production up to
> industrial levels.

please change your SQL from something like this:

INSERT INTO TABLE1 VALUES (1 , 'Martin' , 'STAFFORD' , Null , Null);
INSERT INTO TABLE1 VALUES (2 , 'Lally' , Null , Null , Null);
INSERT INTO TABLE1 VALUES (3 , 'Swain' , 'STOKE-ON-TRENT' , Null , Null);
INSERT INTO TABLE1 VALUES (4 , 'Poulton' , 'TAMWORTH' , Null , Null);
INSERT INTO TABLE1 VALUES (5 , 'Knight' , 'STAFFORD' , Null , Null);

into something like this:

INSERT INTO TABLE1 VALUES
(1 , 'Martin' , 'STAFFORD' , Null , Null),
(2 , 'Lally' , Null , Null , Null),
(3 , 'Swain' , 'STOKE-ON-TRENT' , Null , Null),
(4 , 'Poulton' , 'TAMWORTH' , Null , Null),
(5 , 'Knight' , 'STAFFORD' , Null , Null);


> Inserting the same number of records with a navigational
> routine is 23 seconds on my system.
Most likely your navigational code wasn't really optimized... It should be a
lot faster than that for just 100k records ;-)

> Since a singleton statement updating all the 100000 records ( 9 fields) is
> very efficient - 7 seconds - I believe something must be wrong with how
> scripts are processed ?

No. It's a limitation of how the current SQL parser works. It's unlikely
this will change for v1.

The first type of statement will create a tree of evaluation node objects
for each line. Resulting in 100000s of objects being allocated.

The 2nd form will create one tree of sql nodes with just a handful of
objects and write all the values into a single temporary in-memory table
during parsing.

--
Thorsten Engler [NexusDB Architect]

Thorsten,

<< No. It's a limitation of how the current SQL parser works. It's unlikely
this will change for v1. >>

Hope this will change at least for v2 then, allowing NexusDB to perform at
it's best with standard SQL syntax.

<< The 2nd form will create one tree of sql nodes with just a handful of
objects and write all the values into a single temporary in-memory table
during parsing. >>

I actually like the syntax. Do you know if the syntax is compliant with the
recent standards ?

Anyhow, the script now executed in 113 seconds wrapped in a single
transaction. On the right side, when I separated the 100000 statements into
smaller transaction batches, e.g. 1000, the whole lot executed in 19
seconds, which is faster than the navigational routine.

Ole

> Hope this will change at least for v2 then, allowing NexusDB to perform at
> it's best with standard SQL syntax.
The new parser code for v2 might allows us to detect a sequence of
compatible INSERT statements and merge them at the parser level. But I
cannot guarantee this currently.

> I actually like the syntax. Do you know if the syntax is compliant with
the
> recent standards ?
No idea. Per?

> Anyhow, the script now executed in 113 seconds wrapped in a single
> transaction. On the right side, when I separated the 100000 statements
into
> smaller transaction batches, e.g. 1000, the whole lot executed in 19
> seconds, which is faster than the navigational routine.

I'm not sure why I would take that long if executed in one statement.
Depending on your table structure (recordsize and indices) it might be that
the server reached the MaxRAM setting and had to move data into temporary
storage.

When using batches of 1000 records it's only faster than the navigational
routines as long as you don't use BatchAppend mode.

--
Thorsten Engler [NexusDB Architect]

Thorsten,

<< I'm not sure why I would take that long if executed in one statement.
Depending on your table structure (recordsize and indices) it might be that
the server reached the MaxRAM setting and had to move data into temporary
storage. >>

I guess that's the case. My OS was very slow after the execution, obviously
reallocating memory.

<< When using batches of 1000 records it's only faster than the navigational
routines as long as you don't use BatchAppend mode. >>

I suppose navigational BatchAppend and SQL script would currently be equally
fast. However, if you added explicit transaction support to the SQL grammar
(START TRANSACTION, COMMIT [WORK]), the script could be piped over to the
server in one go and executed with transactions explicitly triggered in the
script. This would mean less chatting and probably the fastest batch
processing.

Ole

"Thorsten Engler [NDA]" <thorsten.englerNO@SPAMnexusdb.com> wrote in message
news:3f7ed526$1@wic040d....
> > I actually like the syntax. Do you know if the syntax is compliant with
> the
> > recent standards ?
> No idea. Per?

This is ANSI 92 compliant syntax.

- Per

Kind boss Engler san,

<< However, if you added explicit transaction support to the SQL grammar
(START TRANSACTION, COMMIT [WORK]), the script could be piped over to the
server in one go and executed with transactions explicitly triggered in the
script. >>

Any chance of transaction support being added to the SQL grammar ?

Ole

Wishlist, issue #463. :-)


--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f855a43@wic040d....
> Kind boss Engler san,
>
> << However, if you added explicit transaction support to the SQL grammar
> (START TRANSACTION, COMMIT [WORK]), the script could be piped over to the
> server in one go and executed with transactions explicitly triggered in
the
> script. >>
>
> Any chance of transaction support being added to the SQL grammar ?
>
> Ole
>
>

I've added #463 to the wishlist. Not sure if it will appear before v2.

--


Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


"Ole Willy Tuv" <owtuv@online.no> wrote in message news:3f855a43@wic040d....
> Kind boss Engler san,
>
> << However, if you added explicit transaction support to the SQL grammar
> (START TRANSACTION, COMMIT [WORK]), the script could be piped over to the
> server in one go and executed with transactions explicitly triggered in
the
> script. >>
>
> Any chance of transaction support being added to the SQL grammar ?
>
> Ole
>
>