如何优化.net代码中对普通存储过程的多次调用? [英] How should I optimize multiple calls in my .net code to a trivial stored procedure?

问题描述

我有一个非常简单的存储过程:

I've got a very simple stored procedure :

create procedure spFoo(v varchar(50))
as 
insert into tbFoo select v

我有50个值要插入到tbFoo中，这意味着在我的C#代码中，我调用spFoo 50次.这是一种非常低效的方法，尤其是在我的程序和数据库之间存在一些滞后的情况下.

I've got 50 values to insert into tbFoo, which means in my c# code I call spFoo 50 times. This is a pretty inefficient way of doing this, especially if there's some lag between my program and the database.

在这种情况下，您通常做什么?

What do you usually do in this situation ?

我正在使用SQL Server 2008，但这可能无关.

I'm using SQL Server 2008 but it's probably unrelated.

推荐答案

如果您的问题是试图传递多个行，那么从SQL Server 2008开始，您将拥有一个新的参数类型

If your problem is multiple rows trying to be passed in then as of SQL Server 2008 you have a new parameter type Table-Valued. Which allows you to pass a .Net Datatable directly into a stored procedure through a .NET SQLParamter of Type Structured.

tvpParam.SqlDbType = SqlDbType.Structured

但是，如果问题是您要填充的1行中有50列，则最好将它们全部作为单独的参数传递并更改过程"，而不是尝试使用代码或T- SQL.

However if the problem is that there are 50 columns in 1 row that you are trying to populate then you would be better passing them all in as separate parameters and change the Procedure rather than trying to get slick with either code or T-SQL.

有一个很好的文章演示如何在SQL Server中以及通过C#和VB.Net中的.NET使用表值参数. 希望这会有所帮助.

There's a good article that demonstrates how to use table valued parameters in SQL Server and through .NET in both C# and VB.Net. Hope this helps.

这篇关于如何优化.net代码中对普通存储过程的多次调用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！