Hello,

I have a program that sometimes needs to insert large number of records in short period of
time. Now I am just executing parametrized INSERT statement.
I am wondering if it is good idea to create external module to speed things up. I think of
creating a function with one BLOB parameter. This BLOB would be stream containing
parameter data for multiple records. My external function would read this stream and
execute multiple inserts in one transaction. This way I would avoid communication delays.

Is it good idea or it won't help to improve performance?

Regards Marcin.

Marcin,

<< I have a program that sometimes needs to insert large number of records
in short period of time. Now I am just executing parametrized INSERT
statement.  I am wondering if it is good idea to create external module to
speed things up. I think of creating a function with one BLOB parameter.
This BLOB would be stream containing parameter data for multiple records. My
external function would read this stream and execute multiple inserts in one
transaction. This way I would avoid communication delays.

Is it good idea or it won't help to improve performance? >>

It's a good idea, but you don't need to us an external module.  Just create
a stored procedure or script, and then have it execute the parameterized
INSERTs.  The execution will occur completely on the ElevateDB Server.

BTW, where is the parameter data coming from ?  Is it user-entered, or
application-generated ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi,

"It's a good idea, but you don't need to us an external module.  Just create
a stored procedure or script, and then have it execute the parameterized
INSERTs.  The execution will occur completely on the ElevateDB Server."

I am using parametrized Query right now, but the problem is I can insert only one row.
I want to insert multiple rows at once.

"BTW, where is the parameter data coming from ?  Is it user-entered, or
application-generated ?"

Data is application generated. Sometimes my application wants to insert 100
or so records (and return inserted rows ids). My application would put these data
into stream/BLOB parameter, external module and stored procedure would do the rest.
ElevateDB is very efficient but sometimes it is not enough for my application. Now I am
using parametrized queries and application scripts to store data but looking for
more efficient way.

regards, Marcin.

Marcin,

<< Data is application generated. Sometimes my application wants to insert
100 or so records (and return inserted rows ids). My application would put
these data into stream/BLOB parameter, external module and stored procedure
would do the rest.  ElevateDB is very efficient but sometimes it is not
enough for my application. Now I am using parametrized queries and
application scripts to store data but looking for more efficient way. >>

What I would do is something like this:

1) Create a script or stored procedure that accepts two VARCHAR parameters:

SCRIPT (IN InParameters VARCHAR, OUT OutParameters VARCHAR)
BEGIN
.....
END

2) Compile the attached external module and copy it into the configuration
path.

3) Create a function like this:

CREATE FUNCTION "ParseParam" (IN "Params" VARCHAR COLLATE ANSI)
RETURNS VARCHAR COLLATE ANSI
EXTERNAL NAME "parammodule"

4) Call the function in the external module like this:

SCRIPT (IN InParameters VARCHAR, OUT OutParameters VARCHAR)
BEGIN
  DECLARE CurParam VARCHAR DEFAULT '';
  DECLARE ParamPos INTEGER DEFAULT 0;
  DECLARE InsertStmt STATEMENT;

  PREPARE InsertStmt FROM 'INSERT INTO MyTable VALUES (:ParamValue,
ColumnValue)';

  SET CurParam=ParseParam(InParameters);
  SET ParamPos=ParamPos+1;
  WHILE (CurParam <> '') DO
     EXECUTE InsertStmt USING CurParam;
     SET OutParameters=OutParameters+'ParamColumn='+CurParam+#13+#10;
     SET CurParam=ParseParam(InParameters);
     SET ParamPos=ParamPos+1;
  END WHILE;

END

This isn't completely tested, but should give you a good idea of how to
start.  INSERT statements are automatically set up with IN/OUT parameters,
so any parameters to an INSERT statement are automatically set to also
return any column value that is auto-assigned by EDB, such as an IDENTITY
column.

Also, this example only assigns one column value.  If you need to assign
multiple column values, you'll need to modify the parsing in the external
module, etc. accordingly.

Finally, this example assumes that the column/param values are all VARCHAR
strings, so you'll have to add any additional CASTing if you're using other
types.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thank you very much for your ideas and code. I created the module and this way of saving records is extremely fast! My module contains 3 functions: Parse, GetNext, Cleanup, OnError. These functions are used in stored procedure that looks like this:

PROCEDURE SaveData(IN "DataStream" BLOB, OUT "ErrorStream" BLOB)
BEGIN
  DECLARE EofStream BOOL;

  DECLARE data1 Integer;
  DECLARE data2 INTEGER;
  ...

  CALL Parse(DataStream);
  CALL GetNext(EofStream, data1, data2, ...);
  IF (NOT EofStream) THEN
     PREPARE Stmt FROM
        ''INSERT INTO MyTable (field1, field2, ...) VALUES (?, ?, ...)'';
     START TRANSACTION ON TABLES MyTable;
     BEGIN
        WHILE NOT EofStream DO
           EXECUTE Stmt USING data1, data2, ...;
           CALL GetNext(EofStream, data1, data2, ...);
        END WHILE;
        COMMIT;
     EXCEPTION
        ROLLBACK;
        RAISE;
     END;
  END IF;

  CALL Cleanup(ErrorStream);
END;

I have more than 2 data parametres so "..." represent it in my example. I put OnError function in OnError trigger for MyTable. This procedure is 20 timer faster then parametrized query I was using before. For Parse and GetNext to work properly I had to declare global structure/stream in my module. Parse creates the structure and assigns data to it (this is actually memory buffer used as stream). GetNext reads data from that stream and Cleanup destroys structure.

Once again thank you for help.

Marcin

Marcin,

<< Thank you very much for your ideas and code. I created the module and
this way of saving records is extremely fast! My module contains 3
functions: Parse, GetNext, Cleanup, OnError. These functions are used in
stored procedure that looks like this:  >>

Very nice.  Although it does remind me that I need to add a FINALLY
statement so that one can be assured that native external modules are
cleaned up properly, not matter what happens in the routine.

--
Tim Young
Elevate Software
www.elevatesoft.com