Command SP-Based Import (Example)

SP-Based Import (Example)

version 6.0.2

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 Client 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 data is therefore performed locally (at single-user speed) 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 ; String )
      ` CLIENT IMPORT ( -> [Table] ; Input form )

   C_POINTER($1)
   C_STRING(31;$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 ; String ; BLOB )
      ` SERVER IMPORT ( Table Number ; Input form ; Import Data )

   C_LONGINT($1)
   C_STRING(31;$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.