WIXLIB

IEEE TRANSACTIONS ON SOFTWARE ENGINEERINGlibraries, and their system can automatically learn the SQLpatterns, and transform the SQLs into a more optimizedform (e.g., combine loop-generated SQL selects into oneSQL).Our paper’s goal is to improve system performance byfinding redundant data problems in systems that are developed using ORM frameworks. Our approach can reduceunnecessary data transmission and DBMS computation.Different from prior work, our approach does not introduceanother layer to existing systems, which increases systemcomplexity, but rather our approach pinpoints the problemsto developers. Developers can then decide a series of actionsto prioritize and resolve the redundant data problems.Detecting Performance Bugs. Prior studies propose variousapproaches to detect different performance bugs throughrun-time indicators of such bugs. Nistor et al. [54] propose aperformance bug detection tool, which detects performanceproblems by finding similar memory-access patterns duringsystem execution. Chis et al. [19] provide a tool to detectmemory anti-patterns in Java heap dumps using a catalogue. Parsons et al. [56] present an approach for automatically detecting performance issues in enterprise applicationsthat are developed using component-based frameworks.Parsons et al. detect performance issues by reconstructingthe run-time design of the system using monitoring andanalysis approaches.Xu et al. [68] introduce copy profiling, an approach thatsummarizes runtime activity in terms of chains of datacopies, which are indicators of Java runtime bloat (i.e.,many temporary objects executing relatively simple operations). Xiao et al. [66] use different workflows to identifyand predict workflow-dependent performance bottlenecks(i.e., performance bugs) in GUI applications. Xu et al. [67]introduce a run-time analysis to identify low-utility datastructures whose costs are out of line with their gainedbenefits. Grechanik et al. develop various approaches fordetecting and preventing database deadlocks through staticand dynamic analysis [35], [36]. Chaudhuri et al. [13] propose an approach to map the DBMS profiler and the code forfinding the root causes of slow database operations. Similarto prior studies, our approach relies on dynamic systeminformation. However, we focus on systems that use ORMframeworks to map code to DBMSs.In our prior research, we propose a framework to statically identify performance anti-patterns by analyzing thesystem source code [15]. This paper is different from ourprior study in many aspects. First, in our prior study, wedevelop a framework for detecting two performance antipatterns that we observed in practice. Only one of theseperformance anti-patterns is related to data retrieval. In thispaper, we focus on the redundant data problems betweenthe needed data in the code and the SQL requested data.Performance anti-patterns and redundant data problems aretwo different sets of problems with little overlap. Performance anti-patterns may be any code patterns that mayresult in bad performance. The problem can be related tomemory, CPU, network, or database. On the other hand,redundant data problems are usually caused by requesting/updating too much data than actually needed.We propose an approach to locate such redundant data3problems, and we do not know what kinds of redundantdata problems are there before applying our approach.Second, in our prior study, we use only static analysis fordetecting performance anti-patterns. However, static analysis is prone to false positives as it is difficult to obtainan accurate data flow and pointer analysis given the assumptions made during computation [16]. Thus, most ofthe problems we study in this paper cannot be detected bysimply extending our prior framework. In this paper, wepropose a hybrid approach using both static and dynamicanalysis to locate the redundant data problems in the code.Our hybrid approach can give more precise results andbetter locate the problems in the code. In addition, weimplemented a tool to transform SQL queries into abstractsyntax trees for further analysis. Finally, we manually classify and document the redundant data problems that wediscovered, and we conduct a survey on their existence inORM frameworks across different programming languages.3BACKGROUNDIn this section, we provide some background knowledge ofORM before introducing our approach. We first provide abrief overview of different ORM frameworks, and then wediscuss how ORM accesses the DBMS using an example.Our example is shown using the Java ORM standard, JavaPersistence API (JPA), but the underlying concepts are common for other ORM frameworks.3.1Background of ORMORM has become very popular among developers dueto its convenience [39], [47]. Most modern programminglanguages, such as Java, C#, Ruby, and Python, all supportORM. Java, in particular, has a unified persistent API forORM, called Java Persistent API (JPA). JPA has become anindustry standard and is used in many open source andcommercial systems [64]. Using JPA, users can switch between different ORM providers with minimal modifications.There are many implementations of JPA, such as Hibernate [22], OpenJPA [28], EclipseLink [32], and parts ofIBM WebSphere [37]. These JPA implementations all followthe Java standard, and share similar concepts and design.However, they may experience some implementation specific differences (e.g., varying performance [48]). In thispaper, we implement our approach as a framework fordetecting redundant data problems for JPA systems due tothe popularity of JPA.3.2Translating Objects to SQL QueriesORM is responsible for mapping and translating databaseentity objects to/from database records. Figure 1 illustratessuch process in JPA. Although the implementation detailsand syntax may be different for other ORM frameworks,the fundamental idea is the same.JPA allows developers to configure a class as a databaseentity class using source code annotations. There are threecategories of source code annotations: Entities and Columns: A database entity class(marked as @Entity in the source code) is mapped

