LTRIM, RTRIM, and TRIM


The trimming functions in Oracle frequently are used by many developers; but not all know the full set of options available and how they compare to each other. Sometimes I need to remind myself so this post is as much for me as for others. I hope you find it helpful.

The LTRIM and RTRIM functions both default to removing a blanks from the left (LTRIM) or right (RTRIM) ends of the input string if that is the only parameter used. If the a second parameter is passed, then all characters within that set, regardless of order will be trimmed from their corresponding sides. Frequently, I see code with a single character for the trim option, but rarely the second and it is both a convenient and efficient means of doing so. The following examples demonstrate the various syntax and functional variations.

SELECT '>' || result || '<' result
  FROM (SELECT LTRIM('    abc.......cba    ') result FROM DUAL   -- remove blanks (default) from the beginning (left) side of the string
        UNION ALL
        SELECT LTRIM('abc.......cba', 'a') result FROM DUAL  -- remove "a" characters from the left side of the string
        UNION ALL
        SELECT LTRIM('abc.......cba', 'bac') result FROM DUAL  -- remove all "a", "b", and "c" characters from the left side of the string
        UNION ALL
        SELECT RTRIM('    abc.......cba    ') result FROM DUAL  -- remove blanks (defaul) from the end (right) side of the string
        UNION ALL
        SELECT RTRIM('abc.......cba', 'a') result FROM DUAL  -- remove "a" characters from the right side of the string
        UNION ALL
        SELECT RTRIM('abc.......cba', 'bac') result FROM DUAL  -- remove all "a", "b", and "c" characters from the right end of the string
       );

RESULT
-------------------
>abc.......cba    <
>bc.......cba<
>.......cba<
>    abc.......cba<
>abc.......cb<
>abc.......<

A relatively common use I find for the multi-character functionality is cleaning up DOS end-of-line sequences of carriage-return and line-feed characters. For example:

RTRIM(some_variable,chr(13)||chr(10))

TRIM, in contrast to the LTRIM and RTRIM functions, only works with a single character; but still defaults to blanks if no character is specified.
Also, rather than using a simple positional parameter, the non-default functionality is specified with a descriptive clause preceding the input string.
A LEADING clause will produce results similar to LTRIM, a TRAILING clause similar to RTRIM – but specifying only a single character to be trimmed.

Using BOTH allows for trimming a non-default character from both sides. The keyword BOTH is optional though as seen in the last two subqueries in the examples below.

SELECT '>'|| result || '<' result
  FROM (SELECT TRIM('    abc.......cba    ') result FROM DUAL   -- remove blanks from both sides of the string
        UNION ALL
        SELECT TRIM(LEADING ' ' FROM '    abc.......cba    ') result FROM DUAL  -- remove blanks from the beginning of the string
        UNION ALL
        SELECT TRIM(TRAILING ' ' FROM '    abc.......cba    ') result FROM DUAL  -- remove blanks from the end of the string
        UNION ALL
        SELECT TRIM(BOTH ' ' FROM '    abc.......cba    ') result FROM DUAL  -- remove blanks from both sides of the string
        UNION ALL
        SELECT TRIM(' ' FROM '    abc.......cba    ') result FROM DUAL  -- remove blanks from both sides of the string
        UNION ALL
        SELECT TRIM('a' FROM 'abc.......cba') result FROM DUAL   -- remove "a" characters from both sides of the string
       );

RESULT
-------------------
>abc.......cba<
>abc.......cba    <
>    abc.......cba<
>abc.......cba<
>abc.......cba<
>bc.......cb<

Finally, if you forget the single-character limit of TRIM, Oracle will remind you explicitly of the error.

SQL> SELECT TRIM('abc' FROM 'abc.......cba') result FROM DUAL;
SELECT TRIM('abc' FROM 'abc.......cba') result FROM DUAL
       *
ERROR at line 1:
ORA-30001: trim set should have only one character
Advertisements

Toad 13 LDAP connection list not populating after upgrade to 18c client


In Toad version 13, after upgrading to an 18c client, the dropdown list on the LDAP tab of the connection window will be empty.

