TO NHIBERNATE OR NOT TO NHIBERNATE SQL Saturday

TO NHIBERNATE OR NOT TO NHIBERNATE? SQL Saturday Bratislava 2016 Volodymyr Usarskyy

SPONSORS

BOOKS TO READ * A bit outdated * Haven’t read this one * Must read http: //www. amazon. com/NHibernate-Action-Pierre-Christian-Paperback/dp/B 00 M 8 PADAM http: //www. amazon. com/Learning-NHibernate-4 -Suhas-Chatekar/dp/1784393568 http: //www. amazon. com/Pro-Server-Internals-Dmitri-Korotkevitch/dp/1430259620

WHAT IS NHIBERNATE? “NHIBERNATE IS AN OBJECT/RELATIONAL MAPPING TOOL FOR NET. ENVIRONMENTS. THE TERM OBJECT/RELATIONAL MAPPING (ORM) REFERS TO THE TECHNIQUE OF MAPPING A DATA REPRESENTATION FROM AN OBJECT MODEL TO A RELATIONAL DATA MODEL WITH A SQL-BASED SCHEMA. ” “NHIBERNATE NOT ONLY TAKES CARE OF THE MAPPING FROM. NET CLASSES TO DATABASE TABLES (AND FROM. NET DATA TYPES TO SQL DATA TYPES), BUT ALSO PROVIDES DATA QUERY AND RETRIEVAL FACILITIES AND CAN SIGNIFICANTLY REDUCE DEVELOPMENT TIME OTHERWISE SPENT WITH MANUAL DATA HANDLING INSQL AND ADO. NET. NHIBERNATE'S GOAL IS TO RELIEVE THE DEVELOPER FROM 95 PERSISTENCE RELATED PROGRAMMING TASKS. ” * * check http: //nhibernate. info for more info PERCENT OF COMMON DATA

WHAT IS NHIBERNATE? “NHIBERNATE IS AN OBJECT/RELATIONAL MAPPING TOOL FOR NET. ENVIRONMENTS. THE TERM OBJECT/RELATIONAL MAPPING (ORM) REFERS TO THE TECHNIQUE OF MAPPING A DATA REPRESENTATION FROM AN OBJECT MODEL TO A RELATIONAL DATA MODEL WITH A SQL-BASED SCHEMA. ” “NHIBERNATE NOT ONLY TAKES CARE OF THE MAPPING FROM. NET CLASSES TO DATABASE TABLES (AND FROM. NET DATA TYPES TO SQL DATA TYPES), BUT ALSO PROVIDES DATA QUERY AND RETRIEVAL FACILITIES AND CAN SIGNIFICANTLY REDUCE DEVELOPMENT TIME OTHERWISE SPENT WITH MANUAL DATA HANDLING INSQL AND ADO. NET. NHIBERNATE'S GOAL IS TO RELIEVE THE DEVELOPER FROM 95 PERSISTENCE RELATED PROGRAMMING TASKS. ”* * check http: //nhibernate. info for more info PERCENT OF COMMON DATA

WHY PEOPLE* ARE AFRAID OF NHIBERNATE? • MAIN REASON: BECAUSE DEVELOPERS CAN SCREW UP ENTIRE APPLICATION BY WRITING NOT EFFICIENT C# CODE THAT WILL GENERATE EVEN WORSE SQL CODE • NO CONTROL OVER GENERATED SQL • NOT WELL OPTIMIZED SQL CODE • SECURITY • NO CONTROL OVER GENERATED SQL • WHAT IF DEVELOPERS ACCESS RESTRICTED DATA? • WHAT IF DEVELOPERS ACCIDENTALLY DELETE SOMETHING IMPORTANT? • NOT ABLE TO MAP ONE N. ET TYPE TO MULTIPLE TABLES** • NOT EASY TO START AS WITH PLAINADO. NET + SPS • NO ASYNC/AWAIT * Mostly DBAs ** Heard just once in my life; 5 minutes after the developer was advocating strongly against mapping one entity to multiple tables

DBA’S FACE WHEN A DEVELOPER SAYS THAT HE WANTS TO USE NHIBERNATE

BEFORE WE LOOK INTO SOLUTIONS FOR ABOVE MENTIONED PROBLEMS, LETS HAVE A LOOK ON WHERE NHIBERNATE IS LOCATED ON TYPICAL APPLICATION DIAGRAM…

APPLICATION DIAGRAM This is definitely not your biggest issue here Rev. proxies & Load balancers & FW Web application (Angular. JS) Web application (ASP. NET) CDN Message Queue/Bus Distributed cache (if used) API Web NHibernate Async Jobs Win Services Encryption NHibernate Security Service-1 Service-N Scheduled Tasks Win Services NHibernate Elastic Full text search Main DB IAM DB Scheduled Tasks DB SMTP Server Or Cloud Service Internal services

