Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\design.07.5.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Managing Connections and Updates</title> <link rel="StyleSheet" href="css/design.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="design.07.1.html#107050">5 Designing ODBC Applications for Performance Optimization</a> : Managing Connections and Updates</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="107050">Managing Connections and Updates</a></div> <div class="Body"><a name="107051">The guidelines in this section will help you to manage connections and updates to improve </a>system performance for your ODBC applications.</div> <div class="head_2"><a name="107052">Managing Connections</a></div> <div class="Body"><a name="107053">Connection management is important to application performance. Optimize your application </a>by connecting once and using multiple statement handles, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection. </div> <div class="Body"><a name="107054">Although gathering driver information at connect time is a good practice, it is often more </a>efficient to gather it in one step rather than two steps. Some ODBC applications are designed to call informational gathering routines that have no record of already attached connection handles. For example, some applications establish a connection and then call a routine in a separate DLL or shared library that reattaches and gathers information about the driver. Applications that are designed as separate entities should pass the already connected HDBC pointer to the data collection routine instead of establishing a second connection. </div> <div class="Body"><a name="107055">Another bad practice is to connect and disconnect several times throughout your application </a>to process SQL statements. Connection handles can have multiple statement handles associated with them. Statement handles can provide memory storage for information about SQL statements. Therefore, applications do not need to allocate new connection handles to process SQL statements. Instead, applications should use statement handles to manage multiple SQL statements.</div> <div class="Body"><a name="107056">You can significantly improve performance with connection pooling, especially for </a>applications that connect over a network or through the World Wide Web. With connection pooling, closing connections does not close the physical connection to the database. When an application requests a connection, an active connection from the connection pool is reused, avoiding the network round trips needed to create a new connection.</div> <div class="Body"><a name="107057">Connection and statement handling should be addressed before implementation. Spending </a>time and thoughtfully handling connection management improves application performance and maintainability.</div> <div class="head_2"><a name="107058">Managing Commits in Transactions</a></div> <div class="Body"><a name="107059">Committing data is extremely disk I/O intensive and slow. If the driver can support </a>transactions, always turn autocommit off. </div> <div class="Body"><a name="107060">What does a commit actually involve? The database server must flush back to disk every </a>data page that contains updated or new data. This is not a sequential write but a searched write to replace existing data in the table. By default, autocommit is on when connecting to a data source. Autocommit mode usually impairs system performance because of the significant amount of disk I/O needed to commit every operation. </div> <div class="Body"><a name="107061">Some database servers do not provide an Autocommit mode. For this type of server, the </a>ODBC driver must explicitly issue a COMMIT statement and a BEGIN TRANSACTION for every operation sent to the server. In addition to the large amount of disk I/O required to support Autocommit mode, a performance penalty is paid for up to three network requests for every statement issued by an application.</div> <div class="Body"><a name="107062">Although using transactions can help application performance, do not take this tip too far. </a>Leaving transactions active can reduce throughput by holding locks on rows for long times, preventing other users from accessing the rows. Commit transactions in intervals that allow maximum concurrency.</div> <div class="head_2"><a name="107063">Choosing the Right Transaction Model</a></div> <div class="Body"><a name="107064">Many systems support distributed transactions; that is, transactions that span multiple </a>connections. Distributed transactions are at least four times slower than normal transactions due to the logging and network round trips necessary to communicate between all the components involved in the distributed transaction. Unless distributed transactions are required, avoid using them. Instead, use local transactions when possible.</div> <div class="head_2"><a name="107065">Using Positioned Updates and Deletes</a></div> <div class="Body"><a name="107066">Use positioned updates and deletes or SQLSetPos to update data. Although positioned </a>updates do not apply to all types of applications, developers should use positioned updates and deletes when it makes sense. Positioned updates (either through <span class="Syntax">UPDATE WHERE </span><span class="Syntax">CURRENT OF CURSOR</span> or through SQLSetPos) allow the developer to signal the driver to "change the data here" by positioning the database cursor at the appropriate row to be changed. The designer is not forced to build a complex SQL statement, but simply supplies the data to be changed. </div> <div class="Body"><a name="107067">In addition to making the application more maintainable, positioned updates usually result in </a>improved performance. Because the database server is already positioned on the row for the Select statement in process, performance-expensive operations to locate the row to be changed are not needed. If the row must be located, the server typically has an internal pointer to the row available (for example, ROWID). </div> <div class="head_2"><a name="107068">Using SQLSpecialColumns</a></div> <div class="Body"><a name="107069">Use SQLSpecialColumns to determine the optimal set of columns to use in the Where </a>clause for updating data. Often, pseudo-columns provide the fastest access to the data, and these columns can only be determined by using SQLSpecialColumns.</div> <div class="Body"><a name="107070">Some applications cannot be designed to take advantage of positioned updates and </a>deletes. These applications typically update data by forming a Where clause consisting of some subset of the column values returned in the result set. Some applications may formulate the Where clause by using all searchable result columns or by calling SQLStatistics to find columns that are part of a unique index. These methods typically work, but can result in fairly complex queries.</div> <div class="Body"><a name="107071">Consider the following example:</a></div> <div class="syntax_wide_first"><a name="107072">rc = SQLExecDirect (hstmt, "SELECT first_name, last_name, ssn, address, city, state, zip FROM </a></div> <div class="syntax_wide"><a name="107924"> </a> emp", SQL_NTS);</div> <div class="syntax_wide"><a name="107073">// fetchdata</a></div> <div class="syntax_wide"><a name="107074">...</a></div> <div class="syntax_wide"><a name="107075">rc = SQLExecDirect (hstmt, "UPDATE EMP SET ADDRESS = ? WHERE first_name = ? AND last_name = ? AND </a></div> <div class="syntax_wide"><a name="107925"> </a> ssn = ? AND address = ? AND city = ? AND state = ? AND zip = ?", SQL_NTS);</div> <div class="syntax_wide"><a name="107076">// fairly complex query</a></div> <div class="Body"><a name="107077">Applications should call SQLSpecialColumns/SQL_BEST_ROWID to retrieve the optimal </a>set of columns (possibly a pseudo-column) that identifies a given record. Many databases support special columns that are not explicitly defined by the user in the table definition but are "hidden" columns of every table (for example, ROWID and TID). These pseudo-columns provide the fastest access to data because they typically point to the exact location of the record. Because pseudo-columns are not part of the explicit table definition, they are not returned from SQLColumns. To determine if pseudo-columns exist, call SQLSpecialColumns.</div> <div class="Body"><a name="107078">Consider the previous example again:</a></div> <div class="syntax_wide_first"><a name="107079">...</a></div> <div class="syntax_wide"><a name="107080">rc = SQLSpecialColumns (hstmt, ..... â??empâ??, ...);</a></div> <div class="syntax_wide"><a name="107081">...</a></div> <div class="syntax_wide"><a name="107082">rc = SQLExecDirect (hstmt, "SELECT first_name, last_name, ssn, address, city, state, zip, ROWID </a></div> <div class="syntax_wide"><a name="107936"> </a> FROM emp", SQL_NTS);</div> <div class="syntax_wide"><a name="107083">// fetch data and probably "hide" ROWID from the user</a></div> <div class="syntax_wide"><a name="107084">...</a></div> <div class="syntax_wide"><a name="107085">rc = SQLExecDirect (hstmt, "UPDATE emp SET address = ? WHERE ROWID = ?",</a> SQL_NTS);</div> <div class="syntax_wide"><a name="107086">// fastest access to the data!</a></div> <div class="Body"><a name="107087">If your data source does not contain special pseudo-columns, the result set of </a>SQLSpecialColumns consists of columns of the optimal unique index on the specified table (if a unique index exists). Therefore, your application does not need to call SQLStatistics to find the smallest unique index.</div> <div class="Body"><a name="106056"> </a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de