SP_COLUMNS take longer in SQL Server 2005 SP3 compared to SQL Server 2000
Recently we encountered a case wherein one of the user application which internally calls sp_column stored procedure in SQL server. This application is taking longer time to execute in SQL server 2005 SP3 when compared to SQL server 2000. This application uses sp_columns stored procedure to enumerate the column names. We noticed that there is a significant difference in the execution of sp_columns on table with '_' and without '_' as part of table name.
For Example, the below statement
exec sp_columns N'doctab2',NULL,NULL,NULL
Runs Quicker than the statement
exec sp_columns N'doc_tab2',NULL,NULL,NULL
[Cause]
=====================================================================================================
Different query is used for the table names which are having wild card characters in their name, in the stored procedure sp_columns. The Query is selected based on the condition whether the table name(input parameter) is having wild card character or not(variable: fusepattern).
[Work Around]
=====================================================================================================
We do not have any other option to make the Stored procedure work the same way for the table with and without underscore. One alternative would be to use the stored procedure exec sp_columns_90 instead of sp_columns as we can pass on a parameter (fusepattern) which decides whether or not to use condition to select the query. This stored procedure is undocumented and should be used as a last resort.
https://msdn.microsoft.com/en-us/library/ms187961(SQL.90).aspx
[More Information]
=====================================================================================================
-- In our case exec sp_columns N'f[_]ui',NULL,NULL,NULL is taking around 550 Ms
-- Whereas all the other exec sp_columns on the tables without '_' are taking less than 10 Ms each.
We checked the stored procedure in detail. Below are the two queries being executed. based on if the escape character exist or not.
select TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
TABLE_OWNER = s_cov.TABLE_OWNER,
TABLE_NAME = s_cov.TABLE_NAME,
COLUMN_NAME = s_cov.COLUMN_NAME,
DATA_TYPE = s_cov.DATA_TYPE_28,
TYPE_NAME = s_cov.TYPE_NAME_28,
"PRECISION" = s_cov.PRECISION_28,
"LENGTH" = s_cov.LENGTH_28,
SCALE = s_cov.SCALE,
RADIX = s_cov.RADIX,
NULLABLE = s_cov.NULLABLE,
REMARKS = s_cov.REMARKS,
COLUMN_DEF = s_cov.COLUMN_DEF,
SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
IS_NULLABLE = s_cov.IS_NULLABLE,
SS_DATA_TYPE = s_cov.SS_DATA_TYPE
from
sys.spt_columns_odbc_view s_cov
where
is_cov.object_id = @table_id -- (2nd) (@table_name is null or o.name like @table_name)
-- (2nd) and (@table_owner is null or schema_name(o.schema_id) like @table_owner)
and (@column_name is null or s_cov.COLUMN_NAME = @column_name) -- (2nd) and (@column_name is NULL or c.name like @column_name) and s_cov.ODBCVER = @ODBCVer
order by 17
If there is no pattern observed.
And below is the query where is the pattern is observed.
select
TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
TABLE_OWNER = s_cov.TABLE_OWNER,
TABLE_NAME = s_cov.TABLE_NAME,
COLUMN_NAME = s_cov.COLUMN_NAME,
DATA_TYPE = s_cov.DATA_TYPE_28,
TYPE_NAME = s_cov.TYPE_NAME_28,
"PRECISION" = s_cov.PRECISION_28,
"LENGTH" = s_cov.LENGTH_28,
SCALE = s_cov.SCALE,
RADIX = s_cov.RADIX,
NULLABLE = s_cov.NULLABLE,
REMARKS = s_cov.REMARKS,
COLUMN_DEF = s_cov.COLUMN_DEF,
SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
IS_NULLABLE = s_cov.IS_NULLABLE,
SS_DATA_TYPE = s_cov.SS_DATA_TYPE
from
sys.spt_columns_odbc_view s_cov
where is_cov.ODBCVER = @ODBCVer and (@table_name is null or s_cov.TABLE_NAME like @table_name) and (@table_owner is null or schema_name(s_cov.SCHEMA_ID) like @table_owner) and
(@column_name is null or s_cov.COLUMN_NAME like @column_name)
order by 2, 3, 17
-- The Second query is used for the Underscore and is slow comparatively as it is using the like operator to filter the results which is costly.
-- In SQL server 2000 a different set of queries are used. Which gives almost same duration for tables with underscore and non underscore.
-- We Obtained the Execution plans for the Queries
-- We observed the main difference is seen with the tables : sysobjrdb and sysschobjs
-- These tables are having index scan when compare to the index seek for the good execution.
-- We cannot access these tables directly. We could only access them using the DAC connection.
-- Tried with the DAC connection to view the data.
-- We will also be able to execute the view. sys.spt_columns_odbc_view using DAC connection.
.
Explanation for the Workaround
--------------------------------
The Execution of the stored procedure sp_columns, for the table with wildcard and without wildcard character, varies based on a variable 'fusepattern'. If this variable is set to ‘0’ , then it would use the faster query and if it is ‘1’,then it uses the slower query. The value is set to 1 if we have the wildcard character as per the code of sp_columns as below.
if (@fUsePattern = 1) -- Does the user want it?
begin
if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('%', @column_name),0) = 0) and
(isnull(charindex('_', @column_name),0) = 0) and
(@table_id <> 0))
begin
print 'i am in the if block for wild char'
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
end
The reason why we have separate queries is because we may not get the Table_id if we have wildcard character as above. And so we use the slower query with like operators instead of faster query with ‘=’ operator.
One workaround for this would be to use the new stored procedure sp_columns_90. This stored procedure has new parameter to specify the fUsePattern value. We can specify the parameter of value ‘0’ so that it would always use the faster query irrespective of whether we have the wildcard character or not. The syntax would be as below.
exec sp_columns_90 N'doctab2','TOWERUAT',NULL,NULL,2,0
exec sp_columns_90 N'doc_tab2','TOWERUAT',NULL,NULL,2,0
Sandeep Dasam
Microsoft SQL Server
Reviewed by
Ouseph Devis
Technical Lead, Microsoft Sql Server