Stored Proc SLOW

[BrailleSchool]

Hi, all,

Long time no see. I am working on an app. VB 2005. The log on screen authenticates to Active Directory. When the end user logs in, if their full name is not in MSSQL, it adds the name. If the end users name is in the table, the app is not slow as hell with loading. If the app inserts their name, the app slows down to a crawl.

I am using a stored procedure for the check and insert. Please let me know what I need to do, to make this faster.

User Authentication

    Public Function GetFullName(ByVal UName As String) As String
        Using Searcher As New DirectorySearcher(New DirectoryEntry())
            With Searcher
                .PageSize = 1000
                .SearchScope = SearchScope.Subtree
                .Filter = "(SAMAccountName=" & UName & ")"
            End With

            Using Results As SearchResultCollection = Searcher.FindAll
                If Results Is Nothing OrElse Results.Count <> 1 Then
                    Throw New ArgumentException("Invalid number of results returned - either no users were found or more than one user account was found.")
                End If

                Using Entry As DirectoryEntry = Results(0).GetDirectoryEntry
                    Return CStr(Entry.Properties("GivenName").Value) & " " & CStr(Entry.Properties("sn").Value)
                End Using
            End Using
        End Using
    End Function

Stored Procedure

ALTER PROCEDURE dbo.CheckTicketAuthorExists(@User VARCHAR(MAX))
AS
BEGIN
IF (SELECT COUNT(*) FROM TicketAuthors WHERE TicketAuthors = @User) = 0
BEGIN
INSERT INTO TicketAuthors
VALUES(@User)
END
END

Login method for authentication

    Private Sub Login_Click(ByVal sender As System.Object, _
                            ByVal e As System.EventArgs) Handles Login.Click

        Dim User As String = Username.Text.ToString.Trim
        Dim Pass As String = Password.Text.ToString.Trim

        If ValidateUser(User, Pass) Then
            With DisplayMessage
                .Text = GetFullName(User) & " Authenticated! Logging In..."
                .ForeColor = Color.DarkGreen

                Dim fDashboard As New DashBoard

                With fDashboard
                    .DisplayUserFullName.Text = "Welcome " & GetFullName(User) & "!"
                    .MdiParent = Me.MdiParent
                    .Dock = DockStyle.Fill
                    .Show()
                End With

                MDIContainer.TasksMenu.Enabled = True

                Using SetDatabaseConnection As New SqlConnection(MSSQLConnection)
                    Dim CheckUser As SqlCommand = New SqlCommand

                    SetDatabaseConnection.Open()

                    With CheckUser
                        .Connection = SetDatabaseConnection
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = "CheckTicketAuthorExists"
                        .Parameters.AddWithValue("@User", GetFullName(User))
                        .ExecuteNonQuery()
                    End With

                    SetDatabaseConnection.Close()
                End Using

                With Me
                    .Close()
                    .Dispose()
                End With
            End With
        Else
            With DisplayMessage
                .Text = "Login Mismatch! Please try again!"
                .ForeColor = Color.Red
                Password.Focus()
            End With
        End If
    End Sub
End Class

[szlamany]

Are you sure it's the SPROC? If you comment out the code that calls the sproc is it still slow?

I'm suspicious that the problem is the call to AD - I've seen that to be a slow performer...

Why a varchar(max) for that datatype? Try it with varchar(100) to see if that makes a difference (this is grasping at straws to say the least...)

[BrailleSchool]

I did comment out the sproc and I did not see any performance issues. Only when I was using it.
I will try the varchar(100) to see if there is a difference in performance.
Thanks for the suggestions.

[TysonLPrice]

I'm just throwing darts but you could try and use NOT EXISTS:

IF not exists (SELECT '' FROM #TicketAuthors WHERE TicketAuthors = @User)
	BEGIN
		INSERT INTO #TicketAuthors
		VALUES(@User)
	END
END

Have you tried indexes?

[FunkyDexter]

Have you tried calling the sproc directly in Management Studio? If the problem really is the sproc (and I'm with SzLamany on this, it seems unlikely) then you're going to need to see the execution plan to get to the bottom of it because there's nothing wrong with the sql in your sproc.

Have you tried indexes?

Indexes won't speed up an insert, if anything they'll slow it down. It's possible the select is running slow but the OP says it only happens when the User isn't found.

[TysonLPrice]

Have you tried calling the sproc directly in Management Studio? If the problem really is the sproc (and I'm with SzLamany on this, it seems unlikely) then you're going to need to see the execution plan to get to the bottom of it because there's nothing wrong with the sql in your sproc.

Indexes won't speed up an insert, if anything they'll slow it down. It's possible the select is running slow but the OP says it only happens when the User isn't found.

What about the count that decides if an insert should be done? That is what I was thinking about.

Ooops...didn't see that you adressed that...