Leave feedback
  • Discussion

    Performance boost: Example function to cache DB calls for data enrichment

Enter a new topic
  • Anders Ekstrom Anders Ekstrom
    1 likes 2538 views

    This code utilizes in memory arrays to cache per document data enrichment calls to a database. Easily to be modified to cache other types of calls (files, COM, webservices....) and will be able to speed up execution a lot (just make sure there is enough memory available!) when used.

    // *********************************************************************
    //  Name: GetText
    //  Description: A call to GetText will return a text from the database.
    //  The function will automatically cache texts (in memory) so that the
    //  same job never will have to query the same text from DB twice. This
    //  procedure may speed up execution with several hundred percent,
    //  depending on the database connection and how many documents in job
    //  that is actually using the same texts. If all documents have different
    //  texts this function does not speed up anything, then it will just
    //  consume a lot of memory.
    //  Note: This function assumes that a DB-connection is already opened
    //   at job-begin and closed at job end - to speed up process even
    //   more.
    //  Parameters:
    //   #1 = Unique id for text in database
    // #2 = databasename
    //  Author: Anders Ekström 

    func GetText()
     // Check that input parameter is not empty
     if (#1 = "")
      preproclog(3,">>>> GetText(): Unique ID (#1) Missing");
      // Here you can add other error handling code to e.g. abort job or use default text etc.

     //Check if text has already been queried from database (and available in memory)
     $m_gettext_retno = FindInArray($m_gettext_textid_gen_arr, #1);

     if($m_gettext_retno = "-1")                  // If -1, then textid. is a new one.
          // Add the new textid and put the corresponding text in the $m_gettext_text_gen_arr array.
          // Determine current size of array, so that we know where to append.
          $m_gettext_retsize = ArraySize($m_gettext_textid_gen_arr);

          $m_gettext_select = "Text_Load '"+#1+"'";
          // Enter your own sql-query in row above! In this case a
          // stored procedure is used which is very clever cause then
          // database changes can be done and tables can be optimzed
          // without having to change strs-project (just the stored proc)
          // this is inline with all industry wide architectural guidlines
          // regarding integration.

          $m_gettext_result = Odbcgetone (#2, $m_gettext_select , $value); //Query DB

          if ($m_gettext_result != "1")
       //No data found or error occured...
       log(9, ">>>>>>>>>>>>>>>Text not found: " + #1);
       // Here you can add other error handling code to e.g. abort job or use default text etc.

          $m_gettext_textid_gen_arr[$m_gettext_retsize] = #1;          // The new textid
          $m_gettext_text_gen_arr[$m_gettext_retsize] = $value;          // The new text
          return  $m_gettext_text_gen_arr[$m_gettext_retsize];
       // The current textid was found in the array (using cached version)

       return  $m_gettext_text_gen_arr[$m_gettext_retno];

    Monday 18 January, 2010


Latest from the blogs