The reason is Toad is looking for the 12c ldap client dll in the 18c $ORACLE_HOME/bin directory.
Connections will still work; but if you want the list and search functionality you can restore it simply by copying the oraldapclnt18.dll to oraldapclnt12.dll in the same directory.

How to use DBMS_LDAP (part 2)


Establishing a Connection

The DBMS_LDAP.INIT function is used to connect to an LDAP server. On success, the function returns a session handle of DBMS_LDAP.SESSION type.

    FUNCTION init (hostname IN VARCHAR2,
                   portnum  IN PLS_INTEGER )
      RETURN SESSION;

DBMS_LDAP.PORT is a constant defined to the default port of 389. The INIT function itself does not have default values for the input parameters, so you must pass in a port, even if it’s just the default port.

The simplest complete block (connect,disconnect) would look something like this:

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no standalone close function.
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Note, this assumes the default exception processing is in effect. That is, on error, the DBMS_LDAP functions will raise an exception. So the v_result assignment would be moot because the assignment would never happen on error. To make the error handling explicit, the same block could be implemented like this:

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no standalone close function.
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

The USE_EXCEPTION setting will persist for the duration of a session, so once set TRUE or FALSE, it will remain in effect across DBMS_LDAP calls. To ensure your error handling works as expected, you will need to set it yourself within your own code to make sure you don’t inherit an unexpected setting from some other code run previously within your user’s session.

The other option would be to set USE_EXCEPTION to FALSE. Doing so would then change the exception handling to be based on the results of each function call.
If INIT fails, instead of raising an exception, it will simply return a NULL value for the session handle. If unbind fails, it will return a numeric code for the error. These will not be SQLCODE values; but rather, when possible, a standard LDAP result code as defined in the Appendix of RFC4511.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := FALSE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    IF v_session IS NULL
    THEN
        DBMS_OUTPUT.put_line('Init failed');
    ELSE
        -- Even if you don't BIND a username/password, you should still UNBIND
        -- to close the session, there is no close
        v_result := DBMS_LDAP.unbind_s(v_session);

        IF v_result != DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line(
                   'Unbind failed with result code: '
                || TO_CHAR(v_result, 'fm9999')
                || ' '
                || DBMS_LDAP.err2string(v_result));
        END IF;
    END IF;
END;

The examples above illustrate a basic ANONYMOUS connection (i.e. no username or password required.) If we wanted to do work on the server, then we would do so between the INIT and the UNBIND_S calls.

If the server requires authentication, then an anonymous connection won’t work. We must send username and password to the server. In LDAP terminology this referred to as “binding.” DBMS_LDAP includes two binding functions: SIMPLE_BIND_S with a fixed authentication method and BIND_S which allows selection of methods. Strangely, DBMS_LDAP defines multiple authentication constants but the documentation, as of 18c, claims: “The only valid value is DBMS_LDAP_UTL.AUTH_SIMPLE.” Also of note, (as of this writing,) the 18c PL/SQL Packages and Types Reference links to the 12.2 Fusion Middleware Application Developer’s Guide for Oracle Identity Management documentation. That may be updated at a future date but it’s something to keep an eye one as future releases/updates come along.

For the sake of simplicity in the examples below I will leave exceptions turned on and allow them to propagate.

When binding to an LDAP server, you won’t use a simple user id as you might with an Oracle database or a UNIX server, but rather your login id will be a full distinguished name and then authenticated with a password.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Login with the DN for a user account and password 
    v_result := DBMS_LDAP.simple_bind_s(
                      ld => v_session, 
                      dn => 'uid=myuserid,ou=users,dc=example,dc=net', 
                      passwd => 'secret_password');

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no close
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Using the DBMS_LDAP.BIND_S function, the coding is nearly identical. The only difference being the “passwd” parameter is named “cred” and the addition of a fourth parameter to specify the authentication method. Again, as of 18c, the documentation specified only one legal value: AUTH_SIMPLE, which makes this method functionally identical to using SIMPLE_BIND_S.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.port);

    -- Login with the DN for a user account and password
    v_result :=
        DBMS_LDAP.bind_s(ld     => v_session,
                         dn     => 'uid=myuserid,ou=users,dc=example,dc=net',
                         cred   => 'secret_password',
                         meth   => DBMS_LDAP.auth_simple);

    -- Even if you don't BIND a username/password, you should still UNBIND
    -- to close the session, there is no close
    v_result := DBMS_LDAP.unbind_s(v_session);
