Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\design.07.2.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>Using Catalog 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#108333">5 Designing ODBC Applications for Performance Optimization</a> : Using Catalog Functions</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="108333">Using Catalog Functions</a></div> <div class="Body"><a name="106872">Because catalog functions, such as those listed here, are slow compared to other ODBC </a>functions, their frequent use can impair system performance:</div> <table class="Format_B" cellspacing="0" summary=""> <caption></caption> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106875">SQLColumns</a></div> </td> </tr> </table> </div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106877">SQLProcedureColumns</a></div> </td> </tr> </table> </div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106879">SQLColumnPrivileges</a></div> </td> </tr> </table> </div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106881">SQLSpecialColumns</a></div> </td> </tr> </table> </div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106883">SQLForeignKeys</a></div> </td> </tr> </table> </div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106885">SQLStatistics</a></div> </td> </tr> </table> </div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106887">SQLGetTypeInfo</a></div> </td> </tr> </table> </div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106889">SQLTables</a></div> </td> </tr> </table> </div> </td> </tr> <tr style="vertical-align: top;"> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="list_bulleted_table_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_table_inner" style="width: 14.4pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_table_inner"><a name="106891">SQLProcedures</a></div> </td> </tr> </table> </div> </td> <td style="padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="Body"><a name="106893"> </a></div> </td> </tr> </table> <div class="Body"><a name="106894">SQLGetTypeInfo is included in this list of expensive ODBC functions because many drivers </a>must query the server to obtain accurate information about which types are supported (for example, to find dynamic types such as user defined types). </div> <div class="head_2"><a name="106895">Caching Information to Minimize the Use of Catalog </a>Functions</div> <div class="Body"><a name="109110">To return all result column information mandated</a><span style="font-style: italic;"> </span>by the ODBC specification, a driver may have to perform multiple queries, joins, subqueries, or unions to return the required result set for a single call to a catalog function. These particular elements of the SQL language are performance expensive. </div> <div class="Body"><a name="106896">Although it is almost impossible to write an ODBC application without catalog functions, </a>their use should be minimized. By caching information, applications can avoid multiple executions. </div> <div class="Body"><a name="106898">For example, call SQLGetTypeInfo once in the application and cache the elements of the </a>result set that your application depends on. It is unlikely that any application uses all elements of the result set generated by a catalog function, so the cached information should not be difficult to maintain.</div> <div class="head_2"><a name="106899">Avoiding Search Patterns</a></div> <div class="Body"><a name="106900">Passing NULL arguments or search patterns to catalog functions generates </a>time-consuming queries. In addition, network traffic potentially increases because of unwanted results. Always supply as many non-NULL arguments to catalog functions as possible. Because catalog functions are slow, applications should invoke them efficiently. Any information that the application can send the driver when calling catalog functions can result in improved performance and reliability. </div> <div class="Body"><a name="106901">For example, consider a call to SQLTables where the application requests information </a>about the table "Customers." Often, this call is coded as shown, using as many NULL arguments as possible:</div> <div class="syntax_first"><a name="106902">rc = SQLTables (hstmt, NULL, 0, NULL, 0, "Customers", SQL_NTS, NULL, 0);</a></div> <div class="Body"><a name="106903">A driver processes this SQLTables call into SQL that looks like this:</a></div> <div class="syntax_first"><a name="107686">SELECT ...</a> FROM SysTables WHERE TableName = â??Customersâ??</div> <div class="syntax"><a name="107975">UNION ALL</a></div> <div class="syntax"><a name="107687">SELECT ... FROM SysViews WHERE ViewName = â??Customersâ??</a></div> <div class="syntax"><a name="107976">UNION ALL</a></div> <div class="syntax"><a name="106906">SELECT ... FROM SysSynonyms WHERE SynName = â??Customersâ?? ORDER BY ...</a></div> <div class="Body"><a name="106907">In our example, the application provides scant information about the object for which </a>information was requested. Suppose three "Customers" tables were returned in the result set: the first table owned by the user named Beth, the second owned by the sales department, and the third a view created by management. </div> <div class="Body"><a name="106908">It may not be obvious to the end user</a><span style="font-style: italic;"> </span>which table to choose. If the application had specified the OwnerName argument in the SQLTables call, only one table would be returned and performance would improve. Less network traffic would be required to return only one result row and unwanted rows would be filtered by the database. In addition, if the TableType argument was supplied, the SQL sent to the server can be optimized from a three-query union into a single Select statement as shown:</div> <div class="syntax_first"><a name="106909">SELECT ... FROM SysTables WHERE TableName = 'Customers' AND Owner = 'Beth'</a></div> <div class="head_2"><a name="106910">Using a Dummy Query to Determine Table Characteristics</a></div> <div class="Body"><a name="106911">Avoid using SQLColumns to determine characteristics about a table. Instead, use a dummy </a>query with SQLDescribeCol.</div> <div class="Body"><a name="106912">Consider an application that allows the user to choose the columns that will be selected. </a>Should the application use SQLColumns to return information about the columns to the user or prepare a dummy query and call SQLDescribeCol?</div> <div class="hanging_label_head"><a name="106913">Case 1: SQLColumns Method</a></div> <div class="syntax_first"><a name="106914">rc = SQLColumns (... "UnknownTable" ...);</a></div> <div class="syntax"><a name="106915">// This call to SQLColumns will generate a query to the system catalogs... </a></div> <div class="syntax"><a name="106916">// possibly a join which must be prepared, executed, and produce a result set</a></div> <div class="syntax"><a name="106918">rc = SQLBindCol (...);</a></div> <div class="syntax"><a name="106919">rc = SQLExtendedFetch (...);</a></div> <div class="syntax"><a name="106920">// user must retrieve N rows from the server </a></div> <div class="syntax"><a name="106921">// N = # result columns of UnknownTable</a></div> <div class="syntax"><a name="106922">// result column information has now been obtained</a></div> <div class="hanging_label_head"><a name="106923">Case 2: SQLDescribeCol Method</a></div> <div class="syntax_first"><a name="106924">// prepare dummy query </a></div> <div class="syntax"><a name="106925">rc = SQLPrepare (... "SELECT * FROM UnknownTable WHERE 1 = 0" ...);</a></div> <div class="syntax"><a name="106927">// query is never executed on the server - only prepared</a></div> <div class="syntax"><a name="106928">rc = SQLNumResultCols (...);</a></div> <div class="syntax"><a name="107716">for (irow = 1; irow <= NumColumns; irow++) {</a></div> <div class="syntax"><a name="107717"> </a> rc = SQLDescribeCol (...)</div> <div class="syntax"><a name="107718"> </a> // + optional calls to SQLColAttributes</div> <div class="syntax"><a name="106932"> </a> }</div> <div class="syntax"><a name="106933">// result column information has now been obtained</a></div> <div class="syntax"><a name="106934">// Note we also know the column ordering within the table! </a></div> <div class="syntax"><a name="108751">// This information cannot be assumed from the SQLColumns example.</a></div> <div class="Body"><a name="106937">In both cases, a query is sent to the server, but in Case 1, the query must be evaluated and </a>form a result set that must be sent to the client. Clearly, Case 2 is the better performing model. </div> <div class="Body"><a name="106938">To complicate this discussion, let us consider a database server that does not natively </a>support preparing a SQL statement. The performance of Case 1 does not change, but the performance of Case 2 improves slightly because the dummy query is evaluated before being prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. Again, for this situation, Case 2 outperforms Case 1.</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