Stored Procedures not that fast?

Question

Hoho there.

I was just trying to enhance the performance of my application (.NET 3.5, C#) with the use of stored procedures.

So I wrote a little test-app to see how much faster these are in comparison to normal queries which looks like this:

    private static long _StartStored;
    private static long _EndStored;
    private static long _StartNormal;
    private static long _EndNormal;
    private static TimeSpan _StoredTime;
    private static TimeSpan _NormalTime;
    private static string[] _Stored = new string[102000];
    private static string[] _Normal = new string[102000];
    static void Main(string[] args)
    {
        Console.WriteLine("Querying 2000 normal queries");
        _SQLConnection = new SqlConnection(/*my_connection*/);
        _SQLConnection.Open();
        _StartNormal = DateTime.Now.Ticks;
        for (int i = 100000; i <= 102000; i++)
        {
            DataTable _ResultDataTable = new DataTable();
            SqlDataAdapter _SQLAdapter = new SqlDataAdapter(/*my_query*/, _SQLConnection);
            _SQLAdapter.Fill(_ResultDataTable);
            if (_ResultDataTable.Rows.Count > 0)
                _Normal[i] = _ResultDataTable.Rows[0]["row"].ToString();
        }
        _EndNormal = DateTime.Now.Ticks;
        _NormalTime = TimeSpan.FromTicks(_EndNormal - _StartNormal);
        Console.WriteLine("Total execution time: " + _NormalTime.ToString());
        //-----------------------------------------------------------------------------
        Console.WriteLine("Querying 2000 stored procedures");
        _StartStored = DateTime.Now.Ticks;
        SqlCommand _Cmd = new SqlCommand(/*my_sp*/, _SQLConnection);
        _Cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter _Param = new SqlParameter("@param1", 0);
        _Cmd.Parameters.Add(_Param);
        for (int i = 100000; i <= 102000; i++)
        {
            _Cmd.Parameters["@param1"].Value = i;
            SqlDataReader _Reader = _Cmd.ExecuteReader();
            while (_Reader.Read())
            {
                _Stored[i] = _Reader["StartWork"].ToString();
            }
            _Reader.Close();
        }
        _EndStored = DateTime.Now.Ticks;
        _StoredTime = TimeSpan.FromTicks(_EndStored - _StartStored);
        Console.WriteLine("Total execution time: " + _StoredTime.ToString());

I'd love to shorten that code, but well... doesn't work :D

TL;DR - 2000 stored procedures of the same query are only about 4 seconds faster, which seems quite low to me?

Am I using the stored procedures wrong?

Answer 1

Most of the overhead will be setting up the stored procedure call and retrieving the result -- which in your example is done 2,000 times.

You may want to consider moving the loop into the stored procedure, then calling the stored procedure once and getting all the results in one go.

Answer 2

With the new versions of SQL Server the differences between SP and normal code are very very tiny, SQL Server optimize the exceution of any query expecially if command doesn't change.

Answer 3

One BIG trip to the db is almost always faster than the same trip done in a loop.

You will greatly enhance the performance if you were to do tyhe loop in the db itself.

Answer 4

The responses to this question might help: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder

Answer 5

You might find this Q&A useful: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder

Answer 6

I agree with Jeremy. One of the better reasons for using a stored procedures is to avoid circumstances where you call the database an unpredictable number of times within a loop. You should move the loop to the stored procedure itself and call it once. Therefore, your example shows a poor usage of stored procedures. A bit like racing a tractor and a Ferrari across a ploughed field, and then claiming that the Ferrari is slow.

Answer 7

You should always use parameterized queries and as such you might not see any speed difference at all.