{"id":6993,"date":"2020-12-18T00:31:00","date_gmt":"2020-12-18T08:31:00","guid":{"rendered":"https:\/\/alt2.minisoft.com\/support\/?p=6993"},"modified":"2021-01-05T13:37:41","modified_gmt":"2021-01-05T21:37:41","slug":"efficient-database-queries-in-eformz","status":"publish","type":"post","link":"https:\/\/c002.minisoft.com\/support\/efficient-database-queries-in-eformz\/","title":{"rendered":"Database Best Practices: Efficient Database Queries in eFORMz"},"content":{"rendered":"\n<p>The following are some guidelines for configuring database connections in eFORMz:<\/p>\n\n\n\n<p>\u2022 Plan for switching between development and production. <br>\u2022 Define databases using Databases.xml. Defning databases within projects limits switching.<br>\u2022 Use different names for dev and prod makes switching difficult. Use a common name and change the active db defined name as shown below:<\/p>\n\n\n\n<p><a href=\"https:\/\/minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB2.png\"><img decoding=\"async\" loading=\"lazy\" width=\"144\" height=\"62\" class=\"alignnone size-full wp-image-8756\" src=\"https:\/\/minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB2.png\" alt=\"\"><\/a> <a href=\"https:\/\/minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB1.png\"><img decoding=\"async\" loading=\"lazy\" width=\"171\" height=\"76\" class=\"alignnone size-full wp-image-8757\" src=\"https:\/\/minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB1.png\" alt=\"\" srcset=\"https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB1.png 171w, https:\/\/c002.minisoft.com\/support\/wp-content\/uploads\/2018\/12\/DB1-150x67.png 150w\" sizes=\"(max-width: 171px) 100vw, 171px\" \/><\/a><br><br>Based on the above screenshots, the \u2018database\u2019 defined in all the project actions will always be \u2018h2Active\u2019. This facilitates a change from a single location, otherwise you are needing to change every SQLLoad and SQLUpdate in the project.<\/p>\n\n\n\n<p><br>\u2022 Be careful not over-ride the schema\/library.\u00a0<br>\u2022 Add conditions to as many SQL calls as possible.<br>\u2022 Combine SQL calls. Limiting the number of calls improves performance.<br>\u2022 Use Stored procedures or views when possible.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Efficient Queries<\/h2>\n\n\n\n<p>When you use <a href=\"\/support\/index.php\/loading-multiple-rows-with-sql-load\/\">SQL Load<\/a>, <a href=\"\/support\/index.php\/sql-lookup\/\">SQL Lookup<\/a>, or <a href=\"\/support\/index.php\/eformz-table-lookup\/\">Table Lookup<\/a> to query a relational database, you can take steps to ensure efficient use of resources. Skipping this step can lead to excessive time to complete even a basic query.<\/p>\n\n\n\n<ul><li>Ensure you pass valid values in your query. If you pass values that are empty or not valid, your query wastes time. One way to control what queries are sent is to use conditions on an SQL Load step in a procedure. For example, if you are looking up prices based on a SKU, set a condition that the SKU value is not an empty string.<\/li><li>Ensure that the table or view you query is properly indexed so you do not risk running a full table scan against a large table.<\/li><li>Using a view or calling a stored procedure can improve performance and improve security for some types of queries, especially if they include joins.<\/li><li>Select only the data that you require. If a table has 40 columns, but you need only three, limit the query to those three columns.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Additional Resources<\/h2>\n\n\n\n<ul><li><a href=\"\/support\/index.php\/calling-database-functions\/\">Calling database functions<\/a><\/li><li><a href=\"\/support\/index.php\/database-url-and-driver-classes\/\">Database URL and Driver Classes<\/a><\/li><li><a href=\"\/support\/index.php\/where-to-set-up-your-database-connection\/\">Where to Set Up Your Database Connection<\/a><\/li><li><a href=\"\/support\/index.php\/calling-database-stored-procedures\/\">Calling database stored procedures<\/a><\/li><li><a href=\"\/support\/index.php\/troubleshooting-a-database-connection\/\">Troubleshooting a Database Connection<\/a><\/li><li><a href=\"\/support\/index.php\/eformz-table-lookup\/\">eFORMz Table Lookup<\/a><\/li><li><a href=\"\/support\/index.php\/sql-update\/\">SQL Update<\/a><\/li><li><a href=\"\/support\/index.php\/loading-multiple-rows-with-sql-load\/\">Loading multiple rows with SQL Load<\/a><\/li><li><a href=\"\/support\/index.php\/sql-resultset-preprocessor\/\">SQL ResultSet Preprocessor<\/a><\/li><li><a href=\"\/support\/index.php\/create-placeholder-names-for-sql-columns\/\">Create placeholder names for SQL columns<\/a><\/li><li><a href=\"\/support\/index.php\/troubleshooting-sql-database-error-the-statement-did-not-return-a-result-set\/\">TroubleShooting: SQL Error \u2013 The Statement did not return a result set<\/a><\/li><li><a href=\"\/support\/index.php\/h2-database\/\">H2 Database<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>The following are some guidelines for configuring database connections in eFORMz: \u2022 Plan for switching between development and production. \u2022 Define databases using Databases.xml. Defning databases within projects limits switching.\u2022 Use different names for dev and prod makes switching difficult. Use a common name and change the active db defined name as shown below: Based [&hellip;]<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[436,438,84,437,338],"_links":{"self":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/6993"}],"collection":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/comments?post=6993"}],"version-history":[{"count":12,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/6993\/revisions"}],"predecessor-version":[{"id":8760,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/posts\/6993\/revisions\/8760"}],"wp:attachment":[{"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/media?parent=6993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/categories?post=6993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/c002.minisoft.com\/support\/wp-json\/wp\/v2\/tags?post=6993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}