Speed issues and trips to the database

[Proctor]

Hello: I have a .net windows app created using 2005.
one of my forms is taking longer than i would like to load.

when it loads, I call numerous subroutines that go to the web server via a web services to get data from the database that also resides there....to populate all the drop downs on the form:

 Private Sub LogJobs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call PopulateCustomers()
        Call PopulateContacts()
        Call PopulateTerritory()
        Call PopulateSales()
        Call PopulateComments()
        Call PopulateSignName()
end Sub

Here's a code example for one of them....they pretty much all look similar:

Public Sub PopulateTerritory()
       If IsNothing(tblLookupTerritory) Then
            Dim oService As New SloanLEDEstimator_WebService.Service
            tblLookupTerritory = oService.PopulateTerritory()
        End If
        Try
            If tblLookupTerritory.Rows.Count >= 1 Then
                With cboTerritory
                    .DataSource = tblLookupTerritory
                    .DisplayMember = "Territory"
                    .ValueMember = "Territoryid"
                End With
                cboTerritory.SelectedItem = 1
            End If
        Catch
            MsgBox("An error occurred in PopulateTerritory " & sql)
        End Try
    End Sub

I was thining perhaps it would be better to make one trip to the server and get a list of datatables to return with.
Do you think this is the better way to deal with this?

Thanks,
Proctor

[CVMichael]

Web services are slow in general... why do you have to use web services? can't you connect directly to the database?

[Proctor]

I was told that they were more secure. I could connect to the db directly.
do you think that's the primary cause of the slowness? I have other screens that make trips to the database via the web service but they aren't this slow in loading.

Thanks,
proctor

[CVMichael]

Well, it depends where is the database and where is you app running...

Why don't you time each function?

[Proctor]

The database and web service reside on a server that is hosted by a facility that's located many miles away from where our company is located - which is where the client windows application lives.

I'll do as you suggested and time each one...perhaps the issue is with one- good idea. I'll return with the results shortly.

thanks,
Proctor

[si_the_geek]

I wouldn't be surprised if one or more of those items returns a large amount of data (especially Comments and Sales), and if that is the case then that would most likely be the cause of slowness. Timing them should tell you which ones(s) to focus on.

[CVMichael]

Sounds to me like you should convert your app to a web app (ASP.NET). This way, it resides on the server, and you have a direct connection to the database.

[Proctor]

Unfortunatly, the app needs to run on the client because it incorporates use of a .Net autocad api so they are opening up autocad and entering a command that then runs the application.

I've been timing it all and I think it's 2 of the calls to populate dropdowns that are the primary cause:

Call PopulateCustomers()
Call PopulateContacts()

I also noticed though that if i leave the screen and go back in, it loads really fast...that's because i have this code that says to go to the database only if the datatable is nothing.

If IsNothing(tblCustomer) Then
                Dim oService As New Company_WebService.Service
                tblCustomer = oService.PopulateCustomers(iGroupId, "Existing", cboUnits.Text)
end if

One of those tables returns 3,222 records and the other returns 1604 records.

One idea is that i can load these two datatables when the user is logging into the application. After they login, I already have a timer that runs and I have a label that says "Loading Estimator......." so maybe it's better to have them waiting at this point - they aren't officially in the app yet.

What do you think?

thanks,
Proctor

[szlamany]

If this is vb.net then start another thread loading that data so your app doesn't look non responsive.

Make sure to lock down any controls related to that data that is being async loaded.

When those threads complete they in turn unlock the controls related to that data.

btw - you were asked to time the events. Did you actually do so - do you really know the time it takes to run each of those two functions?

Use a stopwatch to do it if you have not - I've got code here that I can post if you need to see an example.

[szlamany]

Web services are slow in general...

That's a big generalization.

A well made web method can build it's data-for-return quicker than most native asp.net methods. I make sure to use StringBuilder to allocate memory and poke the strings into concatenation...

I switched from using asp.net to my own ajax/jquery/vb.net web methods as a way to make things run faster actually.

[CVMichael]

That's a big generalization.

Maybe you should post an example in the CodeBank?

[szlamany]

My point is that all that asp.net is - well it's just a big web service anyway.