END;

The last option to explore is connecting with encrypted communication through SSL. To do so you will need a wallet containing the certificates of the servers. Then call OPEN_SSL before authenticating. You must specify the location with a full “file:” path, not the name of a database directory object.

DECLARE
    v_session   DBMS_LDAP.session;
    v_result    PLS_INTEGER;
BEGIN
    DBMS_LDAP.use_exception := TRUE;
    -- Establish a connection to LDAP server
    v_session := DBMS_LDAP.init(hostname => 'ldap.example.net', 
                                portnum => DBMS_LDAP.ssl_port);

    -- Provide the certificate information needed for SSL communication.
    v_result :=
        DBMS_LDAP.open_ssl(ld                => v_session,
                           sslwrl            => 'file:/your/wallet/directory',
                           sslwalletpasswd   => 'wallet_password',
                           sslauth           => 2);

     -- Login with the DN for a user account and password 
    v_result := DBMS_LDAP.simple_bind_s(
                      ld => v_session, 
                      dn => 'uid=myuserid,ou=users,dc=example,dc=net', 
                      passwd => 'secret_password');

    v_result := DBMS_LDAP.unbind_s(v_session);
END;

Hopefully these examples will cover the use cases you might expect to see. In addition to the connection and binding syntax it’s also important to note the error handling flag to either return error codes or raise exceptions.

In my next post I’ll cover searching through the directory tree to find specific records or groups of records.

How to Use DBMS_LDAP (part 1)


Introduction

In 9iR1 (9.0.1) Oracle released the first version of DBMS_LDAP. Between then and 18c the package has received a few enhancements including support for BLOBs, Basic Encoding Rules (BER,) and NLS character set conversions.
The documentation for the package has alwasy been somewhat terse, especially if you’re unfamiliar with LDAP structures and usage.

I’ve had the need to muddle through a few times over the years, accumulating a hodgepodge of notes and code snippets to act as mnemonics. Herein and in following posts I will attempt to organize and expand on these notes to, hopefully, aid myself as well as others.

  1. Introduction (this page)
  2. Establishing a Connection
  3. Searching Hierarchical Data
  4. Browsing Attributes

LDAP itself is merely the means of communicating with a directory server; however the structure of the communication is designed to reflect directory structure of the data storage (even if the server doesn’t actually use such a mechanism.)

That is, when reading or writing data you will need to observe the hierarchical structure of the directory. For example, from the LDAP RFC 4511:

A directory entry might be found at: “OU=People,DC=Example,DC=NET”

Similar to a network address, the path can be read backwards from least to most specific.

DC=NET (Domain Component = NET)
    DC=Example (Domain Component = Example)
        OU=People (Organizational Unit = People)

Thus, People are a group within the Example domain, which is itself a sub-domain within the NET domain.

The RFC doesn’t provide examples of specific people under this tree but does show an additional layer of organization under People with Organizational Units for Managers and Consultants. Adding a few people of our own, we might end up with a staffing directory structure that looks something like this where each person is assigned a unique user id (uid.)

DC=NET
    DC=Example
        OU=People
            OU=Managers
                uid=1234
                uid=3456
            OU=Consultants
                uid=1987
                uid=6543

Digging a little deeper individual people will likely have attributes. Thus fleshing out the directory tree to something like this:

