How to engineer your DB code

A journey of 1000 miles…

We are developers. We make the modern world tick and create tools for information workers. We are computer scientists, IT experts, and coders, and we call ourselves engineers. We even renamed a part of our industry “software engineering” to get one step closer to this aspirational goal of being professionals when doing our jobs.

For several decades now, generations of IT folk have come up with ways to measure, analyse, and regulate most aspects of software development. How to plan, how to estimate, how to implement, how to be social during development, how to be a human(e) developer, etc.

But looking at the day-to-day experience of many developers in a, say “classic corporate environment” (i.e. banks, manufacturers, airlines, retailers, in short, whoever uses software like SAP’s ERP offering), the focus is very much not on engineering, but on compliance and “making it work”.

Now, getting things done™ is very important, no doubt, but just wrestling with the IDE as long as necessary to make it compile without errors to pass some form of test (maybe just a tick against the requirement description) leaves little opportunity to actually learn anything about the solution we have just reported as “done”.

In my experience, this happens a lot when DB-related code is written. More often than not, the first SQL statement (or HANA model) that looks like it does the job becomes the solution.
This approach can work if the developer has experience with the database in question. Yes, with the specific database, just knowing SQL syntax and how some of the tables “go together” is not sufficient. One needs to know the data and the workload on the DB to have an understanding of what new code will do.

But how to get this knowledge about the specific database and the workload on it? That sound a lot like testing on the production system. Not sure about your environment, but I have rarely seen a customer where a full performance/scalability testing landscape had been set up for the developers. Instead, when the code “works” on the development and Q/A system (usually with neither the data volume nor the workload of the production system), it gets deployed to production and any issues will have to be handled there.

Sure enough, the whole simulating data volume and workload business in a separate landscape is terribly expensive, especially in classic SAP scenarios. Even with cloud-based virtualisation, this is not a cheap thing to do.

So, what other options are there?

In this post, I will look at three different implementations of a database extraction program. Each of the variants has its own pros and cons, so the main point, I guess, is to show

• there are always alternative ways to implement something
• runtime (how long the code takes to execute) is not the only relevant aspect to measure when it comes to SQL code
• there is no singular value for “the runtime” of your code when it should run on a shared database
• it is not actually that hard to get a better understanding of your code.

If the above is interesting to you, read on. Otherwise, this is going to be a long bore for you…

The scenario

So, what is this “database extraction” program about? The goal is to generate a list of all SAP HANA SYSTEM PRIVILEGES that are currently assigned to the DB users. This should include all the privileges that are indirectly assigned, i.e. via roles. Since roles can “contain” other roles and privileges, finding all privileges for a given user turns out to be a complicated task. To make life easier, SAP HANA provides the EFFECTIVE_PRIVILEGES system view, which does the job for us.

The tricky bit now is, that the EFFECTIVE_PRIVILEGES view requires a WHERE condition equality filter on USER_NAME. This is what it looks like when we forget to include the “equal predicate”.

SAP DBTech JDBC: [500]: predicates are required in a where clause: M_EFFECTIVE_PRIVILEGES_ needs predicates on columns (connected by AND if more than one): an equal predicate on USER_NAME

If we want to use the view for our program, we need to put every user name into the equal filter.

Now, I hear the SQL-savvy developers already typing in their sub-select (WHERE user_name IN (SELECT user_name FROM users)). Yeah, Nah!

This is not supported by the view. It really only deals with straight equal conditions.
Any other ideas?

Three solutions for the problem

Let’s look at three possible solutions. They all deliver the same result, so we consider them equally “correct”.

IN-List

The first solution can reasonably be called “blunt”. We generate a list of all user names and fill an IN-list with those names.

“Why can you use IN-lists, when a sub-select doesn’t work?” – glad, you asked!

The IN-list is a short form of OR-concatenation of conditions. It is not, as many, many, many developers hope, an ARRAY of values, but a chain of equal conditions glued together by ORs.

To generate this statement, I used a short Python script.

