Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it
Yes, we use SQLServerCallableStatement.java class on JPA to SQL Server and we got performance issue related with sp_sproc_columns.
Our most of user defined procedure name includes underscore wildcard(ex, up_procname_purpose..), so sp_sproc_columns use pattern search. If we can use @fUsePattern=0, the performance will be improve.
The default value of @fUsePattern is 1.
In general, there are 100,000 batch requests/secs on the customer's SQL Server, which have a significant performance impact.
Describe the preferred solution
I hope that it can be to use @fUsePattern parameter in this section on the SQLServerCallableStatement.java.
ThreePartName threePartName = ThreePartName.parse(procedureName);
StringBuilder metaQuery = new StringBuilder("exec sp_sproc_columns ");
if (null != threePartName.getDatabasePart()) {
metaQuery.append("@procedure_qualifier=");
metaQuery.append(threePartName.getDatabasePart());
metaQuery.append(", ");
}
if (null != threePartName.getOwnerPart()) {
metaQuery.append("@procedure_owner=");
metaQuery.append(threePartName.getOwnerPart());
metaQuery.append(", ");
}
if (null != threePartName.getProcedurePart()) {
// we should always have a procedure name part
metaQuery.append("@procedure_name=");
metaQuery.append(threePartName.getProcedurePart());
metaQuery.append(" , @ODBCVer=3");
Describe alternatives you've considered
Additional context
This is comparison result of io & time statistics according to use @fUsePattern paramter.
The result 234ms vs 141ms may not seems important by a difference of viewpoint.
However I hope you consider that the performance impact can be growing up if this type of requests are repeated.
EXEC SYS.SP_SPROC_COLUMNS @procedure_name ='testproc123456789123456789123456789_10143',
@procedure_owner ='dbo', @procedure_qualifier= 'testdb11', @fUsePattern=1
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 5, logical reads 1547, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysscalartypes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_datatype_info'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 83 ms.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 222 ms.
EXEC SYS.SP_SPROC_COLUMNS @procedure_name ='testproc123456789123456789123456789_10143',
@procedure_owner ='dbo', @procedure_qualifier= 'testdb11', @fUsePattern=0
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_datatype_info'. Scan count 0, logical reads 132, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysscalartypes'. Scan count 0, logical reads 132, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server Execution Times:
CPU time = 141 ms, elapsed time = 128 ms.
Reference Documentations/Specifications
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-sproc-columns-transact-sql?view=sql-server-2017
[ @fUsePattern = ] 'fUsePattern' Determines whether the underscore (_), percent (%), and bracket ([ ]) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.
Reference Implementation
Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it
Yes, we use SQLServerCallableStatement.java class on JPA to SQL Server and we got performance issue related with sp_sproc_columns.
Our most of user defined procedure name includes underscore wildcard(ex, up_procname_purpose..), so sp_sproc_columns use pattern search. If we can use @fUsePattern=0, the performance will be improve.
The default value of @fUsePattern is 1.
In general, there are 100,000 batch requests/secs on the customer's SQL Server, which have a significant performance impact.
Describe the preferred solution
I hope that it can be to use @fUsePattern parameter in this section on the SQLServerCallableStatement.java.
Describe alternatives you've considered
Additional context
This is comparison result of io & time statistics according to use @fUsePattern paramter.
The result 234ms vs 141ms may not seems important by a difference of viewpoint.
However I hope you consider that the performance impact can be growing up if this type of requests are repeated.
EXEC SYS.SP_SPROC_COLUMNS @procedure_name ='testproc123456789123456789123456789_10143',
@procedure_owner ='dbo', @procedure_qualifier= 'testdb11', @fUsePattern=1
EXEC SYS.SP_SPROC_COLUMNS @procedure_name ='testproc123456789123456789123456789_10143',
@procedure_owner ='dbo', @procedure_qualifier= 'testdb11', @fUsePattern=0
Reference Documentations/Specifications
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-sproc-columns-transact-sql?view=sql-server-2017
[ @fUsePattern = ] 'fUsePattern' Determines whether the underscore (_), percent (%), and bracket ([ ]) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.
Reference Implementation