TYPICAL SOLUTIONS TO MENTIONED PROBLEMS Problem Solution No control over generated SQL No really good solution but: 1. You can use custom SQL (then however it does not make sense to use NHibernate ) 2. Know your tools (this should solve most of the performance issues) 3. Profiling What if developers access restricted data? 1. 2. 3. GRANT/DENY rights on views and tables Create filtered views (for example, function + WHERE) ROW LEVEL security What if developers accidentally delete something important? Block hard deletes + do soft deletes* Not able to map one. NET type to multiple tables Possible but why would you do it? Not easy to start as with ADO. NET + SPs Read books/blogs/docs No async/await No solution Not consistent column aliases * No solution * almost an industry standard * Most of the developers that are against NHibernate don’t even know about this problem

HOW DOES TYPICAL DATA ACCESS LAYER LOOKS LIKE? This would look completely OK if not… THIS MONKEY CODING! + 150 SPs to select/update/insert/delete

HOW DOES TYPICAL NHIBERNATE-BASED DATA ACCESS LAYER LOOKS LIKE? * In addition all properties in your data entities must be virtual

CUSTOM SQL VS. NHIBERNATE GENERATED EXEC sp_executesql N' SELECT DISTINCT m. [merchantid], … FROM [dbo]. [deals] as d INNER JOIN [dbo]. [merchants] as m ON d. merchantid = m. merchantid WHERE d. dealtypeid = @dealtypeid SELECT DISTINCT a. [applicationid], … FROM [dbo]. [deals] as d INNER JOIN [dbo]. [applications] as a ON d. applicationid = a. applicationid WHERE d. dealtypeid = @dealtypeid SELECT [dealid], … FROM [dbo]. [vwdeals 2] WHERE dealtypeid = @dealtypeid', N'@dealtypeid smallint', @dealtypeid = 1 -- MERCHANTS Table 'Deals'. Scan count 1, logical reads 5, physical reads 0 Table 'Merchants'. Scan count 1, logical reads 3, physical reads 0 -- APPLICATIONS Table 'Deals'. Scan count 1, logical reads 5, physical reads 0 Table 'Applications'. Scan count 1, logical reads 3, physical reads 0 -- DEALS Table 'Realty. Entities'. Scan count 2, logical reads 692, physical reads 0 Table 'Deals'. Scan count 1, logical reads 1582, physical reads 0 Table 'Realty. Entity. Details'. Scan count 4, logical reads 28, physical reads 0 EXEC sp_executesql N'select dealdataen 0_. Deal. ID as Deal. ID 39_0_, … applicatio 1_. Name as Name 38_1_, … merchantda 2_. Name as Name 40_2_, … from vw. Deals 2 dealdataen 0_ left outer join Applications applicatio 1_ on dealdataen 0_. Application. ID=applicatio 1_. Application. ID left outer join Merchants merchantda 2_ on dealdataen 0_. Merchant. ID=merchantda 2_. Merchant. ID where cast(dealdataen 0_. Deal. Type. ID as INT)=@p 0', N'@p 0 int', @p 0 = 1 Table 'Realty. Entities'. Scan count 2, logical reads 692, physical reads 0 Table 'Deals'. Scan count 1, logical reads 1582, physical reads 0 Table 'Realty. Entity. Details'. Scan count 4, logical reads 28, physical reads 0 Table 'Applications'. Scan count 1, logical reads 3, physical reads 0 Table 'Merchants'. Scan count 1, logical reads 3, physical reads 0