SELECT /* user_sys_privs blunt user list */ 
    user_name, grantee_type, privilege 
 FROM 
    effective_privileges 
 WHERE 
 user_name IN 
(
'DEVDUDE','SAKILA_1','SAKILA_2','SAKILA_3','SAKILA_4','SYS','SYSTEM','TEL_ADMIN','TU_1','TU_10','TU_100','TU_1000','TU_101','TU_102','TU_103','TU_104','TU_105','TU_106','TU_107','TU_108','TU_109','TU_11','TU_110','TU_111','TU_112','TU_113','TU_114','TU_115','TU_116','TU_117','TU_118','TU_119','TU_12','TU_120','TU_121','TU_122','TU_123','TU_124','TU_125','TU_126','TU_127','TU_128','TU_129','TU_13','TU_130','TU_131','TU_132','TU_133','TU_134','TU_135','TU_136','TU_137','TU_138','TU_139','TU_14','TU_140','TU_141','TU_142','TU_143','TU_144','TU_145','TU_146','TU_147','TU_148','TU_149','TU_15','TU_150','TU_151','TU_152','TU_153','TU_154','TU_155','TU_156','TU_157','TU_158','TU_159','TU_16','TU_160','TU_161','TU_162','TU_163','TU_164','TU_165','TU_166','TU_167','TU_168','TU_169','TU_17','TU_170','TU_171','TU_172','TU_173','TU_174','TU_175','TU_176','TU_177','TU_178','TU_179','TU_18','TU_180','TU_181','TU_182','TU_183','TU_184','TU_185','TU_186','TU_187','TU_188','TU_189','TU_19','TU_190','TU_191','TU_192','TU_193','TU_194','TU_195','TU_196','TU_197','TU_198','TU_199','TU_2','TU_20','TU_200','TU_201','TU_202','TU_203','TU_204','TU_205','TU_206','TU_207','TU_208','TU_209','TU_21','TU_210','TU_211','TU_212','TU_213','TU_214','TU_215','TU_216','TU_217','TU_218','TU_219','TU_22','TU_220','TU_221','TU_222','TU_223','TU_224','TU_225','TU_226','TU_227','TU_228','TU_229','TU_23','TU_230','TU_231','TU_232','TU_233','TU_234','TU_235','TU_236','TU_237','TU_238','TU_239','TU_24','TU_240','TU_241','TU_242','TU_243','TU_244','TU_245','TU_246','TU_247','TU_248','TU_249','TU_25','TU_250','TU_251','TU_252','TU_253','TU_254','TU_255','TU_256','TU_257','TU_258','TU_259','TU_26','TU_260','TU_261','TU_262','TU_263','TU_264','TU_265','TU_266','TU_267','TU_268','TU_269','TU_27','TU_270','TU_271','TU_272','TU_273','TU_274','TU_275','TU_276','TU_277','TU_278','TU_279','TU_28','TU_280','TU_281','TU_282','TU_283','TU_284','TU_285','TU_286','TU_287','TU_288','TU_289','TU_29','TU_290','TU_291','TU_292','TU_293','TU_294','TU_295','TU_296','TU_297','TU_298','TU_299','TU_3','TU_30','TU_300','TU_301','TU_302','TU_303','TU_304','TU_305','TU_306','TU_307','TU_308','TU_309','TU_31','TU_310','TU_311','TU_312','TU_313','TU_314','TU_315','TU_316','TU_317','TU_318','TU_319','TU_32','TU_320','TU_321','TU_322','TU_323','TU_324','TU_325','TU_326','TU_327','TU_328','TU_329','TU_33','TU_330','TU_331','TU_332','TU_333','TU_334','TU_335','TU_336','TU_337','TU_338','TU_339','TU_34','TU_340','TU_341','TU_342','TU_343','TU_344','TU_345','TU_346','TU_347','TU_348','TU_349','TU_35','TU_350','TU_351','TU_352','TU_353','TU_354','TU_355','TU_356','TU_357','TU_358','TU_359','TU_36','TU_360','TU_361','TU_362','TU_363','TU_364','TU_365','TU_366','TU_367','TU_368','TU_369','TU_37','TU_370','TU_371','TU_372','TU_373','TU_374','TU_375','TU_376','TU_377','TU_378','TU_379','TU_38','TU_380','TU_381','TU_382','TU_383','TU_384','TU_385','TU_386','TU_387','TU_388','TU_389','TU_39','TU_390','TU_391','TU_392','TU_393','TU_394','TU_395','TU_396','TU_397','TU_398','TU_399','TU_4','TU_40','TU_400','TU_401','TU_402','TU_403','TU_404','TU_405','TU_406','TU_407','TU_408','TU_409','TU_41','TU_410','TU_411','TU_412','TU_413','TU_414','TU_415','TU_416','TU_417','TU_418','TU_419','TU_42','TU_420','TU_421','TU_422','TU_423','TU_424','TU_425','TU_426','TU_427','TU_428','TU_429','TU_43','TU_430','TU_431','TU_432','TU_433','TU_434','TU_435','TU_436','TU_437','TU_438','TU_439','TU_44','TU_440','TU_441','TU_442','TU_443','TU_444','TU_445','TU_446','TU_447','TU_448','TU_449','TU_45','TU_450','TU_451','TU_452','TU_453','TU_454','TU_455','TU_456','TU_457','TU_458','TU_459','TU_46','TU_460','TU_461','TU_462','TU_463','TU_464','TU_465','TU_466','TU_467','TU_468','TU_469','TU_47','TU_470','TU_471','TU_472','TU_473','TU_474','TU_475','TU_476','TU_477','TU_478','TU_479','TU_48','TU_480','TU_481','TU_482','TU_483','TU_484','TU_485','TU_486','TU_487','TU_488','TU_489','TU_49','TU_490','TU_491','TU_492','TU_493','TU_494','TU_495','TU_496','TU_497','TU_498','TU_499','TU_5','TU_50','TU_500','TU_501','TU_502','TU_503','TU_504','TU_505','TU_506','TU_507','TU_508','TU_509','TU_51','TU_510','TU_511','TU_512','TU_513','TU_514','TU_515','TU_516','TU_517','TU_518','TU_519','TU_52','TU_520','TU_521','TU_522','TU_523','TU_524','TU_525','TU_526','TU_527','TU_528','TU_529','TU_53','TU_530','TU_531','TU_532','TU_533','TU_534','TU_535','TU_536','TU_537','TU_538','TU_539','TU_54','TU_540','TU_541','TU_542','TU_543','TU_544','TU_545','TU_546','TU_547','TU_548','TU_549','TU_55','TU_550','TU_551','TU_552','TU_553','TU_554','TU_555','TU_556','TU_557','TU_558','TU_559','TU_56','TU_560','TU_561','TU_562','TU_563','TU_564','TU_565','TU_566','TU_567','TU_568','TU_569','TU_57','TU_570','TU_571','TU_572','TU_573','TU_574','TU_575','TU_576','TU_577','TU_578','TU_579','TU_58','TU_580','TU_581','TU_582','TU_583','TU_584','TU_585','TU_586','TU_587','TU_588','TU_589','TU_59','TU_590','TU_591','TU_592','TU_593','TU_594','TU_595','TU_596','TU_597','TU_598','TU_599','TU_6','TU_60','TU_600','TU_601','TU_602','TU_603','TU_604','TU_605','TU_606','TU_607','TU_608','TU_609','TU_61','TU_610','TU_611','TU_612','TU_613','TU_614','TU_615','TU_616','TU_617','TU_618','TU_619','TU_62','TU_620','TU_621','TU_622','TU_623','TU_624','TU_625','TU_626','TU_627','TU_628','TU_629','TU_63','TU_630','TU_631','TU_632','TU_633','TU_634','TU_635','TU_636','TU_637','TU_638','TU_639','TU_64','TU_640','TU_641','TU_642','TU_643','TU_644','TU_645','TU_646','TU_647','TU_648','TU_649','TU_65','TU_650','TU_651','TU_652','TU_653','TU_654','TU_655','TU_656','TU_657','TU_658','TU_659','TU_66','TU_660','TU_661','TU_662','TU_663','TU_664','TU_665','TU_666','TU_667','TU_668','TU_669','TU_67','TU_670','TU_671','TU_672','TU_673','TU_674','TU_675','TU_676','TU_677','TU_678','TU_679','TU_68','TU_680','TU_681','TU_682','TU_683','TU_684','TU_685','TU_686','TU_687','TU_688','TU_689','TU_69','TU_690','TU_691','TU_692','TU_693','TU_694','TU_695','TU_696','TU_697','TU_698','TU_699','TU_7','TU_70','TU_700','TU_701','TU_702','TU_703','TU_704','TU_705','TU_706','TU_707','TU_708','TU_709','TU_71','TU_710','TU_711','TU_712','TU_713','TU_714','TU_715','TU_716','TU_717','TU_718','TU_719','TU_72','TU_720','TU_721','TU_722','TU_723','TU_724','TU_725','TU_726','TU_727','TU_728','TU_729','TU_73','TU_730','TU_731','TU_732','TU_733','TU_734','TU_735','TU_736','TU_737','TU_738','TU_739','TU_74','TU_740','TU_741','TU_742','TU_743','TU_744','TU_745','TU_746','TU_747','TU_748','TU_749','TU_75','TU_750','TU_751','TU_752','TU_753','TU_754','TU_755','TU_756','TU_757','TU_758','TU_759','TU_76','TU_760','TU_761','TU_762','TU_763','TU_764','TU_765','TU_766','TU_767','TU_768','TU_769','TU_77','TU_770','TU_771','TU_772','TU_773','TU_774','TU_775','TU_776','TU_777','TU_778','TU_779','TU_78','TU_780','TU_781','TU_782','TU_783','TU_784','TU_785','TU_786','TU_787','TU_788','TU_789','TU_79','TU_790','TU_791','TU_792','TU_793','TU_794','TU_795','TU_796','TU_797','TU_798','TU_799','TU_8','TU_80','TU_800','TU_801','TU_802','TU_803','TU_804','TU_805','TU_806','TU_807','TU_808','TU_809','TU_81','TU_810','TU_811','TU_812','TU_813','TU_814','TU_815','TU_816','TU_817','TU_818','TU_819','TU_82','TU_820','TU_821','TU_822','TU_823','TU_824','TU_825','TU_826','TU_827','TU_828','TU_829','TU_83','TU_830','TU_831','TU_832','TU_833','TU_834','TU_835','TU_836','TU_837','TU_838','TU_839','TU_84','TU_840','TU_841','TU_842','TU_843','TU_844','TU_845','TU_846','TU_847','TU_848','TU_849','TU_85','TU_850','TU_851','TU_852','TU_853','TU_854','TU_855','TU_856','TU_857','TU_858','TU_859','TU_86','TU_860','TU_861','TU_862','TU_863','TU_864','TU_865','TU_866','TU_867','TU_868','TU_869','TU_87','TU_870','TU_871','TU_872','TU_873','TU_874','TU_875','TU_876','TU_877','TU_878','TU_879','TU_88','TU_880','TU_881','TU_882','TU_883','TU_884','TU_885','TU_886','TU_887','TU_888','TU_889','TU_89','TU_890','TU_891','TU_892','TU_893','TU_894','TU_895','TU_896','TU_897','TU_898','TU_899','TU_9','TU_90','TU_900','TU_901','TU_902','TU_903','TU_904','TU_905','TU_906','TU_907','TU_908','TU_909','TU_91','TU_910','TU_911','TU_912','TU_913','TU_914','TU_915','TU_916','TU_917','TU_918','TU_919','TU_92','TU_920','TU_921','TU_922','TU_923','TU_924','TU_925','TU_926','TU_927','TU_928','TU_929','TU_93','TU_930','TU_931','TU_932','TU_933','TU_934','TU_935','TU_936','TU_937','TU_938','TU_939','TU_94','TU_940','TU_941','TU_942','TU_943','TU_944','TU_945','TU_946','TU_947','TU_948','TU_949','TU_95','TU_950','TU_951','TU_952','TU_953','TU_954','TU_955','TU_956','TU_957','TU_958','TU_959','TU_96','TU_960','TU_961','TU_962','TU_963','TU_964','TU_965','TU_966','TU_967','TU_968','TU_969','TU_97','TU_970','TU_971','TU_972','TU_973','TU_974','TU_975','TU_976','TU_977','TU_978','TU_979','TU_98','TU_980','TU_981','TU_982','TU_983','TU_984','TU_985','TU_986','TU_987','TU_988','TU_989','TU_99','TU_990','TU_991','TU_992','TU_993','TU_994','TU_995','TU_996','TU_997','TU_998','TU_999','_SYS_AFL','_SYS_DATA_ANONYMIZATION','_SYS_EPM','_SYS_PLAN_STABILITY','_SYS_REPO','_SYS_SQL_ANALYZER','_SYS_STATISTICS','_SYS_TABLE_REPLICAS','_SYS_TASK','_SYS_WORKLOAD_REPLAY')
 AND object_type = 'SYSTEMPRIVILEGE'
