First published on MSDN on Oct 10, 2010
Back in a post from May (
http://blogs.msdn.com/b/psssql/archive/2010/05/30/don-t-touch-that-schema.aspx
), I reported that SSRS referenced fields by index instead of by name in code for performance benefits. As a follow up to that, I decided to do some testing to demonstrate the performance benefit of this approach. Here is the code I wrote for the testing (note: for simplicity’s sake, I am running this against a ReportServer catalog database):
Imports System.Data.SqlClient
Imports System.Data
Module Module1
Sub Main()
Dim tsName As New DateTime
Dim tsNum As New DateTime
Dim teName As New DateTime
Dim teNum As New DateTime
Dim totalName As Long
Dim totalNum As Long
Dim iterations As Int16 = 10000
Dim strCn As String = "Integrated Security=SSPI;Initial Catalog=ReportServer;Data Source=myserver”
Dim cmdTxt As String = "select * from ExecutionLog2"
For i As Int32 = 1 To iterations
Dim cn As New SqlConnection(strCn)
Dim cmd As New SqlCommand(cmdTxt, cn)
cn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
'randomly pick a field number to reference
Dim rand As New Random()
Dim fieldnum As Int16 = rand.Next(0, dr.FieldCount - 1)
'now, get it by index
dr.Read()
tsNum = DateTime.Now
Dim val1 As Object = dr.Item(fieldnum)
teNum = DateTime.Now
totalNum += teNum.Subtract(tsNum).Ticks
'close the connection
cn.Close()
If (i Mod 1000) = 0 Then Console.WriteLine(i)
Next i
Console.WriteLine("By index (ms): " + (totalNum / 10000).ToString)
'Now repeat the process by name
For i As Int32 = 1 To iterations
Dim cn As New SqlConnection(strCn)
Dim cmd As New SqlCommand(cmdTxt, cn)
cn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
'randomly pick a field number to reference
Dim rand As New Random()
Dim fieldnum As Int16 = rand.Next(0, dr.FieldCount - 1)
'now, get the field by name
dr.Read()
fieldnum = rand.Next(0, dr.FieldCount - 1)
tsName = DateTime.Now
Dim fieldname As String = KnownFieldName(fieldnum)
Dim val2 As Object = dr.Item(fieldname)
teName = DateTime.Now
totalName += teName.Subtract(tsName).Ticks
'close the connection
cn.Close()
If (i Mod 1000) = 0 Then Console.WriteLine(i)
Next i
Console.WriteLine("By name (ms): " + (totalName / 10000).ToString)
End Sub
Private Function KnownFieldName(ByVal num As Int16) As String
Select Case num
Case 0
Return "InstanceName"
Case 1
Return "ReportPath"
Case 2
Return "UserName"
Case 3
Return "ExecutionId"
Case 4
Return "RequestType"
Case 5
Return "Format"
Case 6
Return "Parameters"
Case 7
Return "ReportAction"
Case 8
Return "TimeStart"
Case 9
Return "TimeEnd"
Case 10
Return "TimeDataRetrieval"
Case 11
Return "TimeProcessing"
Case 12
Return "TimeRendering"
Case 13
Return "Source"
Case 14
Return "Status"
Case 15
Return "ByteCount"
Case 16
Return "RowCount"
Case 17
Return "AdditionalInfo"
End Select
'if we don't hit a case statement, throw an exception
Throw New System.NotSupportedException
End Function
End Module
And, here’s the output:
1000
2000
3000
By index (ms): 530053
1000
2000
3000
By name (ms): 1020102
1000
2000
3000
By index (ms): 580058
1000
2000
3000
By name (ms): 710071
1000
2000
3000
By index (ms): 510051
1000
2000
3000
By name (ms): 850085
As you can see, accessing the fields by index took an average of 540 seconds for 3000 iterations, or about 0.18 seconds per access. Accessing them by name took 860 seconds for 3000 iterations, or about 0.29 seconds per access. Personally, I’ll take 1/10th of a second of performance improvement when I am running a service that might serve thousands of simultaneous requests.
Evan Basalik | Senior Support Escalation Engineer | Microsoft SQL Server Escalation Services