IEEE TRANSACTIONS ON SOFTWARE ENGINEERING4User.java@Entity@Table ( name ”user” )public class User {@Id@Column(name ”user id”)private long userId ;ORM generated SQL templatesselect u.id, u.name, u.address,u.phone number from User u where u.id ?;update user set name ?, address ?,phone number ? where id ?;@Column(name ”user name”)private String userName;. other instance variables@ManyToOne@JoinColumn(name ”group id”)private Group group;ORMvoid setName(String name){this.userName name;}. other getter and setter functions}Main.javaUser user ;ORM generated query using SQL templateupdate user set name Peter, address Waterloo,phone number 1234 where id 1;ORMcacheDatabaseFig. 1. An example flow of how JPA translates object manipulation to SQL. Although the syntax and configurations may be different for other ORMframeworks, the fundamental idea is the same: developers need to specify the mapping between objects and database tables, the relationshipsbetween objects, and the data retrieval configuration (e.g., eager v.s. lazy). to a database table (marked as @Table in the sourcecode). Each database entity object is mapped to arecord in the table. For example, the User class ismapped to the user table in Figure 1. @Column mapsthe instance variable to the corresponding columnin the table. For example, the userName instancevariable is mapped to the user name column.Relations: There are four different types of class relationships in JPA: OneToMany, OneToOne, ManyToOne, and ManyToMany. For example, there is a@ManyToOne relationship between User and Group(i.e., each group can have multiple users).Fetch Types: The fetch type for the associated objectscan be either EAGER or LAZY. EAGER means thatthe associated objects (e.g., User) will be retrievedonce the owner object (e.g., Group) is retrieved fromthe DBMS; LAZY means that the associated objects(e.g., User) will be retrieved from the DBMS onlywhen the associated objects are needed (by the sourcecode). Note that in some ORM frameworks, such asActiveRecord (the default ORM for Ruby on Rails),the fetch type is set per each data retrieval, but otherunderlying principals are the same. However, mostORM frameworks allow developers to change thefetch type dynamically for different use cases [30].JPA generates and may cache SQL templates (dependingon the implementation) for each database entity class. Thecached templates can avoid re-generating query templatesto improve performance. These templates are used for retrieving or updating an object in the DBMS at run-time. Asshown in Figure 1 (Main.java), a developer changes the userobject in the code in order to update a user’s name in theDBMS. JPA uses the generated update template to generatethe SQL queries for updating the user records.To optimize the performance and to reduce the numberof calls to the DBMS, JPA, as well as most other ORM frameworks, uses a local memory cache [44]. When a databaseentity object (e.g., a User object) is retrieved from the DBMS,the object is first stored in the JPA cache. If the object ismodified, JPA will push the update to the DBMS at the endof the transaction; if the object is not modified, the objectwill remain in cache until it is garbage collected or untilthe transaction is completed. By reducing the number ofrequests to the DBMS, the JPA cache reduces the overheadof network latency and the workload on database servers.Such cache mechanism provides significant performanceimprovement to systems that rely heavily on DBMSs.Our case study systems use Hibernate [22] as the JPAimplementation due to Hibernate’s popularity. However, asshown in Section 7, our survey finds that redundant dataproblems also exist in other ORM frameworks and are notspecific to the JPA implementation that we choose.4 O UR A PPROACH OF F INDING R EDUNDANT DATAP ROBLEMSIn the previous section, we introduce how ORM frameworksmap objects to database records. However, such mapping iscomplex, and usually contains some impedance mismatches(i.e., conceptual difference between relational databases andobject-oriented programming). In addition, ORM frameworks do not know what data developers need and thuscannot optimize all the database operations automatically.In this section, we present our automated approach forlocating the redundant data problem in the code due toORM mapping. Note that our approach is applicable toother ORM frameworks in other languages (may requiresome framework-specific modifications).4.1Overview of Our ApproachFigure 2 shows an overview of our approach for locatingredundant data problems. We define the needed databaseaccesses as how database-accessing functions are called during system execution. We define the requested database accesses as the corresponding generated SQL queries duringsystem execution. Our approach consists of three differentphases. First, we use static source code analysis to automatically identify the database-accessing functions (functionsthat read or modify instance variables that are mapped todatabase columns). Second, we leverage bytecode instrumentation to monitor and collect system execution traces.In particular, we collect the exercised database-accessing

