{"id":847,"date":"2009-09-10T12:06:06","date_gmt":"2009-09-10T11:06:06","guid":{"rendered":"http:\/\/blog.jteam.nl\/?p=847"},"modified":"2009-09-10T12:06:06","modified_gmt":"2009-09-10T11:06:06","slug":"feed-your-database","status":"publish","type":"post","link":"https:\/\/trifork.nl\/blog\/feed-your-database\/","title":{"rendered":"Feed Your Database"},"content":{"rendered":"

The priority that performance testing gets among other development activities is always less; it is an “afterthought”, not a critical, ongoing part of the development process.\u00a0Typically, it is done in higher environments like ST, QA. Some attention is given to performance in the prior SDLC cycles like design by making proper selection of frameworks. But during development its often limited to following the right coding standards. With this approach, the performance issues accumulate over time and then these bottlenecks become project killers.
\n
\nThe only solution to this problem is Tune Early, Tune Often<\/em><\/strong>. A process for injecting performance testing into all stages of the development process – making it, appropriately, a key part of application development is the prime necessity for successful execution of the project.\u00a0Especially in database oriented applications, even if the developers are willing to test their application, they often face issues with the scarcity of test data.<\/p>\n

    \n
  1. Brand new application planned to go-live for the first time<\/li>\n
  2. Because of some security norms, the customer is not ready to share the production data with the development team.<\/li>\n
  3. Application undergoes major schema refactoring and its lot of efforts to map the existing data to the new schema<\/li>\n
  4. Dependency on the database team.<\/li>\n<\/ol>\n

    Because of these and such similar problems, application developers cannot go beyond Unit testing. The result is they end up writing queries with complex joins, totally unaware of how it would perform with large volumetrics. And then lot of time and cost is spent on refactoring.<\/p>\n

    Scarcity of test data in development enviroment is a common problem. In the following article I’ll introduce you to the use of Benerator. Benerator is a data generator tool that can be used to feed database with pseudo-random test data.<\/p>\n

    Installing Benerator:<\/strong><\/p>\n

    Unzip the benerator<\/a> installation in an appropriate directory. Set BENERATOR_HOME that points to the path you extracted benerator.<\/p>\n

    Sample Project:<\/strong><\/p>\n

    Execute benerator-gui.bat. Configure the database details in the project creation pop-up.<\/div>\n
    \"benerator\"<\/div>\n

    \n


    \n<\/strong><\/p>\n

    In the generated project, rename benerator-demo.ben.xml to benerator.xml<\/em>. Add the database configuration details in benerator-demo.properties<\/em> file.<\/p>\n

    dbDriver=com.mysql.jdbc.Driver
    \ndbUrl=jdbc:mysql:\/\/localhost\/datagenerator_demo
    \ndbSchema=datagenerator_demo
    \ndbUser=build
    \ndbPassword=build
    \ndbBatch=true
    \nbenerator.defaultPagesize=1000<\/p>\n

    To run the datagenerator from command line, go to the benerator installation directory and run:<\/p>\n

    bin\\benerator.bat ${project_home}\\benerator.xml<\/p>\n

    Or through IDE, run org.databene.benerator.main.Benerator<\/p>\n

    Benerator ships with domain packages. Default generators are available for the commonly used entities like Organization, Address, Bank Account Number, Person, Email Address etc. Its possible to configure region and locale for these generators.<\/p>\n

    \u00a0 <attribute name="email" generator="EMailAddressGenerator" \/>\n<\/pre>\n
    Generate Fresh Data:<\/strong><\/p>\n
    \n\n     <execute target="db">\n        drop table if exists customer;\n     <\/execute>\n\n     <execute target="db">\n        create table customer (\n            id bigint not null auto_increment,\n            name varchar(255),\n            pword varchar(20),\n            address varchar(100),\n            phone varchar(20),\n            primary key (id)\n        );\n    <\/execute>\n\n    <import defaults="true"\/>\n    <import domain="person"\/>\n    <import domain="address"\/>\n\n    <!-- Populate Customer Entity -->\n    <create-entities name="customer" count="{ftl:${customer_count}}" consumer="db">\n        <variable name="person" generator="PersonGenerator" dataset="{${country}}" locale="{${locale}}"\/>\n        <variable name="address" generator="AddressGenerator" dataset="{${country}}"\/>\n        <id name="id" strategy="{${idStrategy}}" param="{${customerIdParam}}" source="db" \/>\n        <attribute name="name" script="{ftl:${person.givenName} ${person.familyName}}" \/>\n        <attribute name="pword" pattern="[A-Za-z0-9]{8,12}" \/>\n        <attribute name="address" script="{ftl:${address.street} ${address.houseNumber} ${address.city} ${address.country} ${address.zipCode} }" \/>\n        <attribute name="phone" script="{ftl:${address.mobilePhone}}" \/>\n    <\/create-entities>\n\n<\/pre>\n
    Generating Data for Entities with Parent Child Relationships:<\/strong><\/p>\n
    For generating parent child entities e.g. customer and customer order, the following configuration can be used.<\/p>\n
    \n    <create-entities name="cust_order" count="{ftl:${customer_count * orders_per_customer}}" consumer="db">\n        <id name="id" strategy="{${idStrategy}}" source="db" param="{${custOrderIdParam}}" \/>\n        <attribute name="customer_id" source="db" selector="select id from customer" cyclic="true" \/>\n        <attribute name="created" nullable="false" generator="CurrentDateGenerator"\/>\n    <\/create-entities>\n<\/pre>\n
    But this configuration is not optimal if you need to generate data for a table with few million items. The easiest and the most optimum way to generate references is to control the id range in which referenced entities are generated and then generate the ids in that range without querying the database.<\/p>\n
    \n    <create-entities name="cust_order" count="{ftl:${customer_count * orders_per_customer}}" consumer="db">\n        <id name="id" strategy="{${idStrategy}}" source="db" param="{${custOrderIdParam}}" \/>\n        <attribute name="customer_id" type="int" min="1" max="{ftl:${customer_count}}" cyclic="true" \/>\n        <attribute name="created" nullable="false" generator="CurrentDateGenerator"\/>\n    <\/create-entities>\n<\/pre>\n
    As you can see the customer_id is generated without querying the customer table here.<\/p>\n
    With benerator it is also possible to feed the database with DBUnit export.<\/p>\n
    \n      <create-entities source="shop.dbunit.xml" consumer="db" \/>\n<\/pre>\n
    Performance could be the issue while populating mass test data. Few tips for generating mass test data:<\/p>\n