Performance Impact of listUserByName and GetUser

Version 1

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: JAMES DEPHILLIP II on 02-11-2010 10:35:44 AM
    I am doing a listUserByName and then doing a GetUser on each of the responses.
     
    <?php
     header("Content-type: text/xml");
     header("Connection: close");
     header("Expires: -1");

    define('CUCM_SERVER_IP',  'x.x.x.x');
    define('CUCM_SERVER_PORT','8443');
    define('CUCM_AXL_USER',   'user');
    define('CUCM_AXL_PASS',   'password');
    define('CUCM_AXL_API',    '/Library/WebServer/Documents/AXLAPI.wsdl');

    function SearchUsers($firstname, $lastname){

      $client = new SoapClient(CUCM_AXL_API,
        array('trace'=>true,
        'exceptions'=>true,
        'location'=>"https://".CUCM_SERVER_IP.":".CUCM_SERVER_PORT."/axl",
        'login'=>CUCM_AXL_USER,
        'password'=>CUCM_AXL_PASS));

      $listusers = $client->listUserByName(
        array(
        "lastname"=>"$lastname"."*",
        "firstname"=>"$firstname"."*",
        "searchLimit"=>"32"));

      $users = $listusers->return->user;

      return $users;
    }

    function BuildDirectoryArray($users){

      $client = new SoapClient(CUCM_AXL_API,
        array('trace'=>true,
        'exceptions'=>true,
        'location'=>"https://".CUCM_SERVER_IP.":".CUCM_SERVER_PORT."/axl",
        'login'=>CUCM_AXL_USER,
        'password'=>CUCM_AXL_PASS));

      $y = 0;

      if($users != NULL){
        if(is_array($users)){
          foreach ($users as $tmp){
            $directory[$y] = $tmp->firstname;
            $directory[$y] = $tmp->lastname;
            $directory[$y] = $tmp->userid;
            $userinfo = $client->getUser(array("userid"=>"$tmp->userid"));
            if( $userinfo->return->user->primaryExtension->pattern == NULL ){
              $directory[$y] = $userinfo->return->user-> telephoneNumber;
            }
            else{
              $directory[$y] = $userinfo->return->user->primaryExtension->pattern;
            }
            $y = $y + 1;
          }
        }
        else{
          $directory[0] = $users->firstname;
          $directory[0] = $users->lastname;
          $directory[0] = $users->userid;
          $userinfo = $client->getUser(array("userid"=>"$users->userid"));
          if( $userinfo->return->user->primaryExtension->pattern == NULL ){
            $directory[0] = $userinfo->return->user-> telephoneNumber;
          }
          else{
            $directory[0] = $userinfo->return->user->primaryExtension->pattern;
          }
        }
      }
    return $directory;
    }


    function DisplayResults($array){

      echo("<CiscoIPPhoneDirectory><Title>Directory</Title><Prompt>Records</Prompt>");

      foreach ($array as $tmp){
        echo("<DirectoryEntry><Name>".$tmp.", ".$tmp."</Name><Telephone>".$tmp."</Telephone></DirectoryEntry>");
      }
      echo("</CiscoIPPhoneDirectory>");
    }


    $first = $_REQUEST["FIRST"];
    $last = $_REQUEST["LAST"];

    $test = SearchUsers($first,$last);
    $test2 = BuildDirectoryArray($test);
    DisplayResults ($test2);

    ?>
    This document was generated from CDN thread

    Created by: JAMES DEPHILLIP II on 02-11-2010 12:57:03 AM
    I am creating a custom directory that pulls the Primary Extension of the user and then falls back to the telephone number if the Primary Extension is not selected.  I have this working by using listUserByName and GetUser in AXL.  Would it be better to do this with AXL SQL queries and what tables would I use? I have only done LDAP directories in the past on CCM 4.x. Thank You!

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: David Staudt on 02-11-2010 02:27:26 AM
    Can you elaborate more on the algorithm and AXL requests you use in the app?  In general unless there is a large and obvious performance benefit, you will want to stick with the named AXL requests rather than use SQL (which has no backward compatbiility policy.)

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: Sascha Monteiro on 02-11-2010 10:49:19 AM
    Hi,
    To retrieve just basic user information (first,last etc) I would always use an sql query, especially if you search on the fly,
    it would take too long for a user to wait if it's a bigger directory.
    With an sql query you retrieve all users in one request, instead of having to send hundreds or thousands of requests
     
    What I have done in the past is duplicating the user information in the web application itself (I use Java Servlets, so store all users in memory),
    the search is super fast and you can i.e. sync every few hours or so

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: David Staudt on 02-11-2010 02:48:51 PM
    Agree then, you can probably achieve much better performance by using SQL, instead of dozens (or hundreds) of individual getUser requests.  You will still need to do two SQL querires though:
     
     This one should get you the users and telephoneNumber, along with a key into the numplan table for the primary extension:
     
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.0">
       <soapenv:Header/>
       <soapenv:Body>
          <ns:executeSQLQuery sequence="1">
             <sql>select userid,firstname,lastname,telephoneNumber,fknumplan from enduser LEFT OUTER JOIN endusernumplanmap ON enduser.pkid=endusernumplanmap.fkenduser where lastname like 's%'</sql>
          </ns:executeSQLQuery>
       </soapenv:Body>
    </soapenv:Envelope>
     
    --------------------------------
     
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
       <soapenv:Body>
          <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/8.0">
             <return>
                <row>
                   <userid>testUser1</userid>
                   <firstname/>
                   <lastname>testUser1</lastname>
                   <telephonenumber/>
                   <fknumplan>fa39b8a2-8c12-7a24-99b1-935b02edb81f</fknumplan>
                </row>
                <row>
                   <userid>testUser2</userid>
                   <firstname/>
                   <lastname>testUser2</lastname>
                   <telephonenumber/>
                   <fknumplan>ab39b8a2-8c12-7a24-99b1-935b02edb8c1</fknumplan>
                 </row>
             </return>
          </ns:executeSQLQueryResponse>
       </soapenv:Body>
    </soapenv:Envelope>
     
     
    You will probably need to do a separate lookup to map the <fknumplan> values to actual DNs:
     

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.0">
       <soapenv:Header/>
       <soapenv:Body>
          <ns:executeSQLQuery sequence="1">
             <sql>select pkid,dnorpattern from numplan where pkid in ('fa39b8a2-8c12-7a24-99b1-935b02edb81f','ab39b8a2-8c12-7a24-99b1-935b02edb8c1')</sql>
          </ns:executeSQLQuery>
       </soapenv:Body>
    </soapenv:Envelope>
    ------------------------------------

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
       <soapenv:Body>
          <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/8.0">
             <return>
                <row>
                   <pkid>fa39b8a2-8c12-7a24-99b1-935b02edb81f</pkid>
                   <dnorpattern>1000</dnorpattern>
                </row>
                <row>
                   <pkid>ab39b8a2-8c12-7a24-99b1-935b02edb8c1</pkid>
                   <dnorpattern>2000</dnorpattern>
                </row>
             </return>
          </ns:executeSQLQueryResponse>
       </soapenv:Body>
    </soapenv:Envelope>


     
     
     

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: JAMES DEPHILLIP II on 02-11-2010 10:27:39 PM
    Thanks guys this is what I will be doing tomorrow!

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: Sascha Monteiro on 02-11-2010 10:40:43 PM
    maybe I am not quite awake yet, but you could join numplan in the 1st query to get the dn straight away?
     
    just tested this;
    select e.userid,e.firstname,e.lastname,e.telephoneNumber,n.dnorpattern from enduser e LEFT OUTER JOIN endusernumplanmap em ON e.pkid=em.fkenduser join numplan n on n.pkid = em.fknumplan where upper(lastname) like upper('m%')

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: JAMES DEPHILLIP II on 03-11-2010 11:30:36 AM
    This returns the information but in two sets.  If I do one user I get 2 results for everything.
     
    stdClass Object
    (
         => stdClass Object
            (
                 => Array
                    (
                        [0] => stdClass Object
                            (
                                 => testuser
                                 => Test
                                 => User
                                 => 71234
                                 => 74567
                            )
                        [1] => stdClass Object
                            (
                                 => testuser
                                 => Test
                                 => User
                                 => 71234
                                 => 5714567
                            )
                    )
            )
    )

     
    There is only one user so I am a little confused.

    Subject: RE: Performance Impact of listUserByName and GetUser
    Replied by: JAMES DEPHILLIP II on 03-11-2010 11:48:41 AM
    I see why now the first lookup is returning the IPCC extension.  is there a way to differentiate these two from each other?

    Subject: RE: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - A
    Replied by: JAMES DEPHILLIP II on 03-11-2010 12:19:05 PM
    Thank you very much I appreciate it.  I think I figured it out. Is there anything that you see could be wrong with this?
     
    SELECT e.userid, e.firstname, e.lastname, e.telephoneNumber, n.dnorpattern FROM enduser e LEFT OUTER JOIN endusernumplanmap em ON e.pkid=em.fkenduser JOIN numplan n ON n.pkid = em.fknumplan WHERE UPPER(lastname) LIKE UPPER('%') AND UPPER(firstname) LIKE UPPER('%') AND em.tkdnusage='1'

    Subject: RE: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - A
    Replied by: JAMES DEPHILLIP II on 03-11-2010 12:29:59 PM
    Is there a way that this will return all users even if the primary extension is not set? I have to fallback to the Telephone number if there is not a primary extentsion.

    Subject: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - Admin
    Replied by: Sascha Monteiro on 03-11-2010 11:56:49 AM
    Yes, but I just went to bed, can look up tomorrow

    Sent from my iPhone4

    On 03/11/2010, at 10:48 PM, Cisco Developer Community Forums <cdicuser@developer.cisco.com> wrote:

    > JAMES DEPHILLIP II has created a new message in the forum "Administration XML Questions":
    > --------------------------------------------------------------
    > I see why now the first lookup is returning the IPCC extension.  is there a way to differentiate these two from each other?
    > --
    > To respond to this post, please click the following link:
    > <http://developer.cisco.com/web/axl/forums/-/message_boards/message/2706927>
    > or simply reply to this email.

    Subject: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - Admin
    Replied by: Sascha Monteiro on 03-11-2010 11:57:49 AM
    Alias for the enduser and numplan tables

    Sent from my iPhone4

    On 03/11/2010, at 10:30 PM, Cisco Developer Community Forums <cdicuser@developer.cisco.com> wrote:

    > JAMES DEPHILLIP II has created a new message in the forum "Administration XML Questions":
    > --------------------------------------------------------------
    > I do not know much about SQL qeury's but what are the e's and n's representing?
    > --
    > To respond to this post, please click the following link:
    > <http://developer.cisco.com/web/axl/forums/-/message_boards/message/2706882>
    > or simply reply to this email.

    Subject: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - Admin
    Replied by: Sascha Monteiro on 03-11-2010 07:53:49 PM
    try

    > SELECT e.userid, e.firstname, e.lastname, e.telephoneNumber, n.dnorpattern FROM enduser e LEFT OUTER JOIN endusernumplanmap em ON e.pkid=em.fkenduser OUTER JOIN numplan n ON n.pkid = em.fknumplan WHERE em.tkdnusage='1'

    On 3/11/2010, at 11:19 PM, Cisco Developer Community Forums wrote:

    > JAMES DEPHILLIP II has created a new message in the forum "Administration XML Questions":
    > --------------------------------------------------------------
    > Thank you very much I appreciate it.  I think I figured it out. Is there anything that you see could be wrong with this?

    > SELECT e.userid, e.firstname, e.lastname, e.telephoneNumber, n.dnorpattern FROM enduser e LEFT OUTER JOIN endusernumplanmap em ON e.pkid=em.fkenduser JOIN numplan n ON n.pkid = em.fknumplan WHERE UPPER(lastname) LIKE UPPER('%') AND UPPER(firstname) LIKE UPPER('%') AND em.tkdnusage='1'
    > --
    > To respond to this post, please click the following link:
    > <http://developer.cisco.com/web/axl/forums/-/message_boards/message/2707005>
    > or simply reply to this email.

    Subject: RE: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - A
    Replied by: David Staudt on 03-11-2010 08:30:21 PM
    Please take care when using joins and especially outer joins with UCM and its Informix IDS.  IDS is targeted as a fast, stable, lightweight 'embedded' database - not an enterprise data warehouse type database - and its support for robust complex queries is limited.  In many cases it may be orders of magnitude harder for it to execute a double outer join like this than for the app to execute separate queries and correlate on its own.  Testing will be your guide.
     
     

    Subject: RE: Re: New Message from JAMES DEPHILLIP II in Administration XML (AXL) - A
    Replied by: JAMES DEPHILLIP II on 04-11-2010 05:24:58 PM
    I appreciate the help.  I decided to keep it as two seperate queries. Now I have to start learning how to work in JTAPI.