SP-Based Import (Example)

Home

4D v16

The following example shows how importing data can be dramatically accelerated in Client/Server architecture. The Regular Import method allows you to test how long it takes to import records using the IMPORT TEXT command on the Client side:

  ` Regular Import Project Method

 $vhDocRef:=Open document("")

 If(OK=1)

    CLOSE DOCUMENT($vhDocRef)

    INPUT FORM([Table1];"Import")

    $vhStartTime:=Current time

    IMPORT TEXT([Table1];Document)

    $vhEndTime:=Current time

    ALERT("It took "+String(0+($vhEndTime-$vhStartTime))+" seconds.")

 End if

With the regular import data, 4D parses the text file, then for each record, creates a new record, fills out the fields with the imported data and sends the record to the Server machine to be added to the database. There are consequently many requests going over the network. A way to optimize the operation is to use a stored procedure to do the job locally on the Server machine. The client machine loads the document into a BLOB, then starts a stored procedure that passes the BLOB as parameter. The stored procedure stores the BLOB in a document on the server machine disk, then imports the document locally. The import of the data is therefore performed locally (at a speed comparable to that of a local version of 4D) because most the network requests have been eliminated.

Here is the CLIENT IMPORT project method. Executed on the Client machine, it calls the SERVER IMPORT stored procedure listed just below:

  ` CLIENT IMPORT Project Method

  ` CLIENT IMPORT ( Pointer ; Text)

  ` CLIENT IMPORT ( -> [Table] ; Input form )

 

 C_POINTER($1)

 C_TEXT($2)

 C_TIME($vhDocRef)

 C_BLOB($vxData)

 C_LONGINT(spErrCode)

 

  ` Select the document do be imported

 $vhDocRef:=Open document("")

 If(OK=1)

  ` If a document was selected, do not keep it open

    CLOSE DOCUMENT($vhDocRef)

    $vhStartTime:=Current time

  ` Try to load it in memory

    DOCUMENT TO BLOB(Document;$vxData)

    If(OK=1)

  ` If the document could be loaded in the BLOB,

  ` Start the stored procedure that will import the data on the server machine

       $spProcessID:=Execute on server("SERVER IMPORT";32*1024;

       "Server Import Services";Table($1);$2;$vxData)

  ` At this point, we no longer need the BLOB in this process

       CLEAR VARIABLE($vxData)

  ` Wait for the completion of the operation performed by the stored procedure

       Repeat

          DELAY PROCESS(Current process;300)

          GET PROCESS VARIABLE($spProcessID;spErrCode;spErrCode)

          If(Undefined(spErrCode))

  ` Note: if the stored procedure has not initialized its own instance

  ` of the variable spErrCode, we may be returned an undefined variable

             spErrCode:=1

          End if

       Until(spErrCode<=0)

  ` Tell the stored procedure that we acknowledge

       spErrCode:=1

       SET PROCESS VARIABLE($spProcessID;spErrCode;spErrCode)

       $vhEndTime:=Current time

       ALERT("It took "+String(0+($vhEndTime-$vhStartTime))+" seconds.")

    Else

       ALERT("There is not enough memory to load the document.")

    End if

 End if

Here is the SERVER IMPORT project method executed as a stored procedure:

  ` SERVER IMPORT Project Method

  ` SERVER IMPORT ( Long ; Text; BLOB )

  ` SERVER IMPORT ( Table Number ; Input form ; Import Data )

 

 C_LONGINT($1)

 C_TEXT($2)

 C_BLOB($3)

 C_LONGINT(spErrCode)

 

  ` Operation is not finished yet, set spErrCode to 1

 spErrCode:=1

 $vpTable:=Table($1)

 INPUT FORM($vpTable->;$2)

 $vsDocName:="Import File "+String(1+Random)

 If(On Windows)

    $vsDocName:=$vsDocName+".txt" ` On Windows, file extension is mandatory

 End if

 DELETE DOCUMENT($vsDocName)

 BLOB TO DOCUMENT($vsDocName;$3)

 IMPORT TEXT($vpTable->;$vsDocName)

 DELETE DOCUMENT($vsDocName)

  ` Operation is finished, set spErrCode to 0

 spErrCode:=0

  ` Wait until the requester Client got the result back

 Repeat

    DELAY PROCESS(Current process;1)

 Until(spErrCode>0)

Note: The On Windows project method is listed in the System Documents section in the 4D Language Reference manual.

After these two project methods have been implemented in a database, you call perform a “Stored Procedure-based” import data by writing, for example:

CLIENT IMPORT(->[Table1];"Import")

With some benchmarks, you will discover that by using this method you can import records up to 60 times faster than with a regular import.