markneustadt.com

Performance – Inline SQL vs Parameterized Queries

In the search for better and better performance, there are many techniques developers can use.  A technique used early on by some entry level or “still learning” developers is to build inline SQL.  That looks something like this:

string name = "Mark";
string query = "SELECT * FROM users WHERE FirstName='" + name + "'";
OleDbCommand cmd = new OleDbCommand(query);
OleDbDataReader reader = cmd.ExecuteReader();

There are steps missing… this is just example code

 

This is something more seasoned developers learn to avoid almost immediately.  There’s a variety of reasons why inline SQL is bad.  The most important reason is security.

However, there’s another reason to avoid it.  Performance.

Using a parameterized query much more closely replicates the behavior of a stored procedure.  Instead of building up the the SQL into a string variable, you simply have placeholders for the values you are changing.

string name = "Mark";
string query = "SELECT * FROM users WHERE FirstName=@name";
OleDbCommand cmd = new OleDbCommand(query);
cmd.Parameters.Add("@name",OleDbType.VarChar).Value = name;
OleDBDataReader reader = cmd.ExecuteReader();

Again… there are steps missing… this is just example code

Not only is it easier to read, it’s more secure as it makes SQL Injection Attacks much more unlikely.  Then there’s the additional advantage of boosting performance.  But you have to do it on purpose.  Simply using a parameterized query will not do the trick.

You have to “prepare” your statement.  The steps are to open your connection, declare your command variable with the command text and then call Prepare() on it.

Here’s the full text of the routine that has the prepared query:

        private static void QueryParameterizedPrepared()
        {
            int stateNumber = 0;
            Random random = new Random();
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\SampleData.accdb");
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM users WHERE state=@state");

            // open the connection and prepare the command
            conn.Open();
            cmd.Connection = conn;
            cmd.Prepare();

            for (int i = 0; i < 10000; i++)
            {
                // get a random state abbreviation
                stateNumber = random.Next(0, 50);

                // clear the parameters collection from the last time this was run
                cmd.Parameters.Clear();

                // add the parameter with the random state
                cmd.Parameters.Add("@state", OleDbType.VarChar).Value = states[stateNumber];

                // execute the command and get a reader
                OleDbDataReader dr = cmd.ExecuteReader();

                // close the reader so we can run query again
                dr.Close();
            }

            // close the connection
            conn.Close();

        }

Notice that bit at line 13. That’s where we tell the database to get ready to run this statement many times. This is the key to it.

In the example, I open a connection and then query the database 10,000 times.  Each time it’s the same query, but with a different (and random) parameter so the database doesn’t know what’s coming.

Here are the results of my performance testing.

Inline SQL vs. Parameterized Query vs. Prepared Parameterized Query
Inline SQL vs. Parameterized Query vs. Prepared Parameterized Query

As you can see, the inline query and the unprepared parameterized query took basically the exact same amount of time.  But once we prepared the statement, it cut the time roughly in half.  That’s a huge impact.

Stored procedures are another way to do this, but I can’t test that in my current environment.

Leave a Reply

Scroll To Top