June 17, 2009 at 8:40 am
I'm currently testing new web servers with our existing web sites and have come across a curious problem. It seems that it's taking a very long time to get results back from SQL. It's exactly the same code, connecting to exactly the same database, but the original web server returned the data almost instantly, but the new server is taking anything up to 20 seconds.
The old and new servers are very different. The old server was:
Windows Server 2003 Standard Edition x86 SP3, IIS 6
The new one is:
Windows 2008 Enterprise Edition x64, IIS 7 (technically there are 2 of these load balanced, but I've removed that for the testing)
It's just a simple Sql Data Reader. Here's a sample below.
Any suggestions and/or things to check would be greatly appreciated.
Public Sub ValidateUser(ByVal strNewUserName As String)
Dim dr As SqlDataReader
Dim cmd As New SqlCommand("spUser_sGet", DbAccess.getConnection)
Me.UserName = strNewUserName
Try
cmd.Connection.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@username", SqlDbType.VarChar, 100).Value = strNewUserName
dr = cmd.ExecuteReader
If dr.Read Then
Authenticated = True
FullName = dr("Fullname").ToString
Department = dr("Department").ToString
Directorate = dr("Directorate").ToString
PositionTitle = dr("PositionTitle").ToString
Role = dr("role").ToString
EsrNumber = dr("ESRnumber")
PromisIdNumber = dr("PromisIdNumber").ToString
StationId = dr("stationId").ToString
ComplexId = dr("ComplexId").ToString
Area = dr("Area").ToString
Me.UserGroupList = Me.GetGroupList(Me.UserName)
Else
'no user
Authenticated = False
End If
dr.Close()
Catch ex As Exception
Authenticated = False
Dim objError As New MiErrorLogWs.ErrorLog
With objError
.Application = "MiSecurityService"
.Database = "MiApplicationSecurity"
.ModuleOrClass = "UserSecurityCredentialsDto"
.StoredProcedure = "spUser_sGet"
.SubOrFunction = "ValidateUser"
.Description = ex.Message
.Number = Err.Number
.User = strNewUserName
End With
objError.Write()
Finally
cmd.Connection.Close()
cmd.Dispose()
End Try
End Sub
July 21, 2010 at 1:47 pm
I guess misery loves company so...
Our situation is similar allthough we have figured out it appears to be access to our new SQL box itself. We take our new windows 2003 64 bit web server and point it at our Windows 2003 32 bit SQL environment and everything runs fine. Change the connection to the 2008 Enterprise edition 64 bit with SQL 2005 Server 64 bit and the query time increases by 20 seconds. We did notice an improvement by disabling TOE on the network card, but that improves by around 15 seconds, but results are sporadic at best. Still working with DELL\Equallogic on this issue.
July 22, 2010 at 10:32 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply