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 as many NULL arguments as possible:
A driver processes this SQLTables call into SQL that looks like this:
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 our example, the application provides scant information about the object for which 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.
It may not be obvious to the end user 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:
SELECT ... FROM SysTables WHERE TableName = 'Customers' AND Owner = 'Beth'