ORDER BY
      user_name ASC, grantee_type ASC, privilege ASC;

CursorLoop

The second option is to make the database loop over all user names, execute the SELECT against EFFECTIVE_PRIVILEGES for every single one of them, and carefully make sure to keep the results computed before.

This is what I do with the cursor in this implementation.

The main “trick” is of course the UNION ALL where the current result “t_r” is combined with the overall result so far “result” and then assigned to “result” again. If you have ever written a hierarchical common table expression (WITH clause) in another DBMS, you know the drill here.

DO BEGIN /* user_sys_privs cursor loop */ 
DECLARE cursor c_users for 
               (SELECT user_name FROM users);
      -- initialize result structure
      result = SELECT 
                   user_name, grantee_type, privilege 
               FROM effective_privileges 
               WHERE user_name ='' AND 1=0;
          
      FOR r_user AS c_users DO  
          t_r = SELECT
                    user_name, grantee_type, privilege
                FROM
                    effective_privileges
                WHERE
                    user_name = :r_user.user_name
                AND object_type = 'SYSTEMPRIVILEGE';
            
          result =  SELECT * FROM :t_r
                   UNION ALL
                    SELECT * FROM :result;
      END FOR;
     SELECT 
            user_name, grantee_type, privilege 
     FROM 
            :result 
     ORDER BY 
            user_name ASC, grantee_type ASC, privilege ASC;
