René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Add further JPA performance rules","articleBody":"There're a lot of things in regards of performance to take into consideration when using JPA.\r\nThose should be documented as rules in the JPA performance article.\r\n\r\nA list of possible rules that @hohwille suggested in #27 :\r\n\r\n* Only load the data you need\r\n* Only load the data when you need it (e.g. in a search result screen you do not need to have all the details loaded from DB tier to app tier into frontend but only minimal sets of the records to display, once the user selects a search result for expansion or to open details screen then load the details for that record).\r\n* Declare all your JPA relations as lazy but avoid triggering of lazy loading at all - it is a stupid feature of JPA. Either you do not need to load things at all in your TX or you need them. If you do, then lazy loading only leads to N+1 problems and waste so simply load eagerly then (not via Annotations but via the query - e.g. using FETCH JOIN).\r\n* Check the execution plans of all your queries, in case of Oracle check AWRs and statistics frequently on production\r\n* Create indices - a rule of thumb is create an index for every foreign key column and consider indexes for all searched columns but not blindly but after checking execution plans\r\n* General rule: Ensure to have an expert for your DB technology in your project in case you are doing something serious (millions of records, anything below is peanuts and may work without deeper knowledge)\r\n* Avoid conversions (Cast, TO_DATE, TO_NUMBER, etc.) in your SQLs\r\n* Ensure that data-types in variable bindings fit to your DDL. Oracle does not convert the given variable value to the DDL type to convert to but instead converts every column value to the data-type you provided what can lead to catastrophic performance and DB CPU drain, etc.\r\nNever use Criteria API - it sucks and leads to disaster (I have rescued a big-data project and mean it)\r\n* Do explicit joins (selecting from multiple tables and then \"joining\" in WHERE clause makes hibernate to do cross-join what makes Oracle build the Cartesian product before evaluating the WHERE clause. If both tables to \"join\" are really big this will lead to an ultimate disaster (DB IO death)\r\n* Avoid WHERE ID IN (...) expressions and be aware of limitations (1000 values for Oracle). Instead think if you can use a sub-query to avoid having the list of IDs in the first place.\r\nso much more to tell - we should collect input from DB community, projects, etc. to get all the valuable knowledge collected.","author":{"url":"https://github.com/baumeister25","@type":"Person","name":"baumeister25"},"datePublished":"2023-03-21T09:27:36.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/38/java/issues/38"}