DC=NET
    DC=Example
        OU=People
            OU=Managers
                uid=1234
                    CN=Jane Doe
                    hiredate=2005-04-03
                    email=jane.doe@example.net
                    department=Acquisitions
                    objectClass=person
                uid=3456
                    CN=John Doe
                    hiredate=2006-05-04
                    email=john.doe@example.net
                    department=Sales
                    objectClass=person
            OU=Consultants
                uid=1987
                    CN=Mickey Mouse
                    hiredate=2005-12-11
                    email=mickey.mouse@example.net
                    department=Acquisitions
                    objectClass=person
                uid=6543
                    CN=Donald Duck
                    hiredate=2008-07-06
                    email=donald.duck@example.net
                    department=Sales
                    objectClass=person

To find a particular user you would specify the full path to that user which is called a “distinguished name” or “dn”. In relational database land, we might refer to the distinguished name as the “primary key.”

So, Mickey Mouse would be identified in this hierarchy as:

uid=1987,ou=consultants,ou=people,dc=example,dc=net

Note, capitalization is not important within the hierarchy, so searches and dn references are case-insensitive.

Much like learning a new database schema, you might not know all of the tables and relationships right away. So, it may be necessary to talk to your LDAP server admin about the structure and hierarchy of elements you will need. You may also want to use a browsing tool. There are free and/or open source tools such as JXplorer, LDAP Admin, or Apache Directory Studio. If I used LDAP more extensively or had to administer a production system I might use other tools; but for my limited use JXplorer has been adequate. I like that I was able to use the same tool in Windows and Linux.

Hopefully this brief introduction helps explain how data within an LDAP tree is organized and how references to it can be made. In my next post I’ll show how connections are made anonymously, with authentication, and through SSL.

My KScope2018 slides


Finally getting around to posting these, sorry about the delay.

My powerpoint slides can be downloaded here.

Some of the slides included simplified queries so the text would fit on a single screen and remain legible.

To find who is using temp, a more functionally robust query can be found here:
https://seanstuber.wordpress.com/2018/03/08/who-is-using-temp-and-what-are-they-doing/

Similarly, the who is blocking whom query has a fuller version here:
https://seanstuber.wordpress.com/2018/03/04/who-is-blocking-whom-and-what-are-they-doing/

Thanks to all who attended and special thanks for the follow up questions.

Partitioned Outer Joins – something old and new at the same time.


This past week I was looking through the Oracle documentation on a task of pedantry; looking up the exact description of Oracle’s outer join syntax. While digging through the syntax diagrams and explanations of the SQL Reference I came across the phrase “partitioned outer join”. It’s an old feature now, having been around since 2003 with the introduction of 10gR1. However, I don’t recall ever having used it before. I probably read about it back then, but having not practiced it, it had fallen out of memory.

I asked around and found many others had never used it either. That sounded like a good topic for a blog article.

Much like an analytic function windowing clause, the partitioning applies the same value to each row in the partition and then applies the outer join condition to those rows.
Compare this to a non-partitioned outer join (a normal outer join) where each unmatched row of the driving table will have one row in the result set with nulls for the missing outer joined table.
With a partitioned outer join, the null-rows for each unmatched row of the driving table will be repeated for each partition expression.

To illustrate, I’ll use the standard Order Entry (OE) sample schema.
If I outer join all orders by month for the year 2007 I might use a query like this:

WITH
    months
    AS
        (    SELECT ADD_MONTHS(DATE '2007-01-01', LEVEL - 1) month
               FROM DUAL
         CONNECT BY LEVEL <= 12),
    orders_by_month
    AS
        (SELECT customer_id, TRUNC(order_date, 'mm') order_month, order_id
           FROM oe.orders
          WHERE order_date >= DATE '2007-01-01' AND order_date < DATE '2008-01-01')
  SELECT customer_id, m.month, order_id
    FROM months m LEFT JOIN orders_by_month o ON o.order_month = m.month
ORDER BY customer_id, month

Which will produce 70 rows, 69 rows of results for customers that have orders for some months and one null-row for January where no customer placed an order.

