My goal with this code is to calculate the processing time of the query string that's passed into the stored procedure. The query string is created by the user, a student taking a sql/database course. In a subsequent query, the string is compared against the key to determine if it produces the correct results for the assignment. As you'll see, it's VB.NET code (.NET 4.5 project) calling a C# method (.NET 3.5 project) that calls a stored procedure (SQL SERVER 2012).
My initial call from the main program project is here:
Dim executionTime As Integer = exercise.GetSqlTime(txtUserSQL, connectionString)
The method called is here:
public int GetSqlTime(string SqlString, string ConnectionString)//bookmark
{
int executionTime = 0;
SqlConnection con = new SqlConnection(ConnectionString);
using (SqlCommand cmd = new SqlCommand("QueryExecutionTime", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserSQLstring", SqlDbType.NVarChar).Value = SqlString;
cmd.Parameters.Add("@Duration", SqlDbType.Int);
cmd.Parameters["@Duration"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
executionTime = Convert.ToInt32(cmd.Parameters["@Duration"].Value);
}
return executionTime;
}
My stored procedure is here:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[QueryExecutionTime]
@UserSQLstring NvarChar(3000),
@Duration int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartTime datetime = GETDATE()
EXEC sp_executesql @UserSQLstring
DECLARE @EndTime datetime = GETDATE()
SET @Duration = DATEDIFF(MILLISECOND,@StartTime,@EndTime)
END
The result is 0 when the query string is correct. When the string is incorrect, it returns a value between 1.4 and 2.5 seconds
This is the first time I've used a stored procedure with an output parameter. I had no idea it would prove to be so challenging.
Am I overlooking a simple detail that would make this work correctly? I have already tried at least five or six examples here on Stack Overflow alone - along with several other websites. What you're seeing here is my latest iteration. If you need additional information, please let me know.
If you know of a better way of doing this, I'm open to any and all suggestions.
1条答案
按热度按时间osh3o9ms1#
您的结果可能是正确的。如果您的SQL SERVER版本是2008或更高版本,请使用SYSDATETIME()而不是GETDATE(),因为它具有更高的准确性(或分辨率)。否则,您可以尝试多次运行查询字符串,然后将结果除以运行次数。应放弃格式错误的查询字符串的执行时间。