I am using Sql Server 2008. My Stored Procedure accepts almost 150 parameters. Is there anything wrong with that performance-wise?
我正在使用Sql Server 2008.我的存储过程接受近150个参数。这种性能方面有什么问题吗?
6 个解决方案
#1
Nothing wrong performance wise but it smells as something that could be better done with dynamic SQL. Hard to tell without seeing the code.
没有什么错误的性能明智,但它闻起来可以用动态SQL做得更好。没有看到代码很难说。
#2
when you are using SQL Server 2008 you can use the new Table parameter. If the parameters are the same, you can easily use the table parameter.
当您使用SQL Server 2008时,您可以使用新的Table参数。如果参数相同,则可以轻松使用table参数。
Here is link to the MSDN. Here is another link, a bit more explained
这是MSDN的链接。这是另一个链接,更多解释
Enjoy.
#3
Maybe it is not a problem from a performance perspective. But from a maintenance perspective.
从性能角度来看,这可能不是问题。但从维护的角度来看。
You could consider sending the data as a single xml parameter. For details see:
您可以考虑将数据作为单个xml参数发送。详情见:
#4
the max is 1,024, but i think 150 is little over
最大值是1,024,但我认为150稍微超过了
#5
Regulars in the SQL Server newsgroups will be familiar with the many quarrels between Joe Celko and Tony Rogerson and one of them is on the very subject of whether it is a good idea to use a stored procedure with a large number of parameters.
SQL Server新闻组中的规则将熟悉Joe Celko和Tony Rogerson之间的许多争吵,其中一个问题就是使用具有大量参数的存储过程是否是一个好主意。
Because the question is specifically about performance, here's Tony side of the argument:
因为这个问题是关于性能的,所以这里是Tony的论点:
Don't use CSV/XML - use 1,000 Parameters instead!
不要使用CSV / XML - 请改用1,000参数!
Tony Rogerson is a fellow Brit (US = limey) and the title is ironic (US = NULL).
Tony Rogerson是英国同胞(US = limey),头衔具有讽刺意味(US = NULL)。
#6
You should definitely identify the reasons for all the parameters.
你应该明确确定所有参数的原因。
- Are they values to be inserted or updated into a table? Then you may be better off using a table-valued parameter for those values.
- Are they complex selection criteria? Then I suspect you should use an XML parameter to pass the selection criteria; otherwise you might re-evaluate the frequency with which the various criteria are actually used. You may find a simpler SP would meet your needs in 80% of the cases.
它们是要插入或更新到表中的值吗?那么你可能最好为这些值使用表值参数。
它们是复杂的选择标准吗?然后我怀疑你应该使用XML参数来传递选择标准;否则您可能会重新评估实际使用各种标准的频率。在80%的情况下,您可能会发现更简单的SP可以满足您的需求。
#1
Nothing wrong performance wise but it smells as something that could be better done with dynamic SQL. Hard to tell without seeing the code.
没有什么错误的性能明智,但它闻起来可以用动态SQL做得更好。没有看到代码很难说。
#2
when you are using SQL Server 2008 you can use the new Table parameter. If the parameters are the same, you can easily use the table parameter.
当您使用SQL Server 2008时,您可以使用新的Table参数。如果参数相同,则可以轻松使用table参数。
Here is link to the MSDN. Here is another link, a bit more explained
这是MSDN的链接。这是另一个链接,更多解释
Enjoy.
#3
Maybe it is not a problem from a performance perspective. But from a maintenance perspective.
从性能角度来看,这可能不是问题。但从维护的角度来看。
You could consider sending the data as a single xml parameter. For details see:
您可以考虑将数据作为单个xml参数发送。详情见:
#4
the max is 1,024, but i think 150 is little over
最大值是1,024,但我认为150稍微超过了
#5
Regulars in the SQL Server newsgroups will be familiar with the many quarrels between Joe Celko and Tony Rogerson and one of them is on the very subject of whether it is a good idea to use a stored procedure with a large number of parameters.
SQL Server新闻组中的规则将熟悉Joe Celko和Tony Rogerson之间的许多争吵,其中一个问题就是使用具有大量参数的存储过程是否是一个好主意。
Because the question is specifically about performance, here's Tony side of the argument:
因为这个问题是关于性能的,所以这里是Tony的论点:
Don't use CSV/XML - use 1,000 Parameters instead!
不要使用CSV / XML - 请改用1,000参数!
Tony Rogerson is a fellow Brit (US = limey) and the title is ironic (US = NULL).
Tony Rogerson是英国同胞(US = limey),头衔具有讽刺意味(US = NULL)。
#6
You should definitely identify the reasons for all the parameters.
你应该明确确定所有参数的原因。
- Are they values to be inserted or updated into a table? Then you may be better off using a table-valued parameter for those values.
- Are they complex selection criteria? Then I suspect you should use an XML parameter to pass the selection criteria; otherwise you might re-evaluate the frequency with which the various criteria are actually used. You may find a simpler SP would meet your needs in 80% of the cases.
它们是要插入或更新到表中的值吗?那么你可能最好为这些值使用表值参数。
它们是复杂的选择标准吗?然后我怀疑你应该使用XML参数来传递选择标准;否则您可能会重新评估实际使用各种标准的频率。在80%的情况下,您可能会发现更简单的SP可以满足您的需求。