January 6, 2011 at 9:30 am
I'm not a VB.Net web app dev, so bear with me here, this may be a stupid question.
Our dev group has created a web app that is updating records in the database when the user hasnt changed anything. They claim this is the way it is and it's too much work to not issue an update when nothing changed.
Is this correct? Is there no:
if record is new
insert
else
if record is modified
update
end if
end if
Logic available?
January 6, 2011 at 9:38 am
I have to say it isn't really all that hard to at least keep a variable that says *something* has changed. However, IF the best practice of using a sproc for data access/modification was used, the sproc could be modified to check for actual changes and not perform an update if there isn't one. This keeps the front-end out of it. However, it would be a better practice for the front-end to actually keep track of whether something has changed, not necessarily which field, just some field.
Does the web page generate dynamic SQL to do inserts/updates?
CEWII
January 6, 2011 at 10:01 am
Thanks for the quick response.
The application is calling a stored procedure that has an insert flag parameter. If set to true the sp does an insert, if not does an update.
My problem is calling the sp from the front end even when nothing changes. It seems trivial to me to only call the sp when something changes.....otherwise do nothing. We're talking about a system with 8 tables and a similar amount of application windows. I'm getting A LOT of push back from the dev group over this...I dont understand why....
January 7, 2011 at 9:39 am
It's lazy developers. They think their time is too valuable to optimize this instead of fixing it right so that network and database resources are not wasted. A simple checksum often will determine if something has changed, and they can skip a call to the db if it's not needed.
I'd ask to see their code, and make a judgment if this is really that hard. Or I might try to talk to one of them and get a real estimate of what the change requires in resources (time/effort). It might be pervasive in the system, in which case it's not a simple or easy change. But it can be refactored over time and fixed in pieces.
Overall if this isn't heavily impacting the performance of the database, it's not critical, but it is something that you want to fix over time. As the server ages, data volumes go up, this will become more of an issue.
Note this also impacts backup times and space. More logs, larger diffs, etc. It's worth fixing.
January 7, 2011 at 9:44 am
Agree with the comments about lazy developers, just a word of warning, i was faced with the exact same situaiton last year where the devs thought it was easier to just update everything all the time, they tested with a couple of records and it worked fine... once in production and being used for hundreds of changes per minute it was a very different story and our websites crashed big time..
January 7, 2011 at 12:23 pm
Thanks for the feedback everyone. I suspected this isnt as big a deal as they were making it out to be, but didnt want to dig in my heels on this without some input.
The system is small, not yet live, so now is the time to fix it. The breaking point was the audit trail. Triggers were capturing the inserts and updates, to audit tables. A solid audit trail is a requirement of the app as it could potentially be used as evidence in a trial. I refused to put code in the triggers to determine whether something changed or not - you did an update, it's getting captured, if it is screwing up the audit requirements, stop updating when you dont need to 😉
Problem (hopefully) solved.
January 7, 2011 at 12:34 pm
a datatable in .NET has a flag on each row that signifies whether the row has been changed;
typically at our shop, we test a datatable,(usually from a types dataset, but it doesn't matter),
and only if there are changes, do we perfrom the update:
example:
Public Function hasChanges(ByVal table As DataTable) As Boolean
For Each row As DataRow In table.Rows
If hasChanges(table, row) Then Return True
Next
Return False
End Function
Private Sub Somecode()
If hasChanges(MyDataset.SomeTable) Then
MyDataAdapter.UpdateDataTable(SomeTable)
End If
Public Function hasChanges(ByVal table As DataTable, ByVal row As DataRow) As Boolean
If row.RowState <> DataRowState.Unchanged And _
row.RowState <> DataRowState.Detached Then
If row.RowState = DataRowState.Deleted Then
Return True
End If
If row.RowState = DataRowState.Added Then
Return True
End If
For Each col As DataColumn In table.Columns
If Not (col.Caption.ToUpper.Contains("_CALC")) Then
Select Case col.DataType.ToString
Case "System.Int32"
If GetInteger(row(col, DataRowVersion.Original)) <> GetInteger(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.Double"
If GetDouble(row(col, DataRowVersion.Original)) <> GetDouble(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.Decimal"
If GetDecimal(row(col, DataRowVersion.Original)) <> GetDecimal(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.DateTime"
If GetDate(row(col, DataRowVersion.Original)) <> GetDate(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.String"
Dim oldColValue As String = Trim(GetString(row(col, DataRowVersion.Original)))
Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))
If IsRTFColumn(col.MaxLength, newColValue) Then
If RTFColumnValueChanged(oldColValue, newColValue) Then Return True
Else
If oldColValue <> newColValue Then Return True
End If
Case Else
If GetString(row(col, DataRowVersion.Original)) <> GetString(row(col, DataRowVersion.Current)) Then
Return True
End If
End Select
End If
Next
End If
Return False
End Function
Public Function hasChanges(ByVal table As DataTable, ByVal row As DataRow, ByVal FieldsToIgnore() As String) As Boolean
If row.RowState <> DataRowState.Unchanged And _
row.RowState <> DataRowState.Detached Then
If row.RowState = DataRowState.Deleted Then
Return True
End If
If row.RowState = DataRowState.Added Then
For Each col As DataColumn In table.Columns
Dim IgnoreThisColumn As Boolean = False
For Each sFieldname As String In FieldsToIgnore
If col.ColumnName = sFieldname Then
'field was found
IgnoreThisColumn = True
End If
Next
If Not IgnoreThisColumn Then
Select Case col.DataType.ToString
Case "System.Int32"
If GetInteger(row(col, DataRowVersion.Current)) <> 0 Then
Return True
End If
Case "System.Double"
If GetDouble(row(col, DataRowVersion.Current)) <> 0 Then
Return True
End If
Case "System.Decimal"
If GetDecimal(row(col, DataRowVersion.Current)) <> 0 Then
Return True
End If
Case "System.DateTime"
If GetDate(row(col, DataRowVersion.Current)) <> Date.MinValue Then
Return True
End If
'Case "System.String"
' Dim oldColValue As String = ""
' Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))
' If IsRTFColumn(col.MaxLength, newColValue) Then
' If RTFColumnValueChanged(oldColValue, newColValue) Then Return True
' Else
' If oldColValue <> newColValue Then Return True
' End If
Case Else
If GetString(row(col, DataRowVersion.Current)) <> "" Then
Return True
End If
End Select
End If
Next
'if it made it here, return false, as all the data was empty and undesired to save
Return False
End If
For Each col As DataColumn In table.Columns
Select Case col.DataType.ToString
Case "System.Int32"
If GetInteger(row(col, DataRowVersion.Original)) <> GetInteger(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.Double"
If GetDouble(row(col, DataRowVersion.Original)) <> GetDouble(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.Decimal"
If GetDecimal(row(col, DataRowVersion.Original)) <> GetDecimal(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.DateTime"
If GetDate(row(col, DataRowVersion.Original)) <> GetDate(row(col, DataRowVersion.Current)) Then
Return True
End If
Case "System.String"
Dim oldColValue As String = Trim(GetString(row(col, DataRowVersion.Original)))
Dim newColValue As String = Trim(GetString(row(col, DataRowVersion.Current)))
If IsRTFColumn(col.MaxLength, newColValue) Then
If RTFColumnValueChanged(oldColValue, newColValue) Then Return True
Else
If oldColValue <> newColValue Then Return True
End If
Case Else
If GetString(row(col, DataRowVersion.Original)) <> GetString(row(col, DataRowVersion.Current)) Then
Return True
End If
End Select
Next
End If
Return False
End Function
End Sub
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply