Diary of an ASP.NET bodger

Reusing SQLCOmmand - revisited :)

With regards to SQL-Injection attacks, I wasn't too concerned as it was a windows service I was writing. However, the poke about performance got me thinking. Would it really be any quicker with parameters, when I was just calling a sproc a few times.

Kirk Allen Evans (Good book btw) had these points to make

Second, you will get better performance by using a parameterized SQL statement by replacing the literal values with question marks and using parameters in place of the literal values. The parameters can then be reused as well, where you simply set the values during each iteration.

Third, you can enhance performance a little more if your provider supports the .Prepared property for the Command object. The .Prepared property pre-compiles the SQL statement and caches it, avoiding parses for invaalid SQL statements.

So, I needed to convert this module to run in a more optimised way.

Sub Main()

Dim startTime As Date = Date.Now

Dim myConn As SqlConnection = New SqlConnection("server=xxx; user id=xxx; password=xxx; database=xxx")
myConn.Open()

Dim main_startDate As Date = Date.Today.AddDays(5)
Dim startDate As Date = Date.Today.AddDays(5)

Console.WriteLine("Today=" & startDate.ToString("s"))

Dim mySQL As New SqlCommand("exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',3,'','','Boston',9999,0", myConn)

mySQL.ExecuteNonQuery()

Dim resorts(4)
  resorts(0) = "New York"
  resorts(1) = "Orlando"
  resorts(2) = "Miami"
  resorts(3) = "Las Vegas"
  resorts(4) = "Boston"
Dim resort As String

For Each resort In resorts

While startDate < Date.Today.AddDays(10)

mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',7,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()

mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',10,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()

mySQL.CommandText = "exec tcHolidays.dbo.TCHolidays_Get_Hotel_Costs 2,0,1,'" & startDate.ToString("s") & "',14,'','','" & resort & "',9999,0"
mySQL.ExecuteNonQuery()

mySQL.CommandText = "Exec tcHolidays.dbo.TCHolidays_get_Flights '" & startDate.ToString("s") & "','" & resort & "'"

mySQL.ExecuteNonQuery()
startDate = startDate.AddDays(1)

Console.WriteLine("Done flights and Hotels for " & startDate.ToShortDateString() & " in " & resort & ".")

End While

startDate = main_startDate

Next

myConn.Close()

Dim endTime As Date = Date.Now
Dim timeSpan As TimeSpan = endTime.Subtract(startTime)
Console.WriteLine("Process took " & timeSpan.ToString() & ".")
Console.WriteLine("Press enter..")
Console.ReadLine()

End Sub

Doing it this way took 1.21.044 (m.s.ms)

To answer Kirks first point, I needed to parametise two stored procedures. I did this outside of the main loop, meaning I could reuse the parameters as suggested.

Dim mySQL As SqlCommand = New SqlCommand
With mySQL
.CommandText = "tcHolidays.dbo.TCHolidays_get_hotel_costs"
.CommandType = CommandType.StoredProcedure
.Connection = myConn
.Parameters.Add(New SqlParameter("@num_Adults", SqlDbType.Int))
.Parameters("@num_Adults").Value = 2
.Parameters.Add(New SqlParameter("@num_Children", SqlDbType.Int))
.Parameters("@num_Children").Value = 0
.Parameters.Add(New SqlParameter("@num_Rooms", SqlDbType.Int))
.Parameters("@num_Rooms").Value = 1
.Parameters.Add(New SqlParameter("@theDate", SqlDbType.VarChar, 20))
.Parameters.Add(New SqlParameter("@nights", SqlDbType.Int))
.Parameters.Add(New SqlParameter("@hcode", SqlDbType.VarChar, 30))
.Parameters("@hcode").Value = ""
.Parameters.Add(New SqlParameter("@country", SqlDbType.VarChar, 30))
.Parameters("@country").Value = ""
.Parameters.Add(New SqlParameter("@resort", SqlDbType.VarChar, 30))
.Parameters.Add(New SqlParameter("@maxPrice", SqlDbType.Int))
.Parameters("@maxPrice").Value = 9999
.Parameters.Add(New SqlParameter("@minPrice", SqlDbType.Int))
.Parameters("@minPrice").Value = 0
End With

Inside the main loop, I then simply change the values of those parameters which change inside the loops:

With mySQL
.Parameters("@theDate").Value = startDate.ToString("s")
.Parameters("@nights").Value = 7
.Parameters("@resort").Value = resort
.Prepare()
.ExecuteNonQuery()
End With

Because I was reusing the mySQL.Command - I needed another sqlCommand object for the last sproc, which obtains flights. 

With myFlight_SQL
.Parameters("@theDate").Value = startDate.ToString("s")
.Parameters("@resort").Value = resort
.Prepare()
.ExecuteNonQuery()
End With

Now. Doing it this way took 1.28.088... 4 seconds slower.
So, I extended it to run over a longer period (30 days, rather than 5). The results were much the same.

With parameters: 8.52.9988944
Without - 8.10.13825644

Can anyone tell me where my extra 40 seconds is coming from?

The upshot is that doing it parametized is *much* cleaner code. I can hand this console to anyone and they'll be able to see instantly what it is doing, and why. Downside is that it seems to be slower but I think that the extra sqlCommand has somethign to do with that.

 