END;

MapMerge

Lastly, for the SELECT and UNION approach, SAP HANA provides a special feature. Usually, one should follow the advice of the culinary industry and avoid single-use tools, but since the problem we are solving here is HANA-specific, we might as well use some proprietary commands. The command I am referring to is the MAP_MERGE function/operator.

The idea with this operator is that if we tell HANA specifically that we want to run the UNION-trick (from above) for a specific function, then HANA can do this more efficiently.

All we need for this is a function to refer to. So this solution first defines a user-defined-table function that returns the data for a single user and then calls this function as part of the MAP_MERGE operator.

In code, this looks as follows:

CREATE FUNCTION eff_priv (IN user_name NVARCHAR(256))
RETURNS TABLE ( user_name NVARCHAR(256)
              , grantee_type NVARCHAR(4)
              , PRIVILEGE NVARCHAR(256))
AS 
BEGIN
 
    return
        SELECT
            user_name, grantee_type, privilege
        FROM
            effective_privileges
        WHERE
            user_name = :user_name
        AND object_type = 'SYSTEMPRIVILEGE';
END;
DO BEGIN /* user_sys_privs map_merge function */
     uns = SELECT user_name FROM users;     
     res = MAP_MERGE (:uns, eff_priv(:uns.user_name));
     SELECT 
            user_name, grantee_type, privilege 
     FROM 
            :res
     ORDER BY 
            user_name ASC, grantee_type ASC, privilege ASC;
