Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\design.07.3.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>Retrieving Data</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#106941">5 Designing ODBC Applications for Performance Optimization</a> : Retrieving Data</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106941">Retrieving Data</a></div> <div class="Body"><a name="106942">To retrieve data efficiently, return only the data that you need, and choose the most efficient </a>method of doing so. The guidelines in this section will help you optimize system performance when retrieving data with ODBC applications.</div> <div class="head_2"><a name="106943">Retrieving Long Data</a></div> <div class="Body"><a name="106944">Because retrieving long data across the network is slow and resource-intensive, </a>applications should not request long data (SQL_LONGVARCHAR, SQL_WLONGVARCHAR, and SQL_LONGVARBINARY data) unless it is necessary. </div> <div class="Body"><a name="108854">Most users do not want to see long data. If the user does need to see these result items, the </a>application can query the database again, specifying only long columns in the select list. This technique allows the average user to retrieve the result set without having to pay a high performance penalty for network traffic.</div> <div class="Body"><a name="106945">Although the best approach is to exclude long data from the select list, some applications do </a>not formulate the select list before sending the query to the ODBC driver (that is, some applications simply <span class="Syntax">SELECT * FROM </span><span class="EquationVariables">table_name</span><span class="Syntax"> ...</span>). If the select list contains long data, the driver must retrieve that data at fetch time even if the application does not bind the long data in the result set. When possible, use a technique that does not retrieve all columns of the table.</div> <div class="head_2"><a name="106946">Reducing the Size of Data Retrieved</a></div> <div class="Body"><a name="106947">Sometimes, long data must be retrieved. When this is the case, remember that most users </a>do not want to see 100 KB, or more, of text on the screen. </div> <div class="Body"><a name="108875">To reduce network traffic and improve performance, you can reduce the size of data being </a>retrieved to some manageable limit by calling SQLSetStmtAttr with the SQL_ATTR_MAX_LENGTH option.</div> <div class="Body"><a name="106948">Eliminating SQL_LONGVARCHAR, SQL_WLONGVARCHAR, and </a>SQL_LONGVARBINARY data from the result set is ideal for optimizing performance. </div> <div class="Body"><a name="106949">Many application developers mistakenly assume that if they call SQLGetData with a </a>container of size <span class="Emphasis">x</span> that the ODBC driver only retrieves <span class="Emphasis">x</span> bytes of information from the server. Because SQLGetData can be called multiple times for any one column, most drivers optimize their network use by retrieving long data in large chunks and then returning it to the user when requested. For example:</div> <div class="syntax_wide_first"><a name="106950">char CaseContainer[1000];</a></div> <div class="syntax_wide"><a name="107733">...</a></div> <div class="syntax_wide"><a name="107744">rc = SQLExecDirect (hstmt, "SELECT CaseHistory FROM Cases WHERE CaseNo = 71164", SQL_NTS);</a></div> <div class="syntax_wide"><a name="107745">...</a></div> <div class="syntax_wide"><a name="106954">rc = SQLFetch (hstmt);</a></div> <div class="syntax_wide"><a name="106955">rc = SQLGetData (hstmt, 1, CaseContainer,(SWORD) sizeof(CaseContainer), ...);</a></div> <div class="Body"><a name="106956">At this point, it is more likely that an ODBC driver will retrieve 64</a> KB of information from the server instead of 1 KB. In terms of network access, one 64-KB retrieval is less expensive than 64 retrievals of 1 KB. Unfortunately, the application may not call SQLGetData again; therefore, the first and only retrieval of CaseHistory would be slowed by the fact that 64 KB of data must be sent across the network.</div> <div class="Body"><a name="106957">Many ODBC drivers allow you to limit the amount of data retrieved across the network by </a>supporting the SQL_MAX_LENGTH attribute. This attribute allows the driver to communicate to the database server that only <span class="Emphasis">x</span> bytes of data are relevant to the client. The server responds by sending only the first <span class="Emphasis">x</span> bytes of data for all result columns. This optimization substantially reduces network traffic and improves client performance. The previous example returned only one row, but consider the case where 100 rows are returned in the result setâ??the performance improvement would be substantial.</div> <div class="head_2"><a name="106958">Using Bound Columns</a></div> <div class="Body"><a name="106959">Retrieving data through bound columns (SQLBindCol) instead of using SQLGetData </a>reduces the ODBC call load and improves performance.</div> <div class="Body"><a name="106960">Consider the following code fragment:</a></div> <div class="syntax_wide_first"><a name="106961">rc = SQLExecDirect (hstmt, "SELECT <20 columns> FROM Employees WHERE HireDate >= ?", SQL_NTS);</a></div> <div class="syntax_wide"><a name="106962">do {</a></div> <div class="syntax_wide"><a name="106963"> </a> rc = SQLFetch (hstmt);</div> <div class="syntax_wide"><a name="106964"> </a> // call SQLGetData 20 times</div> <div class="syntax_wide"><a name="106965">} while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO));</a></div> <div class="Body"><a name="106966">Suppose the query returns 90 result rows. In this case, 1891 ODBC calls are made (20 calls </a>to SQLGetData x 90 result rows + 91 calls to SQLFetch). </div> <div class="Body"><a name="106967">Consider the same scenario that uses SQLBindCol instead of SQLGetData:</a></div> <div class="syntax_wide_first"><a name="106968">rc = SQLExecDirect (hstmt, "SELECT <20 columns> FROM Employees WHERE HireDate >= ?", SQL_NTS);</a></div> <div class="syntax_wide"><a name="107758">// call SQLBindCol 20 times</a></div> <div class="syntax_wide"><a name="106970">do {</a></div> <div class="syntax_wide"><a name="106971">rc</a> = SQLFetch (hstmt);</div> <div class="syntax_wide"><a name="106972">} while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO));</a></div> <div class="Body"><a name="106973">The number of ODBC calls made is reduced from 1891 to 111 (20 calls to SQLBindCol + 91 </a>calls to SQLFetch). In addition to reducing the call load, many drivers optimize how SQLBindCol is used by binding result information directly from the database server into the userâ??s buffer. That is, instead of the driver retrieving information into a container and then copying that information to the userâ??s buffer, the driver simply requests the information from the server be placed directly into the userâ??s buffer.</div> <div class="head_2"><a name="106974">Using SQLExtendedFetch Instead of SQLFetch</a></div> <div class="Body"><a name="106975">Use SQLExtendedFetch to retrieve data instead of SQLFetch. The ODBC call load </a>decreases (resulting in better performance) and the code is less complex (resulting in more maintainable code).</div> <div class="Body"><a name="106976">Most ODBC drivers now support SQLExtendedFetch for forward only cursors; yet, most </a>ODBC applications use SQLFetch to retrieve data. Consider the examples in <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'design.07.3.html#106958', '');">â??Using Bound Columnsâ??</a></span>, this time using SQLExtendedFetch instead of SQLFetch:</div> <div class="syntax_wide_first"><a name="106977">rc = SQLSetStmtOption (hstmt, SQL_ROWSET_SIZE, 100);</a></div> <div class="syntax_wide"><a name="106978">// use arrays of 100 elements</a></div> <div class="syntax_wide"><a name="106979">rc = SQLExecDirect (hstmt, "SELECT <20 columns> FROM Employees WHERE HireDate >= ?", SQL_NTS);</a></div> <div class="syntax_wide"><a name="106980">// call SQLBindCol 1 time specifying row-wise binding</a></div> <div class="syntax_wide"><a name="106981">do {</a></div> <div class="syntax_wide"><a name="106982"> </a> rc = SQLExtendedFetch (hstmt, SQL_FETCH_NEXT, 0, &RowsFetched,RowStatus);</div> <div class="syntax_wide"><a name="106983">} while ((rc == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO));</a></div> <div class="Body"><a name="106984">Notice the improvement from the previous examples. The initial call load was 1891 ODBC </a>calls. By choosing ODBC calls carefully, the number of ODBC calls made by the application has now been reduced to 4 (1 SQLSetStmtOption + 1 SQLExecDirect + 1 SQLBindCol + 1 SQLExtendedFetch). In addition to reducing the call load, many ODBC drivers retrieve data from the server in arrays, further improving the performance by reducing network traffic.</div> <div class="Body"><a name="106985">For ODBC drivers that do not support SQLExtendedFetch, the application can enable </a>forward-only cursors using the ODBC cursor library:</div> <div class="syntax_first"><a name="109301">(rc=SQLSetConnectOption (hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_IF_NEEDED); </a></div> <div class="Body"><a name="109316">Although using the cursor library does not improve performance, it should not be </a>detrimental to application response time when using forward-only cursors (no logging is required). Furthermore, using the cursor library means that the application can always depend on SQLExtendedFetch being available. This simplifies the code because the application does not require two algorithms (one using SQLExtendedFetch and one using SQLFetch).</div> <div class="head_2"><a name="106986">Choosing the Right Data Type</a></div> <div class="Body"><a name="106987">Retrieving and sending certain data types can be expensive. When you are working with </a>data on a large scale, select the data type that can be processed most efficiently. For example, integer data is processed faster than floating-point data. Floating-point data is defined according to internal database-specific formats, usually in a compressed format. The data must be decompressed and converted into a different format so that it can be processed by the wire protocol.</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