CUSTOMER_ID MONTH        ORDER_ID
----------- ---------- ----------
        101 2007-08-01       2458
        101 2007-10-01       2430
        102 2007-03-01       2414
        102 2007-09-01       2432
        102 2007-11-01       2397
        103 2007-09-01       2433
        103 2007-10-01       2454
        104 2007-03-01       2416
        104 2007-09-01       2438
        105 2007-03-01       2417
        105 2007-08-01       2439
        107 2007-03-01       2419
        107 2007-08-01       2440
        107 2007-11-01       2360
        108 2007-03-01       2420
        108 2007-11-01       2361
        109 2007-03-01       2421
        109 2007-07-01       2444
        109 2007-11-01       2362
        116 2007-06-01       2369
        116 2007-09-01       2436
        116 2007-10-01       2453
        116 2007-11-01       2428
        117 2007-07-01       2446
        117 2007-11-01       2429
        118 2007-05-01       2371
        118 2007-10-01       2457
        119 2007-02-01       2372
        122 2007-02-01       2375
        123 2007-06-01       2376
        141 2007-06-01       2377
        142 2007-05-01       2378
        143 2007-05-01       2380
        144 2007-09-01       2435
        144 2007-10-01       2363
        144 2007-12-01       2422
        145 2007-06-01       2448
        145 2007-08-01       2364
        145 2007-09-01       2455
        145 2007-11-01       2423
        146 2007-05-01       2379
        146 2007-06-01       2449
        146 2007-08-01       2365
        146 2007-11-01       2424
        147 2007-04-01       2450
        147 2007-08-01       2366
        147 2007-12-01       2385
        148 2007-06-01       2406
        148 2007-12-01       2451
        148 2007-12-01       2386
        149 2007-03-01       2387
        149 2007-09-01       2434
        149 2007-10-01       2452
        149 2007-11-01       2427
        150 2007-06-01       2388
        152 2007-11-01       2390
        154 2007-07-01       2392
        157 2007-11-01       2398
        158 2007-11-01       2399
        159 2007-07-01       2400
        160 2007-07-01       2401
        161 2007-07-01       2402
        162 2007-07-01       2403
        163 2007-07-01       2404
        164 2007-07-01       2405
        165 2007-06-01       2407
        166 2007-06-01       2408
        167 2007-06-01       2409
        169 2007-05-01       2411
            2007-01-01           

Now let’s add the PARTITION BY clause, so we can see the calendar for each customer. That is, we don’t just want to see January for the entire result set as nulls, we want to see which months for each customer were lacking an order.

WITH
    months
    AS
        (    SELECT ADD_MONTHS(DATE '2007-01-01', LEVEL - 1) month
               FROM DUAL
         CONNECT BY LEVEL <= 12),
    orders_by_month
    AS
        (SELECT customer_id, TRUNC(order_date, 'mm') order_month, order_id
           FROM oe.orders
          WHERE order_date >= DATE '2007-01-01' AND order_date < DATE '2008-01-01')
  SELECT customer_id, m.month, order_id
    FROM months m 
  LEFT JOIN orders_by_month o PARTITION BY (customer_id)
    ON o.order_month = m.month
ORDER BY customer_id, month

And now we get the 12 months repeated for each customer and if there are no orders for a month, we get a null row for that customer for that month. The query produces 457 rows, I have abbreviated the output below to just the first 4 customers.

CUSTOMER_ID MONTH        ORDER_ID
----------- ---------- ----------
        101 2007-01-01           
        101 2007-02-01           
        101 2007-03-01           
        101 2007-04-01           
        101 2007-05-01           
        101 2007-06-01           
        101 2007-07-01           
        101 2007-08-01       2458
        101 2007-09-01           
        101 2007-10-01       2430
        101 2007-11-01           
        101 2007-12-01           
        102 2007-01-01           
        102 2007-02-01           
        102 2007-03-01       2414
        102 2007-04-01           
        102 2007-05-01           
        102 2007-06-01           
        102 2007-07-01           
        102 2007-08-01           
        102 2007-09-01       2432
        102 2007-10-01           
        102 2007-11-01       2397
        102 2007-12-01           
        103 2007-01-01           
        103 2007-02-01           
        103 2007-03-01           
        103 2007-04-01           
        103 2007-05-01           
        103 2007-06-01           
        103 2007-07-01           
        103 2007-08-01           
        103 2007-09-01       2433
        103 2007-10-01       2454
        103 2007-11-01           
        103 2007-12-01           
        104 2007-01-01           
        104 2007-02-01           
        104 2007-03-01       2416
        104 2007-04-01           
        104 2007-05-01           
        104 2007-06-01           
        104 2007-07-01           
        104 2007-08-01           
        104 2007-09-01       2438
        104 2007-10-01           
        104 2007-11-01           
        104 2007-12-01           
    ...

