How do I time the execution of an SQL Query in C#?

Question

I'm trying to just find out the execution time of my SQL Server query using c#. I thought a timer would work; however I'm new to c# and have been having trouble figuring it out. I've searched through several questions on this site, and on other sites trying to find out how to time the execution of my query.

Here is my code:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        Console.WriteLine("Executing query...");
        string customerID = "ID_HERE";
        using (SqlConnection connection = new SqlConnection("CONNECTION_STRING"))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(
                "SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9 FROM Table_name WHERE col1 LIKE @ID", connection))
            {

                command.Parameters.Add(new SqlParameter("ID", customerID));

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int col0 = reader.GetInt32(0);
                    int col1 = reader.GetInt32(1);
                    string col2 = reader.GetString(2);
                    string col3 = reader.GetString(3);
                    int col4 = reader.GetInt32(4);
                    int col5 = reader.GetInt32(5);
                    short col6 = reader.GetInt16(6);
                    string col7 = reader.GetString(7);
                    string col8 = reader.GetString(8);
                    int col9 = reader.GetInt32(9);
                    Console.WriteLine("col0 = {0}, col1 = {1}, col2 = {2}, col3 = {3}, col4 = {4}, col5 = {5}, col6 = {6}, col7 = {7}, col8 = {8}, col9 = {9}",
                        col0,
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8,
                        col9
                        );
                }
            }
        }
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }

}

I'm not sure how to add a timer to this that only times the execution of the query, or even where to put it. I've tried finding other posts about it but none of the ones I've found are similar to this; I could also just be really bad at my searches. Any kind of help would be appreciated, and if you need any more information let me know. I renamed a few things for security, but this is how the code is otherwise. To note: I'm using this for testing purposes, and not for production, so only a few people will ever actually see this but it's necessary.

If you want to time the actual query you should do it on the sql server. Move your query to a stored procedure and capture the current system time before and after execution of your query. — Sean Lange, Sep 18 '18 at 20:29

score 2 · Answer 1 · answered Sep 18 '18 at 20:29

What are you looking for is StopWatch, this is an example:

var watch = System.Diagnostics.Stopwatch.StartNew();
// Run your query
watch.Stop();
//This is the time it took in miliseconds
var elapsedTime = watch.ElapsedMilliseconds;

Check this question for why you shouldn't use DateTime.

score 0 · Answer 2 · answered Sep 18 '18 at 20:23

0

I guess if you get the server time (DateTime.Now) before and after the SQL execution and find the difference that would give you the time elapsed.

answered Sep 18 '18 at 20:23

Sandhya

606
1
7
13

2

No, the DateTime type doesnt have very good resolution. A Stopwatch would be more appropriate – Ňɏssa Pøngjǣrdenlarp Sep 18 '18 at 20:24
Stopwatch was exactly what I was looking for. I'm new to c# so I didn't know about that until now, thank you! – Croy07 Sep 18 '18 at 20:32

score 0 · Answer 3 · answered Sep 18 '18 at 20:25

I was wanting to accomplish the same thing. I've seen StopWatch() but was never able to get it to work. So I just rigged my own:

TimeSpan ts = DateTime.Now.TimeOfDay;
Debug.Print("\n\nProcess Started---- " + processName + " ----  " + ts + "\n\n");
/*
 * code here
 */
TimeSpan fts = DateTime.Now.TimeOfDay;
Debug.Print("\n\nProcess Ended---- " + processName + " ----  " + fts + "\n");
Debug.Print("Time Elapsed----  " + (fts - ts) + " \n\n");

It's probably not the fastest or cleanest. But it tells me what I want to know. You will need the using System.Diagnostics; statement as well in order to print to the debug window.

Note: if you're able to get StopWatch() to work for you, then I would definitely recommend that over this. This was just my solution for my code.

Stopwatch was what I needed to do. After seeing it mentioned here, as I'm fairly new to c#, I did a little bit more digging into it. This site is what ended up helping me get it working: https://www.dotnetperls.com/stopwatch I hope this helps you out, too! — Croy07, Sep 18 '18 at 20:34

score 0 · Accepted Answer · answered Sep 18 '18 at 20:32

The SQL Query starts executing at SqlCommand.ExecuteReader(), and it's finished executing after the SqlDataReader.Read() returns false. Note that if the SQL Server is across a slow network or there are a large number of results, this won't accurately measure the time spent waiting on the SQL Server.

So

using System;
using System.Data.SqlClient;
using System.Diagnostics;

class Program
{
    static void Main()
    {
        Console.WriteLine("Executing query...");
        string customerID = "ID_HERE";
        using (SqlConnection connection = new SqlConnection("CONNECTION_STRING"))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(
                "SELECT col0, col1, col2, col3, col4, col5, col6, col7, col8, col9 FROM Table_name WHERE col1 LIKE @ID", connection))
            {

                command.Parameters.Add(new SqlParameter("ID", customerID));
                var sw = new Stopwatch();

                sw.Start();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int col0 = reader.GetInt32(0);
                    int col1 = reader.GetInt32(1);
                    string col2 = reader.GetString(2);
                    string col3 = reader.GetString(3);
                    int col4 = reader.GetInt32(4);
                    int col5 = reader.GetInt32(5);
                    short col6 = reader.GetInt16(6);
                    string col7 = reader.GetString(7);
                    string col8 = reader.GetString(8);
                    int col9 = reader.GetInt32(9);
                    Console.WriteLine("col0 = {0}, col1 = {1}, col2 = {2}, col3 = {3}, col4 = {4}, col5 = {5}, col6 = {6}, col7 = {7}, col8 = {8}, col9 = {9}",
                        col0,
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8,
                        col9
                        );
                }
                var elapsed = sw.Elapsed;

                Console.WriteLine($"Query Executed and Results Returned in {elapsed.Seconds}sec");
            }
        }
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }

}

I think this is helpful but the timings are going to include the time `Console.WriteLine` spends running, too. The actual query might be fast relative to the time spent sending text to the screen. — ta.speot.is, Sep 28 '18 at 03:20

score 0 · Answer 5 · edited Sep 28 '18 at 03:11

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME

SET     @Time1 = GETDATE()

-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATE DIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

this code allows you to show exact time of execution of a specific code segment in your sql query. Place the code that you would like to get the execution time for in the center of the below script. The exact time will be shown in the results.

How do I time the execution of an SQL Query in C#?

5 Answers5

Linked