END;

Thanks for the code dump – which one is the best, you said?

It is time to get to the meat of this post.

While the code example may be helpful or instructional the interesting (to me at least) now is to get a better understanding of how these statements are different from one another.

Which is the fastest? Which uses the most CPU? (if you think this is the same question, think again!) Which uses the least amount of memory? Which one will impact the production HANA instance the least?

HANA experts will probably point to analysis tools like EXPLAIN PLAN or PlanViz (there is an OpenSAP course coming up for that), but frankly, these are not the tools for the typical SAP developers. The crux of those tools is that one already needs to know how HANA works internally to get good use of them. This is not limited to HANA, of course, but the same with other DBMS.

What I am going to use instead, are tools that are available to everyone with a HANA developer system (e.g. HANA express edition).

The tools

To start simple, we will be looking at the CPU usage of our HANA instance when we run our code. This should provide us with a form of visual impression of the kind of workload each code bit produces.

I use htop for this.

To simulate the workload of the “rest of the system”, i.e. whatever is happening in the DB aside from our code, I use Apache JMeter.

To have some queries to run, I created a SAKILA database schema and collected publicly available queries to run against it. This is in no way a proper simulation of actual system workload, but I figure it is closer to the real thing than running on an empty private database.

The following shows what the JMeter test plan looks like:

