SLOW saving record
I used this below to update a record
Me
.Validate()Me.EDFBindingSource.EndEdit()Me.EDFTableAdapter.Update(Me.ST102ADataSet.EDF)Me.Button3.Focus()Me.Button4.Enabled =FalseIf I am on my pc where the sql resides, why would it take roughly 6 seconds for the data to be update?
I know thats how long because the way I have my code, the button doenst become disabled until 6 seconds have elasped.
Would I be better off to populate the controls and get ride of the bindingsource, then save changes using hard coding.
Davids Learning
Hi,
how many changed records does your datatable have to update? Can you confirm that the Update statement is causing this delay (and not, for example, validation)? You can check this by stepping through code with debugger. If the update is really that slow, you can also hook up the SQL Profiler (or similar tool) to your database to see what's really going on there...
Andrej
Hey,
There are 41 fields in the table, and only 28 of them are used on this form.
Most of the fields as short integers.
Anyhow, I did try to find out if it was validation and its not.
But I went ahead and deleted the binding sources of all of the controls, loaded them through hardcode, and save through hardcode and it was only about 1-2 seconds which is a big difference.
Wonder why?
Davids Learning
 Davids Learning wrote: |
| ...binding sources of all of the controls... |
|
I'm wondering what you meant by the above... How many binding sources are you using? If all of your controls are bound to single table fields, only one binding source [BindingSource control?] should be enough [with all controls being bound to it].
Andrej
I'm also wondering about the possibility of mulitple binding sources...
But I wanted to mention that you should call the GetChanges() method when passing a table to an Update() method. This way the adapter does not have to check the RowState of rows that have not changed.
It looks something like: Me.EDFTableAdapter.Update(Me.ST102ADataSet.EDF.GetChanges)
HTH
-EDIT-
Also, you might modify your select statement on the adapter to not pull the fields that are not being used. This will also help speed up the the row lookup when doing updates - infact, you might want to modify the update query that was autogenerated so that it only uses primary key info to find the existing record, rather than using every field value. This can introduce concurency issues as the record will be found even if another user has modified it, but concurrency is something you have to handle one way or another anyhow.
Thats probably from me not knowing some of the terminoligy yet.
What I meant was for each textbox,combo, there was one bindingsource , then each textbox was bound to a field in that one bindingsource. When you drag and drop from the datasources, it sets it to that by default.
Is this not preffered?
Davids Learning
1st - There is one other binding source in this form, but its from a different table and is used only for an error log.
2nd- when I but in .getchanges, I get an error of
implict conversion from system.data.datatable from myprogram.st102adataset.edf.datatable
what does that mean and what do i need to do to correct that?
3rd- When you refer to limiting what it pulls for data are you talking about this?
This is how I loaded the data(after the first attempt-I got away from dragging and dropping, I deleted the bindingsource from the textboxes and combos),
Dim EDFRow As ST102ADataSet.EDFRowIf Me.ENameListBox.SelectedItems.Count = 1 ThenEDFRow = ST102ADataSet.EDF.FindByEName(
Me.ENameListBox.SelectedValue.ToString)'set dataMe.EActiveComboBox.Text = EDFRow.EActiveMe.ELocationComboBox.Text = EDFRow.ELocationif not please explain a little bit more please, I definately dont wont to pull a record someone esle has pulled to modify.
Will the above take care of concurrency issues?
Davids Learning
OK, sounds like your binding source was fine.
That GetChanges() error sounds strange... Could you repost it being sure to copy the message text exactly as it appears? First it would seem that one of those 'from's should be a 'to' and second the message sounds imcomplete. It would appear though that somehow the data type of the EDFDataTable in the DataSet is not the type that the EDFTableAdapter is expecting. It may be that in previous trial and error, you've somehow corrupted the DataSet or TableAdapter; have you modified any of the designer (pregenerated) code?
On limiting the data - that's not what I meant. I was referring to modifing the Select Command on the TableAdapter. You would go to the DatSet designer, right click on the TableAdapter and select Configure. In this wizard you could then modify the selection of fields from the database and only select the ones you need. After that change is made and the Update Command is updated, you could modify that command through the Properties task window and remove all parameters except the Primary Key field. Modifying the Update Command is where the concurrency issue is created. By default the update command was comparing the original value of every field in the modified row to the database to find the database row to update. If another user had changed just one field in the same row, the update would fail becuase the original row no longer could be found. By doing this modification, on the primary key (which cannot change) is used to find the original row to update. So under this method, the last change wins - unless you implement some other method to manage it.
The change you made to displaying the data should not affect the time it takes to execute an Update(). In fact, the TextBoxes and ComboBoxes bound to a BindingSource should be faster at displaying those values than the code you switched to.
I dont believe I have changed the table adapter(other than changing dbnull to nothing in some of the columns), I havent done anything to the binding source.
the error is exactly
Implicit conversion from 'System.Data.DataTable' to 'MyApp.ST102ADataSet..EDFDataTable'.
Imade a different form, dragged the same amount of textboxes and combos, and still it takes about 6 seconds for the table adapter to update.
I can put code in and remove the binding source and it still out performs the table adapter for some reason.
So if I click on the table adapter and go to properties and delete all of the fields except the primary key in the query, it will update fine?
Davids Learning
Hmm... I wonder if this is your VS settings (eg. Option Strict is turned on)... You might have to cast the DataTable returned by GetChanges() directly... EDFTableAdapter.Update(DirectCast(Me.ST102ADataSet.EDF.GetChanges, ST102DataSet.EDFDataTable))
What are you using to update the database when you remove the BindingSource? You said you have code that out performs the TableAdapter. Are you creating a SQLCommand object and executing your own update query? If so, and this query is only being called to update a single record, than it would certianly be faster than passing the entire table to the Update() method of the TableAdapter without calling GetChanges.
I'm not saying that reconfiguring the TableAdapter will decrease the update time, I'm just saying it might help.
How many records are we talking about? How many are in the entire DataSet.DataTable? How many have been modified when the Update() method is called?
I'm still lost as to the correlation between the data display and the data update... As I said, whether you use a BindingSource object to dispay the values in the controls, or if you set those values using code, it should have no effect on the time it takes the Update() method to complete. The only changed code I've seen you post is for displaying data - I've only seen you post one code snipit for updating data...
I'm starting to suspect that there is something fundementaly wrong with the way you are doing your data access and manipulation... You might need to just explain how you setup your data access and post all your code from Fill to Edit to Update.
Option explicit and option strict are on as advised in a earlier post.
Here again, it is propably the terminology thats in the way here, but the binding source is still there, i just deleted the binding source from the textboxes,combos properties. There is only 69 records in this table and only one of them is being edtied at a time. There are 12 tables in this database file, all being short records(less than 75 records per table.) Is this bad, should I have more datasets defined with fewer tables?
When I call for an update, it is strictly for the one page being viewed only(less than 20 fields in one table, except for my error handler(which is only called if an exception happens)
The form has 1 listbox with only one field(names of people), Then there is a tabcontrol with 9 tabs, on each tab there are from 1-9 textboxes,combos showing information from only one record(being the one that is selected in the listbox)
This is how I load the form
Me.MyErrorLogsTableAdapter.Fill(Me.ST102ADataSet.MyErrorLogs)Me.EDFTableAdapter.Fill(Me.ST102ADataSet.EDF)I kept the bindingsource on the listbox to populate only the names from the field in the table
Then when the form is loaded, you click on a name in the listbox to view only one record from one table
This is how I retrive the data
Private Sub ENameListBox_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ENameListBox.Click
Dim EDFRow As ST102ADataSet.EDFRow
If Me.ENameListBox.SelectedItems.Count = 1 Then
EDFRow = ST102ADataSet.EDF.FindByEName(
Me.ENameListBox.SelectedValue.ToString)Me.Label17.ForeColor = Color.GreenMe.Label17.Text = Me.ENameListBox.SelectedValue.ToString & "'s Employee Records"'set dataMe.EActiveComboBox.Text = EDFRow.EActiveMe.ELocationComboBox.Text = EDFRow.ELocationMe.EUniformComboBox.Text = EDFRow.EUniformMe.EEvalPeriodComboBox.Text = EDFRow.EEvalPeriodMe.EOccuranceComboBox.Text = EDFRow.EOccLevelMe.EAddress1TextBox.Text = EDFRow.EAddress1Me.EAddress2TextBox.Text = EDFRow.EAddress2Me.ECityTextBox.Text = EDFRow.ECityMe.EStateTextBox1.Text = EDFRow.EStateMe.EZipTextBox.Text = EDFRow.EZipMe.EPhone1TextBox.Text = EDFRow.EPhone1Me.EPhone2TextBox.Text = EDFRow.EPhone2'end set'check apps user role assignment and allow privileges
If EDFRow.EScope.ToString = "2" ThenIf EDFRow.MySec = "No" ThenMe.RadioButton4.Checked = FalseMe.RadioButton3.Checked = TrueElseMe.RadioButton3.Checked = FalseMe.RadioButton4.Checked = TrueEnd IfIf Me.TabControl1.TabCount = 9 ThenIf EDFRow.MySec.ToString = My.Settings.CurrentUser Or EDFRow.MySec.ToString = "No" ThenElseMe.TabControl1.TabPages.Remove(TabPage3)Me.TabControl1.TabPages.Remove(TabPage4)Me.TabControl1.TabPages.Remove(TabPage5)Me.TabControl1.TabPages.Remove(TabPage6)Me.TabControl1.TabPages.Remove(TabPage7)Me.RadioButton1.Enabled = TrueMe.RadioButton1.Checked = TrueMsgBox(
"This Employee's Information is only available for " & EDFRow.MySec.ToString & ".", MsgBoxStyle.Information, "Sorry!")Exit SubEnd IfElseIf EDFRow.MySec.ToString = My.Settings.CurrentUser Or EDFRow.MySec.ToString = "No" ThenMe.TabControl1.TabPages.Insert(3, TabPage3)Me.TabControl1.TabPages.Insert(4, TabPage4)Me.TabControl1.TabPages.Insert(5, TabPage5)Me.TabControl1.TabPages.Insert(6, TabPage6)Me.TabControl1.TabPages.Insert(7, TabPage7)Me.RadioButton2.Checked = TrueMe.RadioButton1.Enabled = FalseMe.Refresh()ElseMsgBox(
"This Employee's Information is only available for " & EDFRow.MySec.ToString & ".", MsgBoxStyle.Information, "Sorry!")End IfEnd IfEnd IfIf EDFRow.EScope.ToString = "1" ThenIf Me.TabControl1.TabCount = 4 ThenExit SubElseMe.TabControl1.TabPages.Remove(TabPage3)Me.TabControl1.TabPages.Remove(TabPage4)Me.TabControl1.TabPages.Remove(TabPage5)Me.TabControl1.TabPages.Remove(TabPage6)Me.TabControl1.TabPages.Remove(TabPage7)Me.RadioButton1.Enabled = TrueMe.RadioButton1.Checked = TrueEnd IfEnd IfEnd IfEnd SubThis is an employee file,with 2 different views depending on the role of the user, I took out some of the textboxes,combos to save space here.
I have in each textbox,combo using the changed event to tell which group has been modified(1-9)
My.Settings.MyTemp = 1
then there is a button on the bottom of the form to accept the changes
this is how I am updating my record
Dim EDFRow As ST102ADataSet.EDFRow
EDFRow = ST102ADataSet.EDF.FindByEName(Me.ENameListBox.SelectedValue.ToString)
Try
If My.Settings.MyTemp = 1 ThenEDFRow.EAddress1 =
Me.EAddress1TextBox.TextEDFRow.EAddress2 =
Me.EAddress2TextBox.TextEDFRow.ECity =
Me.ECityTextBox.TextEDFRow.EState =
Me.EStateTextBox1.TextEDFRow.EZip =
Me.EZipTextBox.TextEDFRow.EPhone1 =
Me.EPhone1TextBox.TextEDFRow.EPhone2 =
Me.EPhone2TextBox.TextEnd IfMe.Validate()Me.EDFBindingSource.EndEdit()Me.EDFTableAdapter.Update(Me.ST102ADataSet.EDF)Me.Button3.Focus()Me.Button4.Enabled = FalseMe.Label17.ForeColor = Color.GreenMe.Label17.Text = Me.ENameListBox.SelectedValue.ToString & "'s Employee Records"Catch ex As ExceptionDim newMyErrorLogsRow As ST102ADataSet.MyErrorLogsRownewMyErrorLogsRow = ST102ADataSet.MyErrorLogs.NewMyErrorLogsRow
newMyErrorLogsRow.ELog = Err.Description
newMyErrorLogsRow.EMod =
"Button4 Click"newMyErrorLogsRow.EIP = GetIPAddress()
newMyErrorLogsRow.EDate = Now
newMyErrorLogsRow.EForm =
"empex"ST102ADataSet.MyErrorLogs.Rows.Add(newMyErrorLogsRow)
Me.Validate()Me.MyErrorLogsTableAdapter.Update(Me.ST102ADataSet.MyErrorLogs)End TryEnd SubI took out group 2-9 to save space here.
If there is a better way to do this, please let me know, I am learning here and if I need to change my ways it wont be the first.
Is there something that needs to be done if you edit a record, say if the user sits for 10 minutes the edits a record, if the app is disconnected from the server, how is it going to know that a change has been made to what it pulled previously?
Davids Learning
