Passing null arguments or search patterns to catalog functions generates 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.
For example, consider a call to SQLTables where the application requests information about the table “Customers.” Often, this call is coded as shown, using the fewest non-null arguments necessary for the function to return success:
A driver may process this SQLTables call into SQL as shown:
SELECT ... FROM SysTables WHERE TableName = 'Customers' UNION ALL
SELECT ... FROM SysViews WHERE ViewName = 'Customers' UNION ALL
SELECT ... FROM SysSynonyms WHERE SynName = 'Customers' ORDER BY ...
In this example, the application provided little information about the object for which information was requested. Suppose three “Customers” tables were returned in the result set:
• The first table was owned by the user.
• The second table was owned by the sales department.
• The third table was a view created by management.
It may not be obvious to the userwhich table to choose. If the application had specified the OwnerName argument for 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 can be supplied, the SQL sent to the server can be optimized from a three-query union to a single Select statement as shown:
