Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\design.07.4.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>Selecting ODBC Functions</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#106991">5 Designing ODBC Applications for Performance Optimization</a> : Selecting ODBC Functions</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106991">Selecting ODBC Functions</a></div> <div class="Body"><a name="106992">The guidelines in this section will help you select which ODBC functions will give you the </a>best performance.</div> <div class="head_2"><a name="106993">Using SQLPrepare/SQLExecute and SQLExecDirect</a></div> <div class="Body"><a name="106994">Using SQLPrepare/SQLExecute is not always as efficient as SQLExecDirect. Use </a>SQLExecDirect for queries that will be executed once and SQLPrepare/SQLExecute for queries that will be executed multiple times.</div> <div class="Body"><a name="106995">ODBC drivers are optimized based on the perceived use of the functions that are being </a>executed. SQLPrepare/SQLExecute is optimized for multiple executions of statements that use parameter markers. SQLExecDirect is optimized for a single execution of a SQL statement. Unfortunately, more than 75% of all ODBC applications use SQLPrepare/SQLExecute exclusively.</div> <div class="Body"><a name="106996">Consider the case where an ODBC driver implements SQLPrepare by creating a stored </a>procedure on the server that contains the prepared statement. Creating stored procedures involve substantial overhead, but the statement can be executed multiple times. Although creating stored procedures is performance-expensive, execution is minimal because the query is parsed and optimization paths are stored at create procedure time. </div> <div class="Body"><a name="106997">Using SQLPrepare/SQLExecute for a statement that is executed only once results in </a>unnecessary overhead. Furthermore, applications that use SQLPrepare/SQLExecute for large single execution query batches exhibit poor performance. Similarly, applications that always use SQLExecDirect do not perform as well as those that use a logical combination of SQLPrepare/SQLExecute and SQLExecDirect sequences.</div> <div class="head_2"><a name="106999">Using Arrays of Parameters</a></div> <div class="Body"><a name="107000">Passing arrays of parameter values for bulk insert operations, for example, with </a>SQLPrepare/SQLExecute and SQLExecDirect can reduce the ODBC call load and network traffic. To use arrays of parameters, the application calls SQLSetStmtAttr with the following attribute arguments:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="107001">SQL_ATTR_PARAMSET_SIZE sets the array size of the parameter.</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="107002">SQL_ATTR_PARAMS_PROCESSED_PTR assigns a variable filled by SQLExecute, </a>which contains the number of rows that are actually inserted.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="107003"> </a>SQL_ATTR_PARAM_STATUS_PTR points to an array in which status information for each row of parameter values is returned.</div> </td> </tr> </table> </div> <div class="Body"><a name="107004">NOTE: ODBC 3.</a><span class="Emphasis">x</span> replaced the ODBC 2.<span class="Emphasis">x</span> call to SQLParamOptions with calls to SQLSetStmtAttr using the SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_PARAMS_PROCESSED_ARRAY, and SQL_ATTR_PARAM_STATUS_PTR arguments.</div> <div class="Body"><a name="107005">Before executing the statement, the application sets the value of each data element in the </a>bound array. When the statement is executed, the driver tries to process the entire array contents using one network roundtrip. For example, let us compare the following examples, Case 1 and Case 2.</div> <div class="hanging_label_head"><a name="107006">Case 1: Executing Prepared Statement Multiple Times </a></div> <div class="syntax_wide_first"><a name="107007">rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...) VALUES (?,?,...)", SQL_NTS);</a></div> <div class="syntax_wide"><a name="107008">// bind parameters</a></div> <div class="syntax_wide"><a name="107009">...</a></div> <div class="syntax_wide"><a name="107010">do {</a></div> <div class="syntax_wide"><a name="107011"> </a> // read ledger values into bound parameter buffers</div> <div class="syntax_wide"><a name="107012"> </a> ...</div> <div class="syntax_wide"><a name="107013"> </a> rc = SQLExecute (hstmt);</div> <div class="syntax_wide"><a name="107014"> </a> // insert row</div> <div class="syntax_wide"><a name="107015">} while ! (eof);</a></div> <div class="hanging_label_head"><a name="107016">Case 2: Using Arrays of Parameters </a></div> <div class="syntax_wide_first"><a name="107017">SQLPrepare (hstmt, " INSERT INTO DailyLedger (...) VALUES (?,?,...)", SQL_NTS);</a></div> <div class="syntax_wide"><a name="107019">SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMSET_SIZE, (UDWORD)100, SQL_IS_UINTEGER);</a></div> <div class="syntax_wide"><a name="107021">SQLSetStmtAttr (hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &rows_processed, SQL_IS_POINTER);</a></div> <div class="syntax_wide"><a name="107023">// Specify an array in which to return the status of </a></div> <div class="syntax_wide"><a name="107024">// each set of parameters.</a></div> <div class="syntax_wide"><a name="107025">SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, SQL_IS_POINTER);</a></div> <div class="syntax_wide"><a name="107027">// pass 100 parameters per execute</a></div> <div class="syntax_wide"><a name="107028">// bind parameters</a></div> <div class="syntax_wide"><a name="107029">...</a></div> <div class="syntax_wide"><a name="107030">do {</a></div> <div class="syntax_wide"><a name="107031"> </a> // read up to 100 ledger values into </div> <div class="syntax_wide"><a name="107032"> </a> // bound parameter buffers</div> <div class="syntax_wide"><a name="107033"> </a> ...</div> <div class="syntax_wide"><a name="107034"> </a> rc = SQLExecute (hstmt);</div> <div class="syntax_wide"><a name="107035"> </a> // insert a group of 100 rows</div> <div class="syntax_wide"><a name="107036">} while ! (eof);</a></div> <div class="Body"><a name="107037">In Case 1, if there are 100 rows to insert, 101 network roundtrips are required to the server, </a>one to prepare the statement with SQLPrepare and 100 additional roundtrips for each time SQLExecute is called. </div> <div class="Body"><a name="107038">In Case 2, the call load has been reduced from 100 SQLExecute calls to only 1 </a>SQLExecute call. Furthermore, network traffic is reduced considerably.</div> <div class="head_2"><a name="107039">Using the Cursor Library</a></div> <div class="Body"><a name="107040">If the driver provides scrollable cursors, do not use the cursor library. The cursor library </a>creates local temporary log files, which are performance-expensive to generate and provide worse performance than native scrollable cursors.</div> <div class="Body"><a name="107041">The cursor library adds support for static cursors, which simplifies the coding of applications </a>that use scrollable cursors. However, the cursor library creates temporary log files on the userâ??s local disk drive to accomplish the task. Typically, disk I/O is a slow operation. Although the cursor library is beneficial, applications should not automatically choose to use the cursor library when an ODBC driver supports scrollable cursors natively.</div> <div class="Body"><a name="107042">Typically, ODBC drivers that support scrollable cursors achieve high performance by </a>requesting that the database server produce a scrollable result set instead of emulating the capability by creating log files. Many applications use:</div> <div class="syntax_first"><a name="107043">rc = SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_ODBC);</a></div> <div class="Body"><a name="107045">but should use:</a></div> <div class="syntax_first"><a name="107046">rc = SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_IF_NEEDED);</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