In addition to that, we will also query HANA to tell us details about the resource usage of the different implementations.

Getting a baseline impression

Looking at some CPU usage bar graphs likely will not be terribly useful without a baseline reference of what it looks like when the “normal” workload runs without the new code running.

This is what the following video shows.

Baseline – the “background” workload

The “background” workload chucks along steadily, with relatively even utilisation of all CPU threads. So, this is what “normal” looks like for us.

Checking the code

Next is to run our code (finally!) and keep an eye on how this looks for each variant in htop.

First, let’s try this without background workload.

IN-List

CursorLoop

MapMerge

What have we seen in those three videos?

Both IN-list and CursorLoop lead to a CPU utilisation pattern where a single CPU thread is heavily used, while the remaining CPU threads are more or less idle.

In contrast, the MapMerge code puts the pedal to the metal and uses whatever CPU thread is available.

Unsurprisingly, this burst of computing efforts is over much quicker than the other two options.

Results, the first

The first round of “performance testing” gives us the following table.

If all we wanted to know, is which version is the fastest, we could stop here.

MapMerge runs circles around the other two implementations.

The other two implementations are not only much slower, but also seem to ignore the strong warning about not using CURSORS if you want performance from the HANA performance guide and blog posts.
While this advice is commonly correct, our example is a nice exception to the rule.

To a more complete picture

But I didn’t build the whole test setup for nothing, so of course, we run the statements also with the “background” workload in full swing.

IN-List

CursorLoop

MapMerge

These videos are similar to the first set. IN-List and CursorLoop lead to high single-thread usage, while the MapMerge version grabs all CPU threads.

But what happened to the “background” queries? You know, the “other” queries that represent the existing, money-making workload of our HANA system?

Have those queries maybe been affected in any way by the new code?

JMeter provides nice and straightforward to use reporting tools, so let’s have a look at those.

Response Time graphs of the background queries

Again, let’s look at what the baseline response time graph for our four SAKIRA test queries looks like.

Baseline – the “background” workload

After some ramp-up, all of the queries dial in below 50ms response time. It’s very consistent and also very lightweight.

Now, let’s look at what changes when the new code is executed.

IN-List

To illustrate the effect a bit better, each of the code versions is executed a few times in a row.

For the IN-list it is very easy to spot the spikes in the response times.

CursorLoop