IEEE TRANSACTIONS ON SOFTWARE ENGINEERING5SQLsStudiedSystemsSourceCodeExercising workloadsStaticanalysisList ofdatabase-accessingfunctionsAnalyzing SQLRequestedDatabaseAccessesAssessingTransactions aseAccessesCombining infoFig. 2. An overview of our approach for finding and evaluating redundant data problems.systems, we found that our discrepancies cover all themismatches. Namely, if a transaction has a read/write mismatch,this transactionhas atofleastthefunctions(and the locationthe onecall ofsiteofdiscrepancies.such asas well as the generated SQLs. Finally, we find thatthe screpdant data problems by comparing the exercised databaseancyin each functionstest suite. andMostthetransactionsthese thetest detailsuites ofaccessingSQLs. We inexplainhavediscrepancies75% in5 test suites), and excessiveeachphase in the(abovefollowingsubsections.data (attribute) exists in almost every transaction. On theother hand, update all does not occur in many transactionsdue to the nature of the workflows (i.e., the exercised stly read,with onlya few numberof writes). encebeWe use static code analysis to identify the mappingsin tweenPet ClinicbutlowerprevalenceinBroadleaf.database tables and the source code classes. WeSince duplicate selects occurs across transactions (i.e., itthen perform static taint analysis on all the database inis caused by ORM cache problem), we list the total numberstance variables (e.g., instance variables that are mappedof SQLs and number of duplicate selects in each test suiteto database columns) in database entity classes. Static taint(Table 3). We found that some test suites have much moreanalysisselectsallowsthanus toall thefunctionsa functionduplicatethefindother.In PetClinic, alongwe e.If athe duplicate selects are related to selecting the informationdatabaseinstanceis modifiedin pets’a function,aboutpet’s type(e.g.,variablea bird, dog,or cat) andvisits towethe functionas aonlydata-writea databasetheconsiderclinic. SincePet Clinicallows function.a rnedinafunction,(i.e., 6 types), storing the types in the cache can reduce aweconsidertheoffunctionas a data-readFor example,largenumberunnecessaryselects. function.In addition,informa- ifa tionabout pets’visitsdohasnotanchangeoften,so storingwhich is mappedto a columnin thereducedatabasetable, theninformationin the cachecan furtherunnecessaryse-thefunction getUserName(), which returns the variable name, islects.a data-read function. We also parse JPQL (Java PersistenceLanguage, the standardSQL-likelanguage for Java4. QueryPERFORMANCEIMPACTSTUDYORM frameworks) queries to keep track of which entityIn the previous section, we discuss the approach that weare retrieved/modifiedfromthethedatabaseDBMS, andsimilaruseobjectsto discoverdiscrepancies betweenthe sapplication code. However, it is not clear how these es.ancies may affect system performance and whether they areTo handlethe situationwhere both superclasssubcandidatesfor performanceanti-patterns.Therefore, andin ection, we evaluate the performance impact of these dis- todifferentbytables,we constructa class timeinheritancegraphfromcrepanciescomparingthe responsebefore andafterthe code.If a subclass is calling a data-accessing functionremovingthem.from its superclass, we use the result of the class crepanciesto determinecolumns thatsubclass functionORMsupportsdynamicallyconfiguringhow an databaseis accessing.entity object should be retrieved from the database (e.g.,retrieve all attributes or only a certain attributes) [8]. However,configurationsrequirea deep understandingof4.3 suchIdentifyingRequestedDatabaseAccessesthe system workflows, design, and APIs. Due to such complexity,it is theveryrequesteddifficult toremoveaccessesall the asdiscrepanciesWe definedatabasethe columnsmanually.Thus, wein followvery similarmethodologybythat are accessedan SQLa query.We developan SQL querypreviousstudies[5, 31] databaseto study theperformanceimpactofanalyzerto analyzeaccessinformationin SQLs.theseIn thesubsections,we dis-[2],Ourdiscrepancies.analyzer leveragesthe followingSQL parserin FoundationDBcussthe approachesthat we SQL92use to removediscrepancywhichsupports standardsyntax. eachWe firsttransformdiscussedinSection3.an SQL query into an abstract syntax tree (AST), then wetraversethe AST Updatenodes andinformation4.1.1RemovingAlllookand forExcessiveDatasuch(At- ,tribute) by Static and Dynamic Analysisand the tables that the SQL query is querying.We combine the information of both system executiontraces and the corresponding generated SQLs to remove update all and excessive data (attribute) in the test suites.For each transaction, we keep track of how the database-Listing 1: Example Transaction. transaction functionCall user . getUserName () / functionCall sql select u . id , u . name , u . address ,u . phone number from User uwhere u . id 1 / sql / transaction Fig. 3. An example of the exercised database-accessing functions andgenerated SQL queries during a transaction. the SQL to: u.id, Redundantu.name fromDataUser u where u.id 1,4.4selectFindingSince database accesses are wrapped in transactions (to asto remove excessive data (attribute). We apply a similar apsure the ACID property), we separate the accesses accordingproach to update all and remove the attributes in SQLs thatto the transactions to which they belong. Figure 3 showsare not changed during the system execution. We executean example of the resulting data. In that XML snippet, thethe SQLs before and after the transformation, and calculatefunctioncall user.getUserName() (the needed data access) isthe difference in response time after removing the discreptranslatedancies. to a select SQL (the requested data access) in atransaction.4.1.2RemovingExcessiveData (Table)FixingtheWe findredundantdata problemsat bothbythecolumnand table Sourcelevel by Codecomparing the needed and the requesteddatabasewithin andacross transactions.SinceentityweORM accessesuses annotationto configurehow an relatedknowa functionaccessing,shouldthebe databaseretrieved columnsfrom the thatdatabase.UsingistheEAGERwecompare thecolumnand writesbetweenthetheSQLfetch-settingmaycause readsperformanceproblemswheneagerly retrievedis not used. Toremove Ifthisdiscrepqueriesand the datadatabase-accessingfunctions.a columnancy,is wechangethe fetch type infromto LAZYthatbeingselected/updatedan EAGERSQL queryhas nointhe source codefunctionwhere appropriate.Then, wethemeasurethecorrespondingthat vingsuchdiscrepancy.then the transaction has a redundant data problem (e.g.,in Figure 1 the Main.java only modifies user’s name, but4.1.3 Removing Duplicate Selects by SQL Analysisall columns are updated). In other words, an SQL queryWe performa SQL analysisremovebutduplicateselectsisinis selectinga columnfrom thetoDBMS,the ot needed in the source code (similarly, the SQLprimaryqueryforeign akeysin eachThen, waswe startisandupdatingcolumnbuttable.the columnnot analyzingupdatedin Notethe testFor eachupdateineachthe SQLcode).thatsuiteaftersequentially.the static analysisstep,weand insert SQL query, we keep track of which attributes itknow the columns that a table (or database entity class) has.is modifying.Thus, in the dynamic analysis step, our approach can tell usWe then parse the SQL select queries and see if a previexactly which columns are not needed. In other words, ourously modified dat

since early 2000, and its popularity continues to rise in practice [39]. For instance, there exists ORM frameworks for most modern Object-Oriented programming languages such as Java, C#, and Python. However, despite ORM's advantages and popularity, there exist redundant data prob-lems in ORM frameworks [4], [5], [8], [24]. Such redundant