While partitioned outer joins aren’t something I expect to use often; I will try to keep my eye out for opportunities to use them. I’m sure sometime in the past 15 years I’ve probably written a query using multiple self-joins and/or joins to additional tables to produce the same functionality… another tool for my toolbox.

Questions and comments, as always, are welcome.

Fast * Many = Slow, or, the Problem of Fast Code


Several years ago I made a little math joke about a process that performed millions of look-ups and took forever to complete. That joke, just a short one-liner: “Fast Times Many Equals Slow” has since become a bit of a mantra.

The problem can be particularly pernicious in that a problematic set of code might be well designed, extensively tested, and extremely fast. All three points are desirable and, in some cases, indisputable for a given routine. The performance problem arises when this fast code is executed repeatedly. The problem can sneak up on you. Maybe you write a small routine that executes in 14 microseconds and then you invoke it a thousand times. That means your code will contribute 0.014 total seconds to your process. Without any other context, that sounds pretty good to me and would often be considered acceptable, maybe even excellent.

However, if the process is ramped up to a billion iterations, now that fast routine adds almost 4 hours to your processing time.

0.000014 * 1000000000 = 14000 seconds = 3:53:20

If 4 hours (plus whatever time it takes to perform the rest of the process) is considered slow… is the problem that your routine is slow or is the problem that you ran it many times?

While I’ve seen this problem repeatedly over the years, it’s been particularly prevalent lately across a variety of applications from multiple sources (i.e. it wasn’t just one developer implementing the same design over and over.) I’m sure it’s merely coincidence that so many instances were revealed in a short period of time; but that doesn’t negate the underlying cause.

Iteration is a common programming technique and is often necessary. Sometimes you can mask it by hiding behind an API that performs the iteration for you, but doing so doesn’t remove the problem, it simply shoves it somewhere else. In some cases though, pushing the work somewhere else might be the right solution. There is nothing inherently wrong with iteration itself; but rather the choice of what is included within each cycle. A few weeks ago I wrote about the importance of timing instrumentation and how I used it solve a performance problem.

The problem discussed in that article was caused by misplaced iteration. Program A would read information from an external source and pass it to Program B to parse and process the individual records retrieved from the remote service. The iteration issue occurred because A would only feed the data in small increments to B. So B still needed to iterate over each record. That part was unavoidable (at some point “somebody” needed to act on each record) the problem was B already had iteration built in and A simply added extra work by pre-iterating. Not only that, each cycle carried additional overhead in a new database call, additional network traffic and additional response processing. Internal caching benefits within B were reduced because it was forced to start over with each new invocation.

It might have been possible to make the iteration in A and/or B a little faster, or rewrite B to support multi-call caching when processing within a larger set, but the simplest and most beneficial change was to simply pass all of the data in one call from A to B. This completely eliminated the overhead of making distinct calls. The internal work of B didn’t change at all. It iterated the same as it always did. Admittedly, it’s internal caching did become more effective but the reduction of the back and forth communication with A was the biggest savings. The entire process improved by about a factor of 10.

It’s important to note this was not a scaling factor in the billions as in my earlier example. While such volumes are not uncommon, many processes have no need to cycle that many times. In this example the code was only iterating over tens of thousands of rows, a magnitude many people and processes would consider “normal.” It’s also important to note that no one cycle was slow. The A program was able to parse quickly and split quickly. B was able to parse and process the records quickly.

As noted above, this is why iteration problems can be a subtle threat. Everything passed unit testing and seemed fast, because it was. It was only in the larger scale testing with full external volumes that the problem revealed itself.

