-
May 10th, 2012, 01:53 PM
#1
Thread Starter
Fanatic Member
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
-
May 10th, 2012, 02:05 PM
#2
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?
-
May 10th, 2012, 02:42 PM
#3
Thread Starter
Fanatic Member
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
-
May 10th, 2012, 06:31 PM
#4
Re: Speed issues and trips to the database
Originally Posted by CVMichael
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.
-
May 10th, 2012, 06:35 PM
#5
Re: Speed issues and trips to the database
Originally Posted by szlamany
That's a big generalization.
Maybe you should post an example in the CodeBank?
-
May 10th, 2012, 06:41 PM
#6
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.
-
May 10th, 2012, 07:28 PM
#7
Thread Starter
Fanatic Member
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
-
May 10th, 2012, 02:43 PM
#8
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?
-
May 10th, 2012, 03:04 PM
#9
Thread Starter
Fanatic Member
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
-
May 10th, 2012, 03:05 PM
#10
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.
-
May 10th, 2012, 05:14 PM
#11
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.
-
May 10th, 2012, 06:04 PM
#12
Thread Starter
Fanatic Member
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
-
May 10th, 2012, 06:27 PM
#13
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.
-
May 11th, 2012, 10:38 AM
#14
Thread Starter
Fanatic Member
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
-
May 11th, 2012, 11:41 AM
#15
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
-
May 11th, 2012, 01:34 PM
#16
Thread Starter
Fanatic Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|