CUSTOM SQL VS. NHIBERNATE GENERATED EXEC sp_executesql N' SELECT DISTINCT m. [merchantid], … FROM [dbo]. [deals] as d INNER JOIN [dbo]. [merchants] as m ON d. merchantid = m. merchantid WHERE d. dealtypeid = @dealtypeid SELECT DISTINCT a. [applicationid], … FROM [dbo]. [deals] as d INNER JOIN [dbo]. [applications] as a ON d. applicationid = a. applicationid WHERE d. dealtypeid = @dealtypeid SELECT [dealid], … FROM [dbo]. [vwdeals 2] WHERE dealtypeid = @dealtypeid', N'@dealtypeid smallint', @dealtypeid = 1 -- MERCHANTS Table 'Deals'. Scan count 1, logical reads 5, physical reads 0 Table 'Merchants'. Scan count 1, logical reads 3, physical reads 0 -- APPLICATIONS Table 'Deals'. Scan count 1, logical reads 5, physical reads 0 Table 'Applications'. Scan count 1, logical reads 3, physical reads 0 -- DEALS Table 'Realty. Entities'. Scan count 2, logical reads 692, physical reads 0 Table 'Deals'. Scan count 1, logical reads 1582, physical reads 0 Table 'Realty. Entity. Details'. Scan count 4, logical reads 28, physical reads 0 EXEC sp_executesql N'select dealdataen 0_. Deal. ID as Deal. ID 39_0_, … applicatio 1_. Name as Name 38_1_, … merchantda 2_. Name as Name 40_2_, … from vw. Deals 2 dealdataen 0_ left outer join Applications applicatio 1_ on dealdataen 0_. Application. ID=applicatio 1_. Application. ID left outer join Merchants merchantda 2_ on dealdataen 0_. Merchant. ID=merchantda 2_. Merchant. ID where cast(dealdataen 0_. Deal. Type. ID as INT)=@p 0', N'@p 0 int', @p 0 = 1 Table 'Realty. Entities'. Scan count 2, logical reads 692, physical reads 0 Table 'Deals'. Scan count 1, logical reads 1582, physical reads 0 Table 'Realty. Entity. Details'. Scan count 4, logical reads 28, physical reads 0 Table 'Applications'. Scan count 1, logical reads 3, physical reads 0 Table 'Merchants'. Scan count 1, logical reads 3, physical reads 0

NHIBERNATE CACHE • USES TWO LEVELS (1) • FIRST LEVEL IS IN-MEMORY, ENABLED BY DEFAULT; ASSOCIATED WITH CURRENTSESSION • SECOND LEVEL HAS TO BE EXPLICITLY CONFIGURED • IMPORTANT: TRIES TO USE CACHE “TRANSACTIONS” (REQUIRES ADDITIONAL LOCK AND RELEASE CALLS IN CASE OF DISTRIBUTED CACHE) (2) • SECOND LEVEL CACHE USES A FEW SUB-LEVELS • ENTITIES CACHE (STORES ENTITIES) • COLLECTIONS CACHE (STORES RELATION BETWEEN ENTITY AND DEPENDENT COLLECTIONS) • QUERY CACHE (STORES THE IDENTIFIERS OF THE ENTITIES RETUNED AS A RESULT OF A QUERY) • TIMESTAMP CACHE (STORES TRACK ON WHEN TABLE WAS UPDATED) • USES REGIONS • IMPORTANT THINGS TO REMEMBER: • NHIBERNATE USES CACHE SIMILAR TO DATABASE!ALL ENTITIES ARE CACHED SEPARATELY, IT DOES NOT DIRECTLY CACHE QUERY RESULT • AT SOME POINT GC CAN COLLECT INTERNAL COLLECTION THAT STORES LIST OF CACHED IDENTIFIERS; THIS CAN RESULT INTO A “SELECTN+1 PROBLEM” IF YOU ARE ON THE MIDDLE OF HUGE SELECT (IT WILL BE EVEN WORSE IF YOU USE DISTRIBUTED CACHE) 1) http: //stackoverflow. com/questions/337072/what-are-first-and-second-level-caching-in-hibernate http: //blog. streamlinelogic. ca/2007/03/nhibernate-cache. html 2) https: //ayende. com/blog/3976/nhibernate-2 nd-level-cache 3) https: //ngocthanhit. wordpress. com/2009/04/21/first-and-second-level-caching-in-nhibernate/

NHIBERNATE CACHE • AVAILABLE CACHES: • SYSCACHE – RELIES ONASP. NET CACHE • SYSCACHE 2 – EXTENDED VERSION OFSYSCACHE WITH EXPIRATION POLICIES • SYSCACHE 3 – SIMILAR TOSYSCACHE 2 BUT USES • RTMEMORYCACHE – SIMILAR TOSYSCACHE BUT USES SYSTEM. RUNTIME. CACHING. MEMORYCACHE • MEMCACHE • NCACHE – HAVE TO PAY FOR THIS • REDIS – DISTRIBUTED CACHE (DB? ) • SERVICE FABRIC CACHE (VELOCITY) – OBSOLETE, MS MIGRATES EVERYTHING TO REDIS • COUCHBASE – NOSQL DATABASE • THINGS TO REMEMBER: • NOT ALL CACHES ARE EQUAL! • NOT ALL CACHES ARE WELL TESTED! • PROFILE CACHE PROVIDER ON YOUR DATA BEFORE YOU START USING IT!