So, what is one to do about it?

  1. Check to see if you are iterating needlessly. As seen in my parsing example, there was no need to add an extra layer of iteration. In the context of databases are you iterating row-by-row when you could be performing set-based operations? The database engine may still need to iterate across a data set; but it’s highly optimized for doing so. Adding row-by-row processing in your client is just another instance of adding iteration on top of iteration already in place. This is sometimes the hardest path to implement because it’s essentially a redesign; but it can be the most effective though by completely negating some steps.
  2. Upon what are you iterating? If you are processing a year’s worth of data and loop through each month, do you perform duplicate work in each cycle? If so, can you move some of that work into a step of its own and only do that work once? This is a good place to check for unnecessary “fast” steps such as key-value look-ups. Maybe for a given customer id you do a lookup for the customer name. It may be very fast with a nice, indexed, unique key retrieval; but if you do it for every order, every day, of every month. It adds up, especially when you do all of that again for the next customer, and the next, and so on. If you must iterate, can you sort the data before starting to remove the need to process identical data repeatedly. If you sort orders by customer, you can lookup the customer information once on the first order for that customer and reuse it until all of that customer’s data is exhausted.
  3. Is each cycle as fast as it can be? This may seem obvious; but it’s still worth a look. The same multiplicative factor making iteration painful can be used to your advantage as well. I worked with another DBA on a process iterating on hundreds of millions of rows. While it would have been nice to ask the vendor to redesign their product; that wasn’t really feasible as an immediate solution. Instead we shaved a few milliseconds off each iteration. One millisecond saved for one million executions is over 16 minutes removed from the total processing time. Multiply that by hundreds and we get DAYS of processing time improvement. Again, this was still not ideal, but it was appreciated by the end users waiting on the data.
  4. Are you including extra overhead in your iterations? Can you consolidate steps? This check is a special case combination of the previous two. As an example, a process that reads a web service, then pass the results to another process, and then take the results of that an pass them to a third process to insert or update some repository – a fairly straight forward ETL process. If you watch the data in an ETL flow, do you “E”xtract one row, “T”ransform that row, and then “L”oad that row? Might it be faster to Extract all of the rows, or a large volume of them, then pass those to a transform process, which then passes them in bulk to a loader? You can eliminate the overhead of invoking each step. This overhead could take make forms, including sql vs pl/sql context switches, network latency, file open/close operations, and more. Maybe instead of processing 1000 rows individually, you can process all 1000 together in each step, or if that’s too expensive then maybe 10 batches of 100, 4 batches of 250, 20 batches of 50… whatever the hardware can handle.
  5. Last option – can you parallelize? If individual iteration is required can you do 2 or more at the same time? Parallelizing is the most resource intensive plan of attack but that doesn’t necessarily mean it’s inappropriate. If you can isolate date into independent chunks and have the hardware available to process them, then why not? The most appealing part of parallel processing is it can be combined with any or all of the other solutions. The most important thing to remember when implementing parallelization is it doesn’t actually fix anything. If your process is flawed, it might be possible to throw enough hardware (money) at the problem to reduce the wall-clock time to something acceptable but doing so means your scaling is determined entirely by your wallet and it’s usually not going to be linear. That is, doubling the hardware doesn’t mean you’ll double the processing speed. There are problem sets where parallelizing adds additional benefits beyond reducing processing time. If you have independent data, such as regional sales, it makes sense to process the East region separately from the West region. Not only can you do both sets of work at the same time, but you also get a bonus of resiliency. A failure in the East doesn’t necessarily impact the West processing.

It might be more accurate to say “Fast * Many = A lot of time” but “Fast * Many = Slow” is a better mnemonic. Plus, the phrase is used when there is a performance issue in some looping process, which is mostly commonly described as the process being “slow.” My point was not to suggest all iteration was bad; but rather to consider the larger scale impacts when it occurs and what, if anything, you can or should do about it.

This has been a bit of a rant; but hopefully I was able to include some useful information along the way.

%d bloggers like this: