4D v14

SP-Based Import (Example)

Home

 
4D v14
SP-Based Import (Example)

SP-Based Import (Example)  


 

 

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.

 
PROPERTIES 

Product: 4D
Theme: 4D Server and the 4D Language

 
SEE ALSO 

SP-Based Services (Example)
Stored Procedures

 
ARTICLE USAGE

4D Server Reference ( 4D v13)
4D Server Reference ( 4D Server v12)
4D Server Reference ( 4D Server v11 SQL Release 6)
4D Server Reference ( 4D v14 R2)
4D Server Reference ( 4D v14)
4D Server Reference ( 4D v14 R3)
4D Server Reference ( 4D Server v14 R4)