Results 1 to 16 of 16

Thread: Speed issues and trips to the database

Hybrid View

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Speed issues and trips to the database

    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:

    Code:
     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:
    Code:
    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

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Speed issues and trips to the database

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

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Speed issues and trips to the database

    Quote Originally Posted by CVMichael View Post
    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Speed issues and trips to the database

    Quote Originally Posted by szlamany View Post
    That's a big generalization.
    Maybe you should post an example in the CodeBank?

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Speed issues and trips to the database

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

    I call web methods like this

    Code:
        <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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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:

    Code:
      <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

  8. #8
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Speed issues and trips to the database

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

    Why don't you time each function?

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Speed issues and trips to the database

    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.

  11. #11
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Speed issues and trips to the database

    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.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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.

    Code:
    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

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Speed issues and trips to the database

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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

  15. #15
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Speed issues and trips to the database

    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:

    Code:
    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.
    My usual boring signature: Nothing

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2008
    Posts
    513

    Re: Speed issues and trips to the database

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width