For the CursorLoop the effect on our test queries is less extreme than with the IN-list, but the overall response time is still worse than without the new code.

The “red” query is now consistently above 50ms.

MapMerge

Finally, with the MapMerge code we see very clearly, where all the CPU threads went: not to our test queries at all.

The spikes are of very short duration, that is true, but if your organisation is trying to keep the response times of production queries within two standard deviations, the MapMerge code just blew this goal out of the water.

Results, the second

Let’s look at the response timetable for the scenario with the “background” noise workload. Note, that I included the numbers for the “solo” runs for comparison.

As expected from the graphs above, the decreased response time of the test queries also reflects on the throughput for this workload. With none of the new code, the test queries managed to execute 119.7 times per second.

With the MapMerge code, this number is reduced to 117.3 executions per second – and we only ran our code three times during the 1-minute window of the test workload.

Given this, MapMerge is maybe not the single “best” solution.

But let’s also have a look at how the other implementations did with the workload present in the system: Both IN-List and CursorLoop slowed down a bit – CursorLoop took more than double as long as before.

However, neither of those implementations did impact the workload throughput as badly as the MapMerge code did.

What else?

At this point, it should be clear that this is not going to be a “three simple tips to make your SQL fast” post. We have three implementations with different runtime performance characteristics. What else can we learn about those statements with relative ease?

Well, HANA records how much CPU and memory any statement uses.

Expensive Statements and the SQL Plan Cache

Expensive statements trace

OPERATION           |DUR_SEC|STMT_STRING                                       |MEM_MB|
--------------------|-------|--------------------------------------------------|------|
AGGREGATED_EXECUTION|   9.29|SELECT /* user_sys_privs blunt user list */      u|811.16|
CALL                |   1.82|DO BEGIN /* user_sys_privs map_merge function */  | 21.07|
CALL                |   7.58|DO BEGIN /* user_sys_privs cursor loop */  DECLARE|  7.71|

With a small custom query against the M_EXPENSIVE_STATEMENTS view, we can get some fundamental CPU/memory usage key figures as seen above.

A precondition for using this view is that the expensive statements trace is active (and the filter conditions are fine enough to “catch” our code).

What we see now confirms our impression about the statement runtime (duration): MapMerge finishes first, CursorLoop second, and the IN-List comes last.

But looking at the recorded memory usage we see another kind of “cost” for running our code. The simple IN-list statement clocks in using 811.16 MB(!). This is nearly a GB of memory that cannot be used by any other query while this statement runs.

MapMerge is much better with 21 MB but still 3 times more memory-hungry than the CursorLoop.

Shared SQL Plan Cache

Another and slightly different (very different, in fact, but for our purpose here it’s “slightly”) source of resource usage data for queries the Shared SQL Plan cache.