HOW FAST IS NHIBERNATE (SIMPLE ENTITIES)? Runs 10 times with warmup Entity contains only base types like bool, Date. Time, int, string 510 rows without cache 54784 rows 510 rows with cache without cache 54784 rows with cache (!) ADO. NET 00: 03. 68 00: 09. 54 NH statefull 00: 03. 73 00: 20. 18 Rt. Cahce: 00: 00. 43 Sys. Cache: 00: 00. 46 Redis: 00: 04. 55 Redis+JSON: 00: 05. 44 Rt. Cahce: 00: 08. 87 Sys. Cache: 00: 08. 53 Redis: Never worked out because of the GC NH stateless 00: 03. 67 00: 17. 94 Cache not used ADO. NET - A 00: 01. 89 00: 11. 42 NH statefull - A 00: 01. 95 00: 22. 14 Redis: 00: 14. 70 Redis+JSON: 00: 21. 82 Not tested, takes too much time NH stateless - A 00: 01. 95 00: 19. 81 Cache not used Do not select thousands of rows with off-process second level cache in production!

HOW FAST IS NHIBERNATE (COMPLEX TYPES)? Runs 10 times with warmup Complex entity, properties refer to other entities; fetching all child properties (no lazy loading) 510 rows without cache 54784 rows without cache 510 rows with cache 54784 rows with cache ADO. NET 00: 03. 62 00: 10. 35 NH statefull 00: 03. 63 00: 34. 12 Rt. Cahce: 00: 00. 52 Sys. Cache: 00: 00. 51 Redis: 00: 07. 13 Redis+JSON: Rt. Cahce: 00: 16. 73 Sys. Cache: 00: 16. 13 Redis: Never worked out because of the GC NH stateless 00: 03. 95 00: 47. 17 Cache not used ADO. NET - A 00: 01. 78 00: 12. 30 NH statefull - A 00: 02. 27 00: 44. 31 Redis: 00: 16. 44 Redis+Json: 00: 23. 49 Not tested, takes too much time NH stateless - A 00: 02. 21 00: 01: 04. 00 Cache not used

HOW MUCH MEMORY IS CONSUMED (SIMPLE ENTITIES)? Runs 5 times 510 rows without cache 54784 rows without cache 510 rows with cache 54784 rows with cache ADO. NET 1 481 765 105 596 943 NH statefull * 17 886 507 457 529 741 Rt. Cahce: 19 026 015 Sys. Cache: 19 117 331 Redis: 94 010 583 Redis+JSON: 44 870 931 Rt. Cahce: 575 219 295 Sys. Cache: 587 477 658 Redis: could not wait so long (> 5 GB was used) Redis+JSON: could not wait so long NH stateless * 17 129 099 377 538 871 Cache not used Worth to mention that 1. ~50% of this memory is allocated by Fluent. NHibernate. In this example NHibernate for each database request allocates about 7 -8 times more memory than “perfect” ADO. NET approach 2. XML mappings will show memory footprint very close to Fluent. NHibernate 3. Using new “By. Code” NHibernate mappings helps to reduce memory allocations

WHY TO CONSIDER NHIBERNATE? • SMOOTHER START • LESS DA/SP CODING • PREDICTABLE BEHAVIOR • FREQUENT CHANGES ARE EASIER • A LOT OF EXTENSION POINTS • INTERCEPTORS • CACHING • LOGGING • AUDIT • MANY COOL FEATURES LIKE SHARDING AND TABLE PARTITIONING • VALIDATION BEFORE DATA GETS INTO DATABASE • YOU CAN STILL COMBINE IT WITHADO. NET • YOU CAN EASILY MIGRATE TO ANOTHERDB ENGINE (WHO SERIOUSLY CONSIDERS THIS AS A FEATURE? ) One of my last projects: o 150+ SPs o Some SPs return 20+ results sets Especially on the beginning, when your database schema changes frequently Cache invalidation might be a serious problem in large systems. With NHibernate it literary takes half an hour to go from “absolutely no cache” to “all major things are cached”.

EXTRAS • • HTTPS: //GITHUB. COM/USARSKYY/NH-TESTS • HTTPS: //MSDN. MICROSOFT. COM/EN-US/LIBRARY/X 2 TYFYBC(V=VS. 110). ASPX • • CLR PROFILER 4. 0: HTTPS: //WWW. MICROSOFT. COM/EN-US/DOWNLOAD/DETAILS. ASPX? ID=16273

EVALUATION FORMS • SESSION EVALUATION FORMS HTTP: //WWW. SQLSATURDAY. COM/529/SESSIONS/SESSIONEVALUATION. ASPX • EVENT EVALUATION FORMS HTTP: //WWW. SQLSATURDAY. COM/529/ EVENTEVAL. ASPX