I call web methods like this

    <WebMethod()> _
    <ScriptMethod(ResponseFormat:=ResponseFormat.Json, UseHttpGet:=False)> _
    Public Function LookupService(ByVal fromwho As String) As String
        Dim JsonMaker As JsonWriter = New JsonWriter

        With JsonMaker
            .StartObject()
            Dim NeedColsBln As Boolean = True
            Dim NeedHeadBln As Boolean = True
            Dim SettingColsBln As Boolean = False
            Try
                Using dcn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("LocalSQLServerAWC").ToString)
                    Using cmd As New SqlCommand
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.CommandText = "dbo.awc_BatchInvoice_Lookup_PONumber"
                        cmd.Connection = dcn
                        cmd.CommandTimeout = 0
                        DetermineParameters(dcn, cmd)
                        SetParameters(cmd, True, fromwho, Nothing)
                        dcn.Open()
                        Using sdrReader As SqlDataReader = cmd.ExecuteReader
                            While sdrReader.Read
                                If sdrReader.GetName(0).StartsWith("acs-") Then
                                Else
                                    For i As Integer = 0 To sdrReader.FieldCount - 1
                                        .NewObject(sdrReader.GetName(i), sdrReader(i).ToString, True, True)
                                        If i < sdrReader.FieldCount - 1 Then .Seperate()
                                    Next
                                End If
                            End While
                        End Using
                    End Using
                End Using
            Catch ex As Exception
                .ResetJson()
                .StartObject()
                .NewObject("%%dalerror%%", ex.Message.Replace("""", "'").Replace("\", "\\"))
            End Try
            .EndObject()
        End With

        Return JsonMaker.GetJson()

    End Function

The JsonWriter class uses a StringBuilder so that the return data isn't thrashed around memory.

When I see a web method take too long I get rid of the JsonWriter class and talk to the StringBuilder locally to really speed it up.

[Proctor]

Thank you for your reply. Can you please explain more about "thread"? I'm not familiar with this terminology.

Also, I will provide the actual time for these each to load. I have a stop watch I can use via my phone app.

Here's a sample of the code I'm using on the webservice:

  <WebMethod()> _
Public Function PopulateTerritory() As DataTable
        Using DBConn As SqlConnection = New SqlConnection(sConnectionString)
            PopulateTerritory = Nothing
            Dim DBCommand As SqlDataAdapter
            Dim myDS As New DataSet
            DBCommand = New SqlDataAdapter _
                ("select TerritoryId, Abbreviation + ' - ' + Territory  as territory,Abbreviation from tblLookupTerritory order by Abbreviation", DBConn)
            Try
                DBCommand.Fill(myDS, "tblLookupTerritory")
                Dim tblLookupTerritory As System.Data.DataTable
                tblLookupTerritory = myDS.Tables("tblLookupTerritory")
                If tblLookupTerritory.Rows.Count >= 1 Then
                    PopulateTerritory = tblLookupTerritory
                End If
            Catch
                MsgBox("An error occurred in PopulateTerritory")
            End Try
        End Using
        Return PopulateTerritory
    End Function

Do you see anything there that could use some improvement?

Thanks again,
Proctor

[Proctor]

Hello: I timed each call to Populate and have my result:

cboCustomer: 02.4
cboContact: 04.6
cboTerritory: 01
cboSales: 01.3
cboComments 01.
cboSignReads 00.5

When I time how long it takes to load all together:
.07 (approx)

Now I'm thinking that it's a combination of all these loading...it's not an instantaneous load like i would like. I suppose 7 sec isn't so bad...but it feels like alot for me when I'm waiting and so I think the user would think that too.

Thanks again.....
Proctor

[Shaggy Hiker]

You might look over in the CodeBank for my lightweight profiler. You can use that to get very precise times on code, which might be of interest if you really want to nail down the cost of each.

However, the ultimate solution is probably going to be something like what I did in a similar situation: As soon as the program begins running, launch a thread to start loading the datatables. If the thread finishes by the time the tables are needed, all is well. If the thread hasn't finished yet, you can either Join on the thread (which will freeze the app until the tables are ready, so you'd have to put up a message, in this case), or you can just not show the controls.

In my case, the slow controls were all on a form that wasn't reached by the user in less than ten seconds, normally, so I put a couple labels on the form that would display a wait message if the user got there before the data was ready for the controls:

Public Sub SetUp(ByVal cnStr As String)
        Me.bReLoad.Visible = False 'Hide one control.
        Me.lMess1.Text = "Loading..." 'Show wait message.
        Me.lMess2.Text = "...Please Wait" 'More wait message.
        mConnectionString = cnStr
        uiContext = System.Threading.SynchronizationContext.Current 'The context is needed.
        
        mData = New DataSet
        mFillT1 = New Threading.Thread(AddressOf FillData)
        mFillT1.IsBackground = True
        mFillT1.Start()
    End Sub

Private Sub FillData()

        Using cn As New System.Data.OleDb.OleDbConnection(mConnectionString)

            Try
                cn.Open()
            Catch ex As Exception
                mError = "Failed to Open: " & ex.Message
                uiContext.Post(AddressOf EventRaiser, False) 'Post event to UI thread.
                Return
            End Try
            Using cmd As System.Data.OleDb.OleDbCommand = cn.CreateCommand
                Using da As New System.Data.OleDb.OleDbDataAdapter
                    Try

                        'Need to fill three tables here.
                        Try
                            cmd.CommandText = "some select statement"
                            System.Threading.Monitor.Enter(mData) 'Not wise, actually, but ok for this.
                            da.SelectCommand = cmd
                            da.Fill(mData, "StreamTable")
                        Catch ex As Exception
                            mError = "Failed StreamTable: " & ex.Message
                            uiContext.Post(AddressOf EventRaiser, False)
                            Return
                        Finally
                            System.Threading.Monitor.Exit(mData)
                        End Try

                    Catch
                        mError = "Outer Catch"
                        uiContext.Post(AddressOf EventRaiser, False)
                        Return
                    End Try
                End Using
            End Using
        End Using

        'This method used Invoke to bind the tables to the controls.
        BindDataToCombo()
        uiContext.Post(AddressOf EventRaiser, True) 'Raise an event to indicate that the thread is done.
    End Sub

The way this worked (and much has been removed), is that the form shows a couple labels indicating that things are happening, then the thread is called to load the datatables. Rather than having the UI thread JOIN the background thread, I post a call to the UI synchronization context, which I had stored before starting the thread. What this does is calls a method in the UI thread. The method raises an event, but since it was called on the UI thread, the event is on the UI thread, as well. In that method, I hid the wait messages and enabled whatever control I had disabled.

In other words, as long as the thread was still running, the wait messages would be displayed on the form. When the thread finished, it raised an event, and the form handled that event to hide the wait messages. If the user reached the form while the thread was still running, they would see the wait messages and a critical control would be unavailable. At some point, the thread would complete, the wait messages would go away, and the controls would be enabled.

[Proctor]

Shaggy Hiker: Thank you for explaining this to me and showing me an example. I'm going to try to create the thread and load the tables as suggested. I'll be in touch shortly to let you know the results.

(I'll also be looking at that code to get the exact times....I think that would be very useful).

Thanks again,
Proctor