How to engineer your DB code

A journey of 1000 miles…

We are developers. We make the modern world tick and create the tools for the information workers. We are computer scientists, IT experts, 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 on 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”.

To me, this comes up often, when I see how DB-related code gets developed. More often than not, the first SQL statement (or HANA model) that looks like it does the job is the one that 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 table “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 ot 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 is not the only relevant aspect to measure when it comes to SQL code
  • there is no “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, that 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 reasonable 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 making 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 reference, so this solution first defines a user-defined-table function which 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 from 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 how the JMETER test plan looks like:

A very simple JMeter test plan setup to run DB queries.
A very simple JMeter test plan setup to run DB queries.

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 runs.

This is what the following video shows.

Baseline – the “background” workload

1 min of htop while background workload is running – really not much to see here except relatively even utilisation of all CPU threads.

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 on 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

“IN-list” implementation running as the only session. Very clear to see how the single SELECT statement one complete CPU thread (but not more, even though the system is idle otherwise)

CursorLoop

“CursorLoop” implementation running as the only active session. Notice the high CPU usage of a single thread throughout.

MapMerge

“MapMerge” implementation running as the only session. Notice the short spike of activity with job worker-threads using all CPU threads to 100% for a short period (between 0:03 and 0:06).

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.

Query ScenarioQuery Runtime
In-List~ 9.2 secs
CursorLoop~ 6.8 secs
MapMerge~ 1.2 secs
Query runtimes on an otherwise idle system

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 advise 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” implementation running together with the background workload. Notice the single thread using 100% of its time between 0:09 and 0:12.

CursorLoop

“IN-list” implementation running together with the background workload. Notice the single thread using 100% of its time between 0:09 and 0:19.

MapMerge

“MapMerge’ implementation running together with the background workload. Notice how the short spike of job worker-thread activity uses all CPU threads for a short period.

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 from the new code?

JMeter provides nice and straight-forward 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

1 min of response times for the 4 different background queries. Nothing special about this, the response times are consistently around 50ms for the slowest query after a few seconds.
1 min of response times for the 4 different background queries. Nothing special about this, the response times are consistently around 50ms for the slowest query after a few seconds.

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 what changes when the new code is executed.

IN-List

"IN-list"-implementation executed a few times during a 1 min period. Easy to see the high response time variation of the 4 background queries,
“IN-list”-implementation executed a few times during a 1 min period. Easy to see the high response time variation of the 4 background queries,

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

"CursorLoop"-implementation executed a few times during a 1 min period. Relatively few response time spikes, nevertheless the additional workload clearly affects the background query runtime.
“CursorLoop”-implementation executed a few times during a 1 min period. Relatively few response time spikes, nevertheless the additional workload clearly affects the background query runtime.

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 consistenyly above 50ms.

MapMerge

"MapMerge"-implementation executed a few times during a 1 min period. Very pronounced spikes in the response time of the background queries, some of them taking nearly 10x longer than without the "MapMerge" query.
“MapMerge”-implementation executed a few times during a 1 min period. Very pronounced spikes in the response time of the background queries, some of them taking nearly 10x longer than without the “MapMerge” query.

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 time table for the scenario with the “background” noise workload. Note, that I included the numbers for the “solo” runs for comparison.

Query ScenarioBackground ThroughputQuery Runtime
Background only119.7/sec
IN-List~ 9.2 secs
IN-List + Background119.3/sec~10.5 secs
CursorLoop~ 6.8 secs
CursorLoop + Background118.7/sec~ 16.6 secs
MapMerge~ 1.2 secs
MapMerge + Background117.3/sec~ 1.2 secs
Query runtimes with test workload in the background

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 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.

Our code does not “consume” CPU or memory – these things are still in your computer, believe me!

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 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 with 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” in order to be executed, we find entries for those executions in the Shared SQL Plan cache. It is possible that statements do not use the Shared SQL Plan Cache, but 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 is possible with that (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 a number of 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 set up) 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.

2 Comments

  • Jörg Brandeis says:

    Hi Lars,

    thank you for your interesting post. The considerations of competing tasks in the database is an important aspect.

    I created a variant of your first solution with dynamic SQL, because i don’t know Python so well:

    do begin
    declare lv_statement nvarchar(5000);
    select ‘SELECT user_name, grantee_type, privilege FROM effective_privileges WHERE user_name IN (”’
    || string_agg( user_name , ”’, ”’ order by user_name)
    || ”’) AND object_type = ”SYSTEMPRIVILEGE”’
    into lv_statement
    from public.users;
    execute immediate lv_statement;
    end;

    But of course this variant has at least the same drawbacks as your first solution. And maybe the caching does not work so well.

    Regards,
    Jörg

    • Hi Jörg,
      thanks for reading and the positive feedback.

      And thanks for posting your solution approach.
      Dynamic SQL can be quite useful for tasks like the one presented in the blog post.
      The main issue with it is of course based on the length limit for SQLScript string variables (5000 characters).
      With the fake user_names that I used (TU_1 … TU_999) this does not allow for all that many user_names to have in the statement.

      Just the user names plus the quotation marks plus the 999 commas clock in at:

        2000    (quotation marks)
      + 999     (commas)
      + 7    --> 7 chars (TU_1000)
      + 5394 --> 899 * 6 chars (TU_100 ... TU_999)
      + 445  --> 89 * 5 chars (TU_10 ... TU_99)
      + 36   --> 9 * 4 chars (TU_1 .. TU_9)

      = 8881 characters

      And this does not even consider the actual SQL command text or the other user names in the system.

      This limitation has “broken” several other projects for me before and I guess it was the main driver behind generating the SELECT command outside of SQLscript in the first place.

      Otherwise, you are of course right, this “one big statement” approach suffers from the same issues that the IN-list solution has (it, in fact, is the IN-list solution when it comes to running the statement), namely the slow, memory-intensive execution and the ridiculous amount of SQL cursor cache memory required for it.

      Cheers from Melbourne,
      Lars

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.