Recently, I was asked to look at a bug in our software where editing a certain record with a certain customers database took far too long — an update which should have taken less than a second took two hours to complete.
I debugged through the application to find the SQL statement in question. It was an UPDATE against a fairly simple view. This view joined a couple tables together, and had a WHERE clause restricting the returned records to those visible to the current logged in user. The view has insert, update, and delete triggers which do nothing more than update the underlying tables. There are some triggers on those tables as well, but nothing complicated.
In fact, my first test was to issue some relatively simple SQL like this:
UPDATE THE_VIEW
SET [...]
WHERE PRIMARY_KEY = :PRIMARY_KEY
This statement executed almost instantly. Clearly the update itself was not the problem.
The UPDATE statement executed by the application, however, was more complicated than the one above. It looked more like this:
UPDATE THE_VIEW
SET [...]
WHERE PRIMARY_KEY = :PRIMARY_KEY AND FIELD1 = :FIELD1 AND FIELD2 = :FIELD2 AND [...]
This SQL was generated by the SQL resolver used by TDataSetProvider. Without going into why it generates such SQL (in fact, there’s a good reason for it), the important thing to observe here is that the update should still affect only the record that the user was in fact editing. So the UPDATE itself will be identical to the "simple" UPDATE statement above.
So why did this more complicated UPDATE statement takes so much longer to do precisely the same thing?
It’s important to realize that an UPDATE statement does a SELECT first. This makes sense when you consider that can UPDATE may affect many different rows, and the database server has to find them before it can update them. But it’s easy to forget this when the UPDATE in question is designed to affect only one row.
In executing the "more complicated" UPDATE statement, InterBase first transformed the UPDATE into a SELECT and executed that. The SELECT, with the WHERE clause from the UPDATE is what took such a long time to execute. Once that was complete, the actual update executed just as quickly as in the "simple" UPDATE statement.
Moreover, I could reproduce the performance problem by appending the WHERE clause from the UPDATE statement to the view itself, or to the SELECT statement used in the definition of the view. Doing this allowed me to alter the definition of the view such that there was no longer a performance problem when doing the "more complicated" UPDATE statement.
{ 3 } Comments
Or you can just set the DataSetProvider.UpdateMode to upWhereKeyOnly and include pfInKey into the PK field of the corresponding dataset. Then the provider will generate WHERE statement with PK field only. But upWhereAll or upWhereChanged are used to guard against update of a record that is already changed by a different user. So changing the view behaviour can lead to some sort of inconsistency between application developer expectations and actual database behaviour.
NB:
The where clause in update (and delete) statement, generated by SQL resolver may be controlled by TDatasetProvider.UpdateMode value. upWhereKeyOnly - all you needs for only primary key fields in ‘where’. Exactly, TxxxQuery fields with pfInKey in ProviderFlags option only.
Maxim and Romkin,
Yes, I understand why the DSP/resolver generated the WHERE clause. Like I said in the post, it’s beyond the scope of the post to discuss it here, but IMHO what it’s doing is a *good* thing, and I don’t want to stop it from doing that.
The important point here is that the WHERE clause was legitimate, and the VIEW needed to be changed so that it could be executed quickly.
Post a Comment