Posted: Jun 17 2003, 06:17 PM by Mr Pike | with 8 comment(s)
Filed under:

Comments

Jesse Ezell said:

I don't see why you are making so many queries in the first place... you should be able to pull back everything with one query (or why you should use more queries for the parameterized version... you aren't even comparing the same code, so those numbers mean nothing).

In any case, ALWAYS use parameterized queries. Anything else is a rediculously lame decision.
# June 17, 2003 9:23 AM

Kirk Allen Evans said:

The extra time is coming from the use of .Prepare inside the loop. Use .Prepare outside the loop and you should see a big performance improvement.
# June 17, 2003 9:54 AM

Kirk Allen Evans said:

One more comment: you are not going to see a noticeable time difference between using parameterized SQL queries that simply call a stored proc over using a CommandType of StoredProc. The real difference occurs back on the server, where the procedure is parsed and cached. Here is an OLD article that explains the concept by looking at ADO 2.1 with SQL Server 7 and Oracle 8. But, the same concepts still apply.

The Prepare statement caches the statement and indicates to the provider that the same statement will be run many times. The provider may or may not perform optimimizations to support this, which SQL Server does. By executing this each time within the loop, you cause extra processing. Move this out of the loop, because you only have to prepare it once.

The real benefit for parameterized queries comes not for stored procedures, but for inlined SQL calls, such as "SELECT CompanyName from Customers WHERE CustomerID='ALFKI'". Replace the ALFKI with a question mark, and you parameterize the inlined SQL call.
# June 17, 2003 10:01 AM

Jesse Houwing said:

I read somewhere that prepared statements aren't cleaned up by the Garbage Collector (But can't find the link right now), so be careful with using prepared when changing the actual statement.

If you store the command in your class and use it by changing the values of the parameters on each iteration you should be safe.
# June 18, 2003 12:05 AM

Damian Barrow said:

Dude, I wasn't hitting back, I'm just on a learning curve. I think the comparison is fair because I'm calling the same 4 sprocs but in different ways, one by putting my params inline, and one by actually parametising them. Kirk has spotted one major flaw in that I was calling .prepare each time.

I think Kirks last comment of parametising for none-stored procedures clears something up which may be confusing many new developers. I have always associated parameters with stored procedures and I mainly work with sprocs. If I ever do work with inline SQL its purely for quick and nasty, internal stuff where I don't want the hassle of adding another sproc to my DB just so I can do it the 'right' way, especially if its a transient thing.

Due to the way this 3rd party database is built, I can't drag everything back in one go, it involves some hefty working out of the price per person based on things like number of people in the room, room pricing structure, free-nights, etc etc. I won't bore you..:P

I removed the .prepare from the inner loop, and ran the figures through again. This time, the results took an average of 8.40 (over 3passes) which is still longer than inline. The .prepare obviously makes some difference when used correctly, but the whole thing is still almost 30 seconds slower than using

"exec mySproc '" & param1 & "','" & param2 & "'.

I *must* be missing something. I have to be. I agree with everyone who says "Use Parameters". Its cleaner code, easier to maintain and if someone shifts the order of the sproc (A danger of mine in that I deal with 3rd party databases) then you are protected. However, a difference of 30 seconds is a lot when you consider that is for a months period and I need to do this for 18mths and for 50 resorts rather than 5.

# June 18, 2003 12:50 AM

Kirk Allen Evans said:

Just for kicks, can you change the SqlCommand.CommandType to "StoredProcedure", does it have any effect on the call time? What about over several successive calls compared to the other methods? My guess is that there should be no performance increase, but it is possible that something is awry with the "exec myProcName" syntax.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskCodeExampleExecuteStoredProcedureThatReturnsValue.asp
# June 18, 2003 4:43 PM

Damian Barrow said:

I think I'll do a fresh blog comparing one method with the other. I went home, did some deep reading into ADO.NET, tuned my indexes, updated the statistics and tinkered about but I still could not get a commandtype.storedproc parametised call to work faster than a commandtype.text call with inline params.

It's a warehousing function which I wanted to run from a console (Mainly as a learning app).

Thanks for all the feed-back though Kirk & Jesse, it has helped tremendously.

Cheers
Damian
# June 19, 2003 1:03 AM

Barry Gervin said:

In terms of SQLServer and Oracle, there are no implicit performance improvements between a stored proc and a parameterized inline sql statement. Both of them have their execution plans compiled at first execution - and then reused.

In SqlServer - with non-parameterized sql - the syntax will be parsed and auto-parameterized - so 2nd & nth executions on similar sql (different hard coded values) should exhibit performance on scale with parameterized sql (or stored procs).

Sometimes the auto-parming doing by Sql isn't as efficient as a developer could manually parm it. Think of a case when you have a constant in the query that neverchanges: where inactive="n" and state="CA". SqlServer will auto-parm the "n" and the "CA" but you know that "n" will never change. That would lead to not as efficient parameterization.

Finally, the notion that a stored proc is as fast as parmed sql means that the stored proc's internal sql is in fact parameterized as well. If you have dynamic sql in your proc - then it runs like non-parmed sql - save with auto-parameterization.

So auto-parming? Cool for poorly written sql - but do yourself a favour and parm your sql always. You are giving the dbms more information to work with to optimize the plan...not to mention sql injection.
# April 14, 2004 2:17 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)