EXECS|TOTAL_CURSOR_DUR_SEC|TOTAL_EXEC_MEM_MB|PLAN_MEM_MB|STMT_STRING                                                                                                                                           |
-----|--------------------|-----------------|-----------|------------------------------------------------------------------------------------------------------------------------------------------------------|
    1|             9080.22|           811.10|       4.48|SELECT /* user_sys_privs blunt user list */      user_name, grantee_type, privilege   FROM      effective_privileges   WHERE   user_name IN  ( 'DEVDUD|
-----
    1|                3.98|             1.26|       0.03|SELECT              user_name, grantee_type, privilege       FROM              "SYS"."_SYS_SS2_TMP_TABLE_0_RESULT_4_BA87DD2EBFEAAC44A33DBDE252A1BF77_2|
    1|               14.07|             2.60|       0.03|SELECT              user_name, grantee_type, privilege       FROM              "SYS"."_SYS_SS2_TMP_TABLE_0_RES_2_EEA3FA5F1AB5D84B964C200608D349CF_2" "|
 1018|             5660.31|         21453.52|       0.16|/* procedure: "DEVDUDE"."EFF_PRIV" variable: _SYS_SS2_RETURN_VAR_ line: 8 col: 5 (at pos 192) */ SELECT             user_name, grantee_type, privilege|
    1|                1.80|             3.21|       0.02|SELECT "USER_NAME" FROM "SYS"."_SYS_SS2_TMP_TABLE_0_UNS_2_EEA3FA5F1AB5D84B964C200608D349CF_2"                                                         |
    1|                1.71|             0.07|       0.03|/* procedure: "DEVDUDE"."(DO statement)" variable: UNS line: 2 col: 6 (at pos 54) */ SELECT user_name FROM users                                      |
    1|                0.00|             5.19|       0.02|DO BEGIN /* user_sys_privs map_merge function */      uns = SELECT user_name FROM users;           res = MAP_MERGE (:uns, eff_priv(:uns.user_name)); |
-----
 1018|             6340.59|          1563.29|       0.17|/* procedure: "DEVDUDE"."(DO statement)" variable: RESULT line: 20 col: 11 (at pos 651), procedure: "DEVDUDE"."(DO statement)" variable: T_R line: 12 |
    1|                0.16|             7.71|       0.18|/* procedure: "DEVDUDE"."(DO statement)" variable: RESULT line: 6 col: 7 (at pos 161) */ SELECT                     user_name, grantee_type, privilege|
    1|             7551.59|             7.71|       0.03|/* procedure: "DEVDUDE"."(DO statement)" variable: C_USERS line: 11 col: 21 (at pos 350) */ (SELECT user_name FROM users)                             |
    1|                0.00|             5.29|       0.03|DO BEGIN /* user_sys_privs cursor loop */  DECLARE cursor c_users for                 (SELECT user_name FROM users);        -- initialize result struc|

Now, this is a lot more complicated and confusing, so let’s unpack this a bit.

Except for the single SELECT IN-List solution, the implementations use several SQL commands in loops to compute the result. Since every SQL statement needs to be processed by the HANA SQL “engine” to be executed, we find entries for those executions in the Shared SQL Plan cache. While it is possible that statements do not use the Shared SQL Plan Cache, for our scenario let’s assume that this is not the case here.

I’ve grouped the different statements so that the ones belonging to the same code are in subsequent rows.

What we learn here is that besides runtime and “working memory” that is used during the execution, there is another memory requirement for every statement: the memory required to store the SQL plan itself.

For the IN-List just storing the plan requires 4.48MB, which is a lot if you consider how much can be done with this amount of memory (e.g. the whole Secret of Monkey Island game for the Amiga came on four 880KB disks = 3.520 KB).

Our other two implementations come in at 0.29 MB (MapMerge) and 0.41 MB (CursorLoop).

Another interesting bit to learn here is the number of executions (EXECS) for each of the statements. Our “loopy” constructs show statements that got executed 1018 times.

Why is that?

So glad, you asked (again).

Our code loops over all users in the system. A quick

SELECT count(*) FROM users;
COUNT(*)|
--------|
    1018|

reveals that there are currently 1018 users in my test system.

And yes, I forgot to mention that I created several users, roles, and role assignments for this test scenario so that our code would have something to work with.

Also, this kind of requirement (regularly dumping out current privilege assignments of all users to document the security setup) is more common in larger organisations with stricter compliance responsibilities. Having thousands of users in the HANA system is not outlandish for those organisations.

Which of the implementations would you choose?

Is the fastest (MapMerge) still the best for our scenario?

What about the huge memory demands of the IN-List solution? Also, as soon as the list of users changes, the statement will have to be regenerated and re-compiled. The two other solutions are “stable” in terms of the active code.

The CursorLoop is self-contained and does not require any function to be created in the DB ahead of time, so it may be more readily usable (think of using it to monitor many HANA systems across the landscape).

Of course, it all boils down to the specific requirements and circumstances. And weighing this up and making a decision is exactly the kind of engineering work our industry aspires to do.

There you go, now you know.

Maybe, if there’s interest in it, I’ll publish how to create the scenario presented here